Relative vs Absolute References in Excel (With Examples)
Relative references move when you copy a formula; absolute references with $ stay fixed. A side-by-side comparison with examples of when to use each.
By Ayoub Farid · · 2 min read
Every reference in a formula is either relative, absolute or mixed. You choose without noticing, and it decides what happens when you copy the formula. Here's the difference side by side.
Relative references (the default)
B2 is relative. Excel remembers it as "the cell one column left of me, same row" (if the formula is in C2). Copy the formula down and it adjusts.
| Formula in | Becomes when copied to row 3 |
|---|---|
C2: =B2*1.2 | C3: =B3*1.2 |
D2: =B2*C2 | D3: =B3*C3 |
This is what you want for row-by-row calculations: each row uses its own price and quantity.
Absolute references ($)
$B$14 is absolute: "exactly B14, always".
| Formula in | Becomes when copied to row 3 |
|---|---|
C2: =B2/$B$14 | C3: =B3/$B$14 |
D2: =B2*$F$1 | D3: =B3*$F$1 |
This is what you want when every row needs the same cell: a total, a rate, a target.
Side by side
| Situation | Use |
|---|---|
| Price × quantity per row | Relative: =B2*C2 |
| Each row ÷ grand total | Absolute total: =B2/$B$14 |
| Each row × tax rate in F1 | Absolute rate: =B2*$F$1 |
| Lookup table | Absolute range: $H$2:$J$50 |
| Running total | Mixed range: =SUM($B$2:B2) |
| Multiplication grid | Mixed: =$A2*B$1 |
A quick way to decide
Ask: "When I copy this formula to the next row, should this reference point at the next row too?"
- Yes → relative
- No → absolute
- Only in one direction → mixed
Copy vs move
References only adjust when you copy (fill, Ctrl+C/Ctrl+V). If you move a cell (cut and paste, or drag the border), its formula keeps pointing at the same cells.
Practise it
Read the full explanation in absolute references, then practise. Cellio's "Fill down" and "Lock with $" lessons copy your formula down the column like Excel does and show you exactly which copies broke.
Frequently asked questions
What is the difference between relative and absolute references?
Relative references change when the formula is copied; absolute references (with $) stay the same.
Which is the default in Excel?
Relative. You add $ to make a reference absolute.
Do references change when I cut and paste?
No. Moving a formula keeps its references; only copying adjusts them.
What is a mixed reference?
A reference with only the row or only the column locked, like $A2 or A$2.