- Using Relative References in Formulas
- Using Absolute Cell References
- But Wait! There's More!
- Type a Few $s, Save Time
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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
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
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
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
6 An absolute cell reference in the formula makes it possible to copy the
formula the usual way.