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.
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.