Microsoft Office Tutorials and References
In Depth Information
Overriding Relative Behavior: Absolute Cell References
in the copied formula. However, Excel also changed G1 to G2. Because there is
nothing in G2, the formula calculates a zero.
Figure 8.6.
Figure 8.6. This formula fails in row 3.
This formula fails in row 3.
Because the sales tax factor is only in G1, you want Excel to always
point to G1. To make this happen, you need to build the original formula as
=C2*$G$1. The two dollar signs tell Excel that you do not want to have the
reference change as the formula is copied. The $ before the G freezes the ref-
erence to always point to column G. The $ before the 1 freezes the reference
to always point to row 1. Now, when you copy this formula from cell D2 to
other cells in column D, Excel changes the formula to =C3*$G$1, as shown in
Figure 8.7 .
Figure 8.7.
Figure 8.7. The dollar signs in the formula make sure that the copied for-
The dollar signs in the formula make sure that the copied for-
mula always points to cell G1.
mula always points to cell G1.
To recap, a reference with two dollars signs is called an absolutereference.
If you are never going to copy the formula to the left or right, you can
safely use =C2*G$1. This formula freezes only the row number. Given the
shape of the current data, it is likely that using a single dollar sign will be
valid.
Search JabSto ::




Custom Search