- Using Relative References in Formulas
- Using Absolute Cell References
- But Wait! There's More!
- Type a Few $s, Save Time

**Page 1**of 4 Next >

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

**Page 1**of 4 Next >