50 Excel
Cell References
When Excel calculated the total sales for column C and column D on
your worksheet, it inserted the following formulas into C13 and D13,
respectively:
5 SUM(C2:C11)
and
5 SUM(D2:D11)
At this point you may wonder how Excel knew to copy everything except
the cell reference from cell B13 and, in place of the original B2:B11 refer-
ence, to shift the cell reference one and two columns to the right. Excel does
this automatically when you use relative references in your formulas. A rel-
ative reference identifi es a cell range on the basis of its position relative to
the cell containing the formula. One advantage of using relative references,
as you’ve seen, is that you can fi ll up a row or column with a formula and
the cell references in the new formulas will shift along with the cell.
Now what if you didn’t want Excel to shift the cell reference when you
copied the formula into other cells? What if you wanted the formula always
to point to a specifi c cell in your worksheet? In that case you would need
an absolute reference. In an absolute reference, the cell reference is prefi xed
with dollar signs. For example, the formulaFigure 2-11
All sales
totals