Page 231 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 188
Part II
Working with Formulas and Functions
An absolute reference uses two dollar signs in its address: one for the column letter and one for the row
number (for example, $A$5). Excel also allows mixed references in which only one of the address parts is
absolute (for example, $A4 or A$4).
By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you
copy a formula to another cell.
Figure 11.7 shows a simple worksheet. The formula in cell D2, which multiplies the quantity by the price, is
=B2*C2
This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the
references adjust in a relative manner. For example, the formula in cell D3 is
=B3*C3
FIGURE 11.7
Copying a formula that contains relative references.
But what if the cell references in D2 contained absolute references, like this?
=$B$2*$C$2
In this case, copying the formula to the cells below would produce incorrect results. The formula in cell D3
would be exactly the same as the formula in cell D2.
Now I’ll extend the example to calculate sales tax, which is stored in cell B7 (see Figure 11.8). In this situa-
tion, the formula in cell D2 is
=B2*C2*$B$7
The quantity is multiplied by the price, and the result is multiplied by the sales-tax rate stored in cell B7.
Notice that the reference to B7 is an absolute reference. When the formula in D2 is copied to the cells below
it, cell D3 will contain this formula:
=B3*C3*$B$7
Here, the references to cells B2 and C2 were adjusted, but the reference to cell B7 was not — which is
exactly what I want.

188