Microsoft Office Tutorials and References

In Depth Information

**Overriding Relative Behavior: Absolute Cell References**

Excel
’
s capability to change D2 to D3 in the formula is called relativeref-

erencing. This is the default behavior of a reference. Sometimes, you do not

want Excel to change a reference as the formula is copied, as explained in the

next section.

Overriding Relative Behavior: Absolute Cell References

Overriding Relative Behavior: Absolute Cell References

Relative referencing, which is Excel
’
s ability to change a formula as it is

copied, is what makes spreadsheets so useful. At times, however, you need

part of a formula to always point at one particular cell. This happens a

lot when you have a setting at the top of the worksheet such as a growth rate

or a tax rate. It would be nice to change this cell once and have all the for-

mulas use the new rate.

The following example sets up a sample worksheet that exhibits this problem

and shows how to use an arcane notation style to solve the problem. When

you see a reference with two dollar signs, such as $G$1, this indicates an

absolute reference to G1. An absolute reference is a cell or range address

where the row numbers and the column letters are locked and will not change

during copying. Absolute references have a dollar sign before each column

letter and each row number. Examples include $G$1 and $T$2:$W$99.

Suppose that you have a sales tax factor in a single cell at the top of a

worksheet. After you enter the formula =C2*G1, it accurately calculates

the tax in cell D2, as shown in
Figure 8.5
.

Figure 8.5.

Figure 8.5.
This formula works fine in row 2.

This formula works fine in row 2.

However, when you copy the same formula to cell D3, you get a zero as the

result. As you can see in
Figure 8.6
, Excel correctly changed cell C2 to C3