• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Hour 6. Understanding Excel 2003 Workbooks > To Do: Format the Worksheet

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.

Figure 6.7. Excel calculates all the averages for you.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint