Each worksheet in a workbook is made up of cells, and a cell can hold any of four types of data:
ò a value (including a date or a time)
|
ò text
|
ò a logical value (true or false)
|
ò a formula, which returns a value,
text, or a logical value
|
An Excel worksheet can also contain charts, drawings, diagrams, pictures, buttons, and other objects.
These objects actually reside on the worksheet's draw layer, which is an invisible layer on top of each worksheet.
|
In Excel 97, text is any combination of numbers, spaces, and non-numeric characters.
|
For example, Excel treats the following entries as text:
ò 09CC478
|
ò 345SWR
|
ò 44-657
|
ò 9893323
|
You enter text by clicking the cell where you want to enter data.
|
Then you simply type whatever it is that you want the cell to contain.
|
If you are typing data into rows, you press ^UEnter^u to enter the data and move the cell pointer to the row below.
|
If you are typing data into columns, you press ^UTab^u to enter the data and move the cell pointer to the row to the right.
|
Entering the same data many times in a worksheet is repetitive and time-consuming.
|
More importantly, you may find that you have entered data that contains minor errors without realizing it.
|
For example, you might enter a salesperson's name as Tom Brown, and as Thomas Brown or even Browne in another entry.
|
When you tally the sales for each employee, there will be one amount for Tom Brown and one for Thomas Brown, and possibly a third for Browne.
|
The AutoComplete function looks at other entries in the current column (both above and below the active cell) and attempts to match an item from the column list with the text you are typing.
|
You switch the AutoComplete option on by clicking the ^UEnable AutoComplete for cell^u ^Uvalues^u checkbox in ^UTools - Options - Edit^u.
|
As soon as the letter T is typed in a cell, AutoComplete will automatically place Tom in that cell.
|
The same is true of B - it will be turned into Brown before you can type the surname.
|
If this is a new entry, ignore the AutoComplete suggestions and continue to type the information until it is correct.
|
The Pick List feature works similarly to AutoComplete.
|
It is important to note that the AutoComplete and Pick List features are both column-related functions. And they only work with text, not numerical data.
|
As you type in a column of information, Excel "remembers" the items in the list.
|
You can access this list by right-clicking the cell at the top or bottom of the column list.
|
The Pick List is displayed as a miniature menu from which you can choose an item to fill the selected cell.
|
You highlight the item you wish to insert in the cell.
|
Then you click the item to insert it.
@@
A number that you type in a cell is a constant value.
|
So in spreadsheets, a constant is a number you type directly into a cell or place in a formula.
|
In Excel 97, a number can only contain the following characters:
ò 0 1 2 3 4 5 6 7 8 9
ò + - ( ) , / $ % . E e
|
All other combinations of numbers and non-numeric characters are treated as text.
|
Excel ignores leading plus signs (+), assuming all numbers to have positive values.
|
If a number is negative, you need to inform Excel of this by preceding the number with a minus sign (-) or by enclosing it in parentheses ().
|
You enter dates using a slash, a space or a hyphen to separate parts of the date.
|
For example, you can type 6/1/97, Jun 97 or Jun-97 to have the date stored in the cell as 06/01/97.
|
Most Excel users will enter times using the 24 hour clock format.
|
To enter a time based on the 12-hour clock, you type the time and then an ^Ra^r or a ^Rp^r to indicate am or pm.
|
In our example we used eight at night and were given the notation 8:00pm.
|
Excel defaults to am when recognizing a time value, so it is more important to remember to indicate pm times.
|
To enter today's date, you press ^UCtrl+;^u (semicolon).
|
To enter today's time, you press ^UCtrl+Shift+:^u (colon).
@@
Excel 97 has various features that make data entry easy.
|
One of these is what is known as AutoFill which will complete a series when given the first item.
|
In a series, for example, you can enter information of many different types, including common groups such as months or days of the week.
|
Let's suppose that we need a series of column headings showing the months of the year.
|
The first step is to type the starting month in the first column and click the ^UCheckmark^u button in the Formula bar.
|
The ^UCheckmark^u button is also called the ^UEnter^u button.
|
You place the pointer over the fill handle in the bottom right-hand corner of the selected cell.
|
The cursor changes shape to form a crosshair.
|
While pressing and holding the mouse button, you drag the handle horizontally across the cells until you reach the last month in your series.
|
You will notice that as you drag the mouse across the cells, each cell's content is displayed in a call-out box.
|
When you release the mouse button the entire range is entered automatically.
|
You can use the AutoFill feature for many different types of series, including
ò numbers which produce various
series such as 1, 2 giving 1, 2, 3, 4,
5 or 1, 3 giving 1, 3, 5, 7, 9
|
ò a mixture of text and numbers
such as Product 1 giving Product 1,
Product 2, Product 3
|
ò cell contents repeated as often as you
like across rows or down columns
|
ò repeated blocks of information which
can be repeated as often as is necessary
@@
You will find editing text and numbers a fairly simple matter.
|
Click the cell that needs editing to select it.
|
Then simply type in the correct text and press ^UEnter^u or ^UTab^u.
|
It is also possible to edit cell contents in the Formula bar.
You select the cell address to be edited, move the pointer into the Formula bar, click and edit the data.
You click the ^UCheckmark^u button when done.
|
To clear the contents of a cell quickly, select the cell by clicking it.
|
Then right-click to access the shortcut menu.
|
Choose the ^UClear Contents^u option.
|
This will delete the contents of the cell, but not the format.
|
You can perform this operation on selections of more than one cell.
|
The ^UClear Contents^u option can also be accessed by selecting ^UEdit - Clear - Contents^u.
|
However, this may be a longer way round.
|
This feature will also operate on a selection of cells.
|
AutoCorrect is a very useful feature which corrects errors as you type.
|
For example, if you type "teh" and press the spacebar or move to another cell, the entry is immediately changed to "the".
|
You access AutoCorrect by selecting the ^UAutoCorrect^u option in the ^UTools^u menu.
|
The AutoCorrect dialog box allows you to set various common errors for correction.
|
These errors include a list of commonly misspelled words with their corrections.
|
The ^UUndo^u option in the ^UEdit^u menu is another feature that helps to correct errors.
|
You are able to undo up to the last 16 actions, such as cutting, pasting, and correcting.
|
You can delete a worksheet in one of two ways:
ò activate the sheet and select
the ^UEdit - Delete Sheet^u option
ò right-click the sheet tab and
choose the ^UDelete^u option on the
shortcut menu
In either case, Excel asks you to confirm the fact that you want to delete the sheet.
|
Undo also has a complementary function known as ^URedo^u.
|
This function redoes your last command or action, including dialog box settings if possible.
@@
@@
As with any project, there are times when you need to insert new rows and columns to accommodate new information.
|
Because Excel always inserts columns or rows before your selection, you insert a new row by selecting the row after the one to be inserted.
|
For example, you have a series of rows showing the names of contractors in alphabetical order.
|
You want to add a new contractor called Alphen Software between Accent Languaging Systems and Apex Hardware.
|
In this case you would click the Apex Hardware row number, right-click to access the shortcut menu and then choose ^UInsert^u.
|
You will be asked whether to insert an entire row.
|
On clicking ^UEntire Row^u the new row will be inserted.
|
To insert a number of rows, click the row numbers at the beginning and at the end of the range while pressing the ^UShift^u key.
|
Once the entire set of rows is selected, you right-click to access the shortcut menu and choose the ^UInsert^u option.
|
The same number of rows selected will be inserted at the first cell address selected.
@@
Selection of rows for deletion is similar to that in an insertion operation.
|
When deleting a row, you select the exact row that you want removed.
|
For instance, if you want to remove Accent Languaging Systems from the worksheet, select that row by its row number (3).
|
Right-click to access the shortcut menu and choose ^UDelete^u.
|
The row you selected will no longer be there.
|
You can also delete an entire row simply by clicking one cell in that row, right-clicking for the shortcut menu, and choosing ^UDelete^u.
|
However, in this case the Delete dialog box will display.
|
You click the appropriate radio button, in this case ^UEntire row^u.
|
The row is removed instantly.
|
You can delete a number of adjacent rows by clicking the row numbers while pressing ^UShift^u.
|
Then you right-click to access the shortcut menu and choose ^UDelete^u.
|
The entire range of rows will be removed.
|
The alternative to using the shortcut menu accessed when right-clicking is to use the ^UEdit^u and ^UInsert^u menus.
@@
Working with columns is the same as working with rows.
|
The same menus and options are used to insert and delete entire columns.
|
To insert a column, you select the column or any cell to the immediate right of where you want to insert the new column.
|
To delete a column, you select the column itself, or any cell within the column.
|
Selecting a single cell and right-clicking gives you the choice of deleting or inserting entire rows and columns.
|
It is important to note that there are no confirmation prompts when deleting rows or columns.
Once you press ^UDelete^u the column or row is deleted directly.
@@
@@
One way to move or copy data is to select the cell or cells you want to cut or copy and then click the ^UCut^u or ^UCopy^u button in the Standard toolbar.
|
The Status bar instructs you to select a destination for the data.
|
You then have three options:
ò click the ^UPaste^u button
|
ò select ^UEdit - Paste^u
|
ò press ^UEnter^u
|
^RCutting^r data retains it on the Clipboard for one paste action, but does not leave a copy of the data on the Clipboard.
|
When you want to repaste the data elsewhere, the ^UPaste^u button does not work because the data is no longer in the Clipboard.
|
If you are ^Rcopying^r data, you can move to different cells and click the ^UPaste^u button.
|
When you are pasting data into the last cell, press ^UEnter^u to cancel the ^UCopy^u command.
|
You select several non-sequential cells at once by selecting the first cell, then holding down the ^UCtrl^u key while clicking the subsequent cells.
|
Say you want to copy the same cell to three different locations.
|
Click the first cell, then hold down the ^UCtrl^u key while you click the other destination cells.
|
You press ^UEnter^u to copy the contents of the original cell into all of the selected cells.
|
You can also cut (move) data by pointing to the cell you want to move and dragging it elsewhere.
|
The cursor adopts the shape of an arrow; you can then drag the cell to the new location.
|
When you drag data to a cell already containing data, Excel 97 asks whether you want to overwrite the destination cell.
If you click ^UOK^u, the new data replaces the data in the destination cell.
@@
Entire columns or rows of data can be copied both within a worksheet and between worksheets.
|
This is also true of copying data from one workbook to another.
|
Suppose you want to copy column D, heading included, to column F's position.
|
The first step is to select the column to be copied (column D).
|
With the column selected you click the ^UCopy^u button in the Formatting toolbar.
|
Then you select the destination, column F.
|
Once you press ^UEnter^u, column D replaces the original column F.
