- Practice
- About 13 min
- 5 practice questions
Cell references: relative, absolute and mixed
Predict how a copied formula changes with relative, absolute and mixed references.
If something looks wrong, please tell us.
When you copy a formula to another cell, the references inside it may shift. Whether they shift depends on the type of reference, and this is a favourite exam topic.
Three types
| Type | Example | When copied |
|---|---|---|
| Relative | A1 | Changes with the new position |
| Absolute | $A$1 | Never changes |
| Mixed | $A1 or A$1 | The part without $ changes; the part with $ stays |
The dollar sign locks what follows it. The F4 key cycles through the reference types when editing a formula.
Worked examples
Example 1. C1 holds =A1+B1. It is copied to C2. The row moves down by one, so C2 holds =A2+B2.
Example 2. D1 holds =A1*$B$1. It is copied to D3. A1 is relative and moves down two rows to A3. $B$1 is absolute and stays. So D3 holds =A3*$B$1. This is how one fixed tax rate in B1 is applied to a whole column of prices.
$ locks. Without $ the reference moves when copied.
Key takeaways: relative moves, absolute stays, and mixed locks only one part.
Check your understanding
-
1. Which of these is an absolute reference?
- $D$7
- D7
- $D7
- D$7
Show answer
Answer: A. An absolute reference has a dollar sign before both the column and the row, as in $D$7.
-
2. E2 holds =B2+C2 and is copied to E6. What does E6 hold?
- =B6+C6
- =B2+C2
- =B6+C2
- =C6+D6
Show answer
Answer: A. The copy moves 4 rows down and no columns, so both relative references move down 4 rows: =B6+C6.
-
3. F1 holds =$A1+B$1 and is copied to G3 (one column right, two rows down). What does G3 hold?
- =$A1+C$1
- =$A3+C$1
- =$A3+B$1
- =$B3+C$3
Show answer
Answer: B. $A1 keeps column A but its row moves 2 down to 3. B$1 keeps row 1 but its column moves 1 right to C. So =$A3+C$1.
-
4. D1 holds =A1*$B$1 and is copied to D4. What does D4 hold?
- =A4*$B$4
- =A1*$B$1
- =A4*$B$1
- =D4*$B$1
Show answer
Answer: C. A1 is relative and moves 3 rows down to A4; $B$1 is absolute and stays.
-
5. A formula containing $A1 is copied one column right and one row down. The reference becomes
- $B2
- $A2
- $A1
- B2
Show answer
Answer: B. The column A is locked and stays; the row is unlocked and moves down one, giving $A2.
Sign in to keep track of the lessons you finish.