Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Apple > Operating Systems

  • Print
  • + Share This
From the author of

Using Absolute Cell References

An absolute cell reference tells Excel not to think of a cell reference relative to the cell in which the formula is written. Instead, think of it as a cell address. No matter where the formula is copied, the cell reference stays the same.

Why would you want to use an absolute reference? Well, perhaps you have a bunch of formulas that all refer to the same cell. Figure 3 shows an example. In each case, the formulas in cells C6 though C9 need to refer to the profit value in cell C3.

Figure 3Figure 3 Absolute cell references can come in handy when you need a bunch of formulas that are basically the same to reference the same cell.

What happens when we copy the formula in cell C6 (=B6*C3) down to cells C7 through C9? As shown in Figure 4, disaster. Excel doesn't know that we always want to refer to cell C3, and helpfully changes the formula to keep the cell references relative.

Figure 4Figure 4 You can't expect Excel to read your mind and know that you want an absolute reference.

Remember, Excel doesn't read cell addressees in the formula. Instead, it reads this: "Take the value in the cell one cell to the left of the current cell and multiply it by the value in the cell three cells above the current cell." Excel rewrites the formulas in each destination cell accordingly.

So how do you tell Excel that you always want to refer to cell C3? You use special notation for that cell reference. Simply include a dollar sign ($) before each part of the reference that you want to Excel not to adjust when the reference is copied. As shown in Figure 5, the formula in cell C6 becomes =B6*$C$3.

Figure 5Figure 5 This formula uses an absolute reference to cell C3.

When you copy the formula in cell C6 to the cells beneath, Excel rewrites only the relative reference in the formula. As a result, the formula is correct for each cell (see Figure 6).

Figure 6Figure 6 An absolute cell reference in the formula makes it possible to copy the formula the usual way.

  • + Share This
  • 🔖 Save To Your Account