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

Home > Articles > Apple > Operating Systems

Absolutely the Last Article You Need To Read About Absolute References in Excel

  • Print
  • + Share This
  • 💬 Discuss
From the author of
Ever copy a formula you think is perfect and have surprising (and incorrect) results in the destination cells? You might be missing out on a feature of Excel that can make your spreadsheet work easier.

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 1Figure 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 2Figure 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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus