Microsoft Office Tutorials and References

In Depth Information

**Finding Where It Is**

✓
An
absolute reference
has a dollar sign in front of the column letter and

the row number (for example, $M$290). When you copy a formula that

contains an absolute cell reference, the reference does not change.

✓
A
partial absolute
reference
has a dollar sign in front of the column letter

or the row number (for example, $M290 or M$290). When you copy a

formula that contains a partial absolute cell reference, the part of the

reference with the dollar sign does not change, but the other part does.

Figure 14-7 shows a worksheet in which entering a formula with a completely

relative cell reference causes a problem. Totals are the result of adding the

tax to the amount. The tax is a percentage (0.075) for a 7.5 percent tax rate.

This percentage is in cell C1 and is referenced by the formulas. The first

formula that was entered is in cell C7 and looks like this: =B7×(1 + C1).

The formula in cell C7 works correctly. It references cell C1 to calculate the

total. But if you use the fill handle to copy the formula from cell C7 to cells

C8 and C9, there’s a problem. The reference to cell C1 changed to cell C2 and

C3. Since these cells are empty, the results in cells C8 and C9 are incorrect —

they are the same as the amounts to the left. (No tax is added.)

Figure 14-7:

Changing a

reference

from relative

to absolute.

To better understand, column D displays the formulas that are in column C.

When the formula in cell C7 was dragged down, the C1 reference changed

to C2 in cell C8, and to C3 in cell C9. Often this is what you want — for Excel

to automatically change cell references when a formula is copied. But

sometimes, as in this situation, it is
not
what you want. You need an absolute cell

reference.