Microsoft Office Tutorials and References

In Depth Information

**Using Relative References**

Figure 3-2

Total monthly expenses

formula to calculate the sum of

values in the range D22:D31

monthl
y totals

◗

5.
Review the total expenses for each month. January and August are particularly

expensive months because Glenn has to pay tuition and purchase books for the

upcoming semester.

◗

6.
Click each cell in the range D32:N32, reviewing the formula entered in the cell.

The formulas pasted into the range D32:N32 all calculate the sums of values in

different cell references. For example, the formula =SUM(D22:D31) was inserted

in cell D32, the formula =SUM(E22:E31) was inserted in cell E32, and so forth.

Using Relative References

When you entered the formula in cell C32 to sum the January expenses, Excel interprets

the cell references in that formula relative to the location of cell C32. In other words,

Excel interprets the formula =SUM(C22:C31) as adding the values entered in the 10 cells

directly above cell C32.

Excel uses this interpretation of the cell references when the formula is pasted into

other cells. For example, when you pasted the formula in cell D32, the formula changed

to =SUM(D22:D31), which has a different cell reference but the same meaning: adding

the values of the 10 cells directly above the active cell. The formulas in the remaining

cells of the range D32:E32 were similarly adjusted so that each formula displays the total

expenses for the selected month.

The cell references used in these formulas are called relative references because when

Excel copies and pastes them, they are always interpreted in relation, or relative, to the

location of the cell containing the formula. Figure 3-3 illustrates how a relative cell

reference in a formula changes when the formula is copied to another group of cells.