Excel 2003 for Starters: The Missing Manual

Copying Formulas

Sometimes you need to perform similar calculations in different cells throughout a worksheet. For example, you may want to calculate sales tax on each item in a product catalog, the monthly sales in each store of a company, or the final grade for each student in a class. In this section, you'll learn how Excel makes doing these kinds of calculations easy with relative cell references. Relative cell references are cell references that Excel updates automatically when you copy them from one cell into another. They're the standard kind of references that Excel uses (as opposed to absolute cell references, which are covered in the next section). In fact, all the references you've used so far have been relative references, but you haven't yet seen how they work with copy-and-paste operations.

Take a look at the worksheet shown in Figure 7-8, which contains a teacher's grade book. In this example, each student has three grades: two tests and one assignment. You want to base each student's final grade on the following percentages: 25 percent for each of the two tests, and 50 percent for the assignment.

The following formula calculates the final grade for the first student (Edith Abbott):

	=B2*25% + C2*25% + D2*50%

The formula that calculates the final grade for the second student (Grace DeWitt) is almost identical. The only change is that all the cell references are offset by one row, so that B2 becomes B3, C2 becomes C3, and D2 becomes D3:

	=B3*25% + C3*25% + D3*50%

The formula ...