Absolutely the Last Article You Need To Read About Absolute References in Excel
- 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
Okay, so you've been using Excel for a while and you're pretty comfortable with the basics of writing formulas. You even know how to copy similar formulas from one cell to another to save time. But that feature may not always work the way you expect, and you may not know why.
Let me explain. And while I'm at it, I'll tell you how to write more flexible formulas that can be copied to surprising places.
The secret is absolute cell references. But before I start explaining them, let's take a moment to review how relative cell references—the ones you're probably already using—work in formulas.
NOTE
Want to follow along without re-creating my worksheets from scratch? Download the Excel file used in these examples.
To keep the examples simple for this article, note that I've used only cells in the same worksheet.
Using Relative References in Formulas
When you use a relative cell reference in a formula, you're telling Excel that you're referring to a cell by its position relative to the cell in which the formula is written.
For example, look at Figure 1. On the surface, the formula in cell B4 (=B2-B3) looks as if it's saying to subtract the value in cell B3 from the value in cell B2. That's what it's saying to us. But that's not what it's saying to Excel. What Excel understands from the formula is the following: "Take the value in the cell that's one cell above this cell, and subtract it from the value in the cell that's two cells above this cell."
)
Figure
1 A simple formula in Excel.
This may not seem like much of a distinction—after all, the net effect is the same—but it's extremely important. Because Excel sees these cell references relative to the cell in which the formula is written, you can copy the formula from cell B4 to cell C4 (as in Figure 2) and the formula remains correct (=C2-C3). That's because Excel rewrites the formula relative to the cell to which the formula has been copied. Excel couldn't do that if it read the formula references as cell addresses.
)
Figure
2 When you copy a formula, Excel adjusts the cell references so their effect
remains the same, relative to the cell in which the new formula appears.