Microsoft Office Tutorials and References
In Depth Information
Using Absolute References
Income values inserted for January through May
incorrect v alues
The formulas you copied and pasted from January resulted in incorrect values for
February, March, April, and May. The February column show the couple’s estimated
monthly income for the summer, and the March, April, and May columns display the
value 0. What went wrong?
The problem is that the formulas use relative references. For example, the formula in
cell C19 is =D5, which references the cell located 14 rows up and 1 column to the left
of the current cell. When you pasted that formula to cell G19, Excel inserted the formula
=H5, which references the empty cell located 14 rows up and 1 column to the left of
cell G19, so that the value displayed in the cell is 0.
Instead of using relative references, you need to use a cell reference that will remain
ﬁ xed when the formula is copied to a new location. Cell references that remain ﬁ xed
are called absolute references. In Excel, absolute references have a $ (dollar sign) before
each column and row designation. For example, B8 is a relative reference to cell B8,
whereas $B$8 is an absolute reference to cell B8. When you copy a formula that
contains an absolute reference to a new location, the reference does not change. Figure 3-6
shows an example of how copying a formula with an absolute reference results in the
same cell reference being pasted in different cells regardless of their location.