- Advanced
- About 13 min
- 5 practice questions
Cell references: relative, absolute and mixed
Predict what a formula becomes when copied by understanding relative, absolute and mixed cell references.
If something looks wrong, please tell us.
The most tested idea in spreadsheets is what happens to a formula when it is copied. The answer depends entirely on the type of cell reference used.
Three kinds of reference
| Type | Example | When copied |
|---|---|---|
| Relative | B2 | Both column and row shift with the copy |
| Absolute | $B$2 | Neither changes |
| Mixed | $B2 or B$2 | Only the part without the dollar sign changes |
The dollar sign locks what follows it. In practice, the F4 key cycles a reference through the types while editing a formula.
Worked example 1: copy down
Cell C2 holds =A2*B2. Copy it down to C4. Each step down adds 1 to the row of every relative reference, so C3 has =A3*B3 and C4 has =A4*B4.
Worked example 2: a fixed rate
Prices are in A2:A5 and a tax rate is in cell E1. In B2 you write =A2*$E$1 and copy down to B5. The relative A2 moves (A3, A4, A5) while $E$1 stays fixed, so every price is multiplied by the same rate. Writing =A2*E1 would shift to E2, E3 and so on, giving wrong results.
Worked example 3: copy across and down
Cell B2 holds =B$1*$A2 and is copied to C3. Moving one column right and one row down: B$1 has a locked row so only the column shifts, giving C$1; $A2 has a locked column so only the row shifts, giving $A3. The formula in C3 is **=C$1*$A3**. This pattern is used to build multiplication tables.
A dollar sign locks what follows it: column, row, or both.
Key takeaways
- Relative shifts, absolute is fixed, mixed locks one part.
- Lock a constant such as a rate with $.
- Shift each unlocked part by the distance moved.
Check your understanding
-
1. C2 holds =A2*B2 and is copied down to C6. What does C6 hold?
- =A6*B6
- =A2*B2
- =A6*B2
- =C6*D6
Show answer
Answer: A. Both references are relative, so each moves 4 rows down: =A6*B6.
-
2. Prices are in column A and a tax rate is in E1. B2 holds =A2*$E$1 and is copied down to B5. What does B5 hold?
- =A5*$E$4
- =A2*$E$1
- =A5*E4
- =A5*$E$1
Show answer
Answer: D. The relative A2 moves to A5; the absolute $E$1 stays fixed, so every price uses the same rate.
-
3. B2 holds =A2*E1 with no dollar signs and is copied to B4. What does the rate reference E1 become?
- E1
- E2
- E3
- F3
Show answer
Answer: C. Without dollar signs E1 is relative, so it moves 2 rows down with the copy to E3, which is why the results go wrong.
-
4. B2 holds =B$1*$A2 and is copied to D4. What does D4 hold?
- =B$1*$A2
- =D$1*$A4
- =D$3*$C4
- =D$1*$A2
Show answer
Answer: B. The copy moves 2 columns right and 2 rows down. B$1: row locked, column moves to D, giving D$1. $A2: column locked, row moves to 4, giving $A4.
-
5. In the reference $B2, what stays fixed when the formula is copied?
- The column B only
- The row 2 only
- Both the column and the row
- Neither the column nor the row
Show answer
Answer: A. The dollar sign locks what follows it; here it stands before B, so the column is fixed and the row can change.
Sign in to keep track of the lessons you finish.