Understanding Cell References: Relative vs. Absolute
Why copying a formula sometimes breaks it — and how a dollar sign fixes that.
Compatibility: All Excel versions
What You'll Learn
- The difference between a relative and an absolute reference
- How to lock a reference with dollar signs
- How to use F4 to cycle reference types quickly
When you copy a formula like =A1*B1 down to the next row, Excel normally adjusts it automatically to =A2*B2. This is called a relative reference — it shifts along with wherever you paste it.
But sometimes you need a formula to always point at the exact same cell no matter where you copy it — for example, multiplying a whole column of prices by a single tax rate stored in one cell. That's where an absolute reference comes in, marked with dollar signs:
=A2*$D$1
Here, A2 still shifts normally when copied, but $D$1 stays locked in place no matter how far down you copy the formula.
The F4 Shortcut
While editing a formula, select a cell reference and press F4 to cycle through relative, absolute, and the two "mixed" reference types (locking just the row, or just the column) — much faster than typing dollar signs by hand.
Common Mistakes
- Copying a formula down a column and not noticing it should have referenced a fixed cell instead
- Only locking the column or only the row when both actually needed to be locked
Practice Exercise
In column A, list 5 prices. In cell D1, enter a tax rate like 0.08. In column B, write one formula that multiplies each price by the tax rate in D1, using an absolute reference, then copy it down.
Key Takeaways
- Relative references shift when copied; absolute references (with $) stay fixed
- $D$1 locks both the column and row
- F4 cycles through reference types while editing a formula