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
Search JabSto ::




Custom Search