To Do: Format the Worksheet
The worksheet requires some formatting to look better, but you've already used Excel to enter text and numbers. The averages now need computing via a formula. Additionally, a little formatting would greatly improve the look. Follow these steps to complete the worksheet:
Move the cell pointer to F8 and type this: =(C8+D8+E8)/3. Then, press the down arrow to move to cell F9. Notice that Excel computed the average of Mary Bee's test scores. You just entered a formula that requests the average. The formula tells Excel to add the contents of cells C8, D8, and E8 and then divide the sum by 3. Several methods exist for creating such a formula, and you'll learn even better ways throughout the next three hours' lessons. Excel is smart and guesses at a lot of tasks to make life easier for you. Instead of typing the same formula all the way down column F, you only need to copy and paste the formula you just entered to F9, F10, and F11 to calculate the other three student averages. Click on cell F8 (the cell to copy), and press Ctrl+C to copy the cell to the Clipboard. Excel highlights the cell to show the selection. Click cell F9 and do this: Hold down the Shift key while pressing the down arrow twice. Excel highlights three cells. These cells are the target of your copy. Press Ctrl+V (or select Edit, Paste) to paste the Clipboard contents into the highlighted cells. When you do, Excel instantly updates the averages for the remaining three students. Excel even changes the formula you copy to reflect the new row numbers. This is called relative cell referencing because the formulas are copied relative to their new locations. You can ignore the Paste icon that Excel displays after the paste. When you paste data into a document, the Paste icon appears and enables you to modify the way the paste is performed. You can, for example, elect to paste the formula as text, and if you do, the result does not show in the cell but the formula itself does.
 | If Excel refused to change the row numbers when you copied the formula from F8, all four students would reflect Mary Bee's average. You can click on cell F10 and look at the formula in the formula bar (beneath the standard toolbar) to see that all references now indicate row 10 and no longer row 8 even though you copied the formula from row 8. Fancy? You bet. |
You now must compute the average for the class. That's simple: just type the following formula in cell F13: =(F8+F9+F10+F11)/4. The class average appears instantly. Your worksheet now looks like the one in Figure 6.7.
Now that you've finished entering text, numbers, and formulas, you can improve a worksheet's look with a little formatting. In addition, you might be interested in knowing how Excel can improve the functionality of the worksheet. If you insert new blank rows into the worksheet, Excel changes the average calculations at the end of the displaced student rows to reflect their new row location. You also can insert new columns in case the students take additional tests, and the formulas update to reflect new column names.
To Do: Complete the Worksheet's Format
One problem exists, however, with the formulas in their present state. If you add tests, you have to change each calculated average to reflect the additional test scores. As you might expect, Excel supports other ways to enter formulas so that the formulas update automatically even if you add new test columns. You'll learn about some of these features as you progress through the next two hours.
For now, follow these steps to complete the worksheet's format:
Although you will often format individual cells, you can use Excel's AutoFormat feature to create an interesting worksheet look. Select the entire Student Gradebook table by clicking on cell B4 and dragging your mouse to cell F13 to highlight the entire table. Select Format, AutoFormat to display the AutoFormat dialog box. Scroll down to the Colorful 2 format and select it. Instantly, Excel makes your table appear as though you slaved away for an hour over a hot computer! The two average calculations produce answers with too many decimal places for this worksheet. You should format the numbers so that they show only two decimal places. Highlight cells F11 through F13 by clicking and dragging your mouse. On the Formatting toolbar, you see a comma (,) that indicates, with the ScreenTip pop-up description, that it is called the Comma Style button. Click that comma toolbar button, and Excel changes the two cells to look better, displaying only two decimal places. (You might have to click the far-right toolbar button that shows additional options if you do not see the Comma Style button.) As Figure 6.8 shows, your Student Gradebook worksheet now looks truly professional. Excel even increased the Name column so that plenty of room exists for each name.
Rename the worksheet by right-clicking over the Sheet1 tab name at the bottom of the screen, selecting Rename, and typing Grades. Don't worry about naming the other two worksheets because you are not using them at this time.
 | By default, Excel creates new workbooks with three worksheets. You can remove extra worksheets by right-clicking over any worksheet tab and selecting Delete. In addition, you can insert additional worksheets by right-clicking over a tab and selecting Insert from the shortcut menu. Excel displays the Insert window, and you then can select Worksheet to insert a blank worksheet. When you insert new worksheets, Excel inserts a new worksheet to the left of the one on which you right-clicked. |
You can save your creation by selecting File, Save and entering a name such as Gradebook for the document name. Excel automatically appends the .xls filename extension if you do not supply it. You've just saved a workbook with three worksheets, but only one has anything valid in it, the first one with the name of Gradebook.
Now that you've got some hands-on experience with Excel, you're ready to master some of the more detailed aspects of the program. The next hour will take you through a more involved look at formulas and cell data.
|
Main Menu
|
