Objective 3. Enter and Edit Data in a Worksheet

1.

Click the Sheet1 tab, if necessary, so that Sheet 1 is the active sheet. Click cell A1, type Tableware Purchases - Dallas and then press .

After you type data into a cell, you must confirm the entry to store it in the cell. One way to do this is to press the key, which typically moves the selection to the cell below to facilitate entry in a column of cells. You can also use other keyboard movements, such as or one of the arrow keys on your keyboard, to make another cell active and confirm the entry.

2.

Look at the text you typed in cell A1, and notice that the text that does not fit into cell A1 spills over and displays in cells B1 and C1 to the right.

If text is too long for a cell and the cells to the right are empty, the text will display. If the cells to the right are in use, only the text that will fit in the cell will display. Cell A2 is the active cell, as indicated by the black border surrounding it.

3.

In cell A2, type Item and press .

The text is left aligned in the cell and the selection moves to cell A3. Left alignmentcharacters align at the left edge of the cellis the default for text entries and is an example of formatting information stored in a cell.

4.

In cell A3 type Plates - 6 inch and press . In cell A4, type P and notice the text from the previous cell displays.

Excel assists you in typing. If the first few characters you type in a cell match an existing entry in the column, Excel fills in the remaining characters for you. This feature, called AutoComplete, speeds your typing. AutoComplete assists only with alphabetic values; it does not assist with numeric values.

5.

Continue typing the rest of the row title, lates - 8 inch press , and then compare your screen with Figure 1.31.

Figure 1.31.

As soon as the entry you are typing differs from the previous value, the AutoComplete suggestion is removed.

6.

Without correcting the spelling error, in cell A5 type Cofee Mugs and press . Then, in cell A6 type Silverware and press . In cell A7 type Total and press .

7.

Click cell B2 to make it the active cell. Type Jan and press .

The selection moves to the right when you use the key to confirm the entry. Using facilitates entering values in rows.

8.

In cell C2, type Feb press and then continue in this manner to type Mar and Apr and May and Jun in cells D2, E2, F2, and G2, respectively. Use or to confirm the last entry. Then, compare your screen with Figure 1.32.

Figure 1.32.

Be sure to confirm the last entry by using or , because entry of a value must be confirmed to make other Excel functions available.

9.

On the Standard toolbar, click the Spelling button , and then compare your screen with Figure 1.33.

Figure 1.33.

10.

Under Not in Dictionary, notice the word Cofee.

The spelling checker tool does not have this word in its dictionary. Under Suggestions, Excel provides a list of suggested spellings.

11.

Under Suggestions, click Coffee, and then click the Change button.

Cofee, which was a typing error, is changed to Coffee. A dialog box displays Do you want to continue checking at the beginning of the sheet? The spelling checker begins its checking process starting with the currently selected cell. To check all the cells, including those above or to the left of the active cell, click Yes.

NoteWords not in the dictionary are not necessarily misspelled

Many proper nouns or less commonly used words are not in the dictionary used by Excel. If Excel indicates a correct word as Not in Dictionary, you can choose to ignore this word or add it to the dictionary. You may want to add proper names that you expect to use often, such as your own last name, to the dictionary if you are permitted to do so.

12.

In the Microsoft Excel dialog box, under Do you want to continue checking at the beginning of the sheet? click Yes.

If you have misspelled other words, the Spelling dialog box will display the next word that is not in its dictionary.

13.

Correct any other errors that you may have made. When the message displays The spelling check is complete for the entire sheet, click OK.

14.

Point to cell A6 and double-clickclick the left mouse button twice in rapid succession while keeping the mouse still. Compare your screen with Figure 1.34.

Figure 1.34.

The insertion point displays in the text in cell A6, and the text also displays in the Formula Bar.

15.

Move the mouse pointer away from the cell so that you have a clear view, and then using the arrow, , or keys as necessary, edit the text and change it to Flatware Confirm the change by pressing .

16.

On the Standard toolbar, click the Undo button . Alternatively, you can press on the keyboard to reverse (undo) the last action.

Silverware is restored and Flatware is deletedyour action was undone. Recall that a toolbar button is a one-click method of performing a command that would otherwise take several clicks to perform from the menu.

More Knowledge: Using AutoCorrect

AutoCorrect can correct common typing errors. It compares what you type to a list of commonly mistyped words and when it finds a match, it substitutes the correct word. To view the AutoCorrect options, from the Tools menu, click AutoCorrect Options.

1.

In the column heading area, point to the vertical line between column A and column B to display the double-headed arrow pointer , press and hold down the left mouse button, and then compare your screen with Figure 1.35.

Figure 1.35.

A ScreenTip displays information about the width of the column. The default width of a column is 64 pixels. A pixel, short for picture element, is a point of light measured in dots per square inch on a screen. Sixty-four pixels equal 8.43 characters, which is the average number of digits that will fit in a cell using the default fonta set of characters with the same design, size, and shape. The default font in Excel is Arial, and the default font sizethe size of characters in a font measured in pointsis 10. There are 72 points in an inch, with 10 points being the most commonly used font size in Excel. Point is usually abbreviated as pt.

