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