2.

Drag to the right until the number of pixels indicated in the ScreenTip reaches 90 pixels, which is wide enough to display the longest titles in cells A2 through A7. The title in A1 will span more than one column and still does not fit in column A. If you are not satisfied with your result, click the Undo button and begin again.

3.

Click cell A7, and then on the Formatting toolbar, click the Align Right button .

The title is aligned at the right side of the cell to distinguish it from the other titles in the column. Text can be aligned at the center, left, or right of a cell. By default, text aligns at the left, but is easily changed as you have done here.

4.

Select the range B2:G2, and then on the Formatting toolbar, click the Center button .

The column titles are aligned in the center of each cell.

5.

In the row heading area, position the pointer over the horizontal line between row 1 and row 2 until the double-headed arrow displays. Drag downward until the height of row 1 is 32 pixels, and then compare your screen with Figure 1.36.

Figure 1.36.

The height of the row is increased. Row height is measured in points or in pixels. Points are the units in which font size is measured and pixels are units of screen display. The default height of a row is 12.75 points or 17 pixels.

6.

On the Standard toolbar, click the Save button to save the changes you have made to your workbook; make it a habit to save your work frequently.

1.

Click cell B3, type 77.35 and then press .

By default, numbers align at the right edge of the cell. The default number formata specific way in which Excel displays numbersis the general format. The general format has no specific characteristicswhatever you type in the cell will display, with the exception that trailing zeros to the right of a decimal point will not display. For example, if you type 125.50 the cell will display 125.5 instead.

2.

Using the techniques you have practiced, enter the numbers shown in the table. You can type the rows first or the columns first, and use either or to confirm each entry.

3.

Use the technique you have practiced to change the width of column A to 80 pixels, and then click cell A3. Compare your screen with Figure 1.37.

Figure 1.37.

The text in cells A3 and A4 no longer extends into cells B3 or B4 because those cells are now occupied. The text is truncatedcut off. However, the entire text still exists and displays in the Formula Bar. Data displayed in a cell is referred to as the displayed value. Data displayed in the Formula Bar is referred to as the underlying value. The number of digits or characters that appear in a cellthe displayed valuedepends on the width of the column.

4.

On the Standard toolbar, click the Undo button to restore the column width to 90 pixels. Change the width of column B to 30 pixels, click cell B6, and then compare your screen with Figure 1.38.

Figure 1.38.

If the column is too narrow to display all of the decimal places in a number, the display of the number will be rounded to fit the available space. Rounding is a procedure in which you determine which digit at the right of the number will be the last digit displayed and then increase it by one if the next digit to its right is 5, 6, 7, 8, or 9.

If a cell width is too narrow to display the entire number even after it is rounded to a whole number, Excel displays a series of pound signs instead; displaying only a portion of a whole number would be misleading. The underlying values remain unchanged and are displayed in the Formula Bar for the selected cell. The underlying value also displays in the ScreenTip if you move the mouse pointer over the cell containing ###.

NoteMonitor Settings Affect Pixels

Depending on the settings for your monitor, changing the pixels for column B to 30 may result in all of the values displaying as ####. Examine Figure 1.38 and continue to step 5.

5.

On the Standard toolbar, click the Undo button to restore column B to a width of 64 pixels. In the Name Box, type b:g and press . In the column heading area, point to the boundary between any two column headings in the selected range to display the pointer, drag to 80 pixels, and notice that when a range of columns is selected in this manner, adjusting the width of one column adjusts the width of all.

6.

Select the range A1:G1, and then on the Formatting toolbar, click the Merge and Center button .

Cells A1 through G1 are merged together and now will function as a single cell; the text is centered across columns A through G in the newly formed cell. When practical to do so, delay centering of titles until other column adjustments are made.

1.

Point to the row 3 heading and right-clickpress the right mouse buttonto simultaneously select the row and display the shortcut menu. Compare your screen with Figure 1.39.

Figure 1.39.

A shortcut menu offers the most commonly used commands relevant to the selected area.

2.

From the displayed shortcut menu, click Insert.

A new row 3 is inserted above the selected row, and the existing rows are shifted down. Additionally, the Insert Options button displays.

3.

Point to the Insert Options button to display its ScreenTip and its arrow, click the arrow to display a menu, and then compare your screen with Figure 1.40.

Figure 1.40.

From this menu, you can format the new row like the row above or the row below, or you can leave it unformatted. The default is Format Same As Above.

4.

Click Format Same As Below.

The new row is formatted using the format from the row of data below instead of the row of column titles above that are centered. The Insert Options button remains visible until you perform another screen action.

5.

Click cell A3, type Linen and then press .

6.

Enter the values for Linen for each month as shown below. Use to confirm each entry and move the active cell across the row. As you type, notice that the number for March200.40will display as 200.4 because trailing zeros do not display when using the default (General) number format.

7.

On the Standard toolbar, click the Save button to save the changes you have made to your workbook.

Objective 4 Construct a Formula and Use the Sum Function