- Foundation
- About 13 min
- 5 practice questions
Cell references: relative, absolute and mixed
Explain what happens to a formula when copied, and use $ to lock a row or column.
If something looks wrong, please tell us.
Copying a formula down a column is the quickest way to fill a sheet. Whether it works correctly depends on the type of cell reference used.
Relative reference
A relative reference such as B2 changes when the formula is copied. If =B2*2 in C2 is copied one row down to C3, it becomes =B3*2. The reference moves with the formula.
Absolute reference
An absolute reference has a dollar sign before both the column and row, such as $B$1, and does not change when copied. It is used for fixed values like a tax rate or a discount rate.
Mixed reference
A mixed reference locks only one part: $B1 locks the column, B$1 locks the row.
| Written | Type | Copy B2 down 1 row |
|---|---|---|
| B2 | Relative | B3 |
| $B$2 | Absolute | $B$2 |
| $B2 | Mixed (column fixed) | $B3 |
| B$2 | Mixed (row fixed) | B$2 |
Worked examples
Example 1. C2 holds =B2*$E$1 with E1 = 10. Copied to C3, what is the formula? =B3*$E$1. B2 moved to B3 but $E$1 stayed fixed.
Example 2. If B2 = 50 and B3 = 80 with E1 = 10, what do C2 and C3 show? C2 = 50 x 10 = 500 and C3 = 80 x 10 = 800.
Example 3. A formula =A1 in cell B1 is copied to cell C1. What does it become? The formula moves one column to the right, so it becomes =B1.
Relative moves, absolute (with $) stays put. The F4 key toggles the dollars in many programs.
Key takeaways
- Relative references shift when copied; absolute references do not.
- Use $ to lock a column, a row, or both.
Check your understanding
-
1. Which reference stays exactly the same when the formula is copied anywhere?
- C4
- $C4
- C$4
- $C$4
Show answer
Answer: D. A dollar sign before both the column and the row makes the reference absolute, so $C$4 never changes.
-
2. C2 holds =B2*$E$1. It is copied down to C5. What formula is in C5?
- =B2*$E$1
- =B5*$E$1
- =B5*$E$4
- =B5*E4
Show answer
Answer: B. Moving 3 rows down, the relative B2 becomes B5, while the absolute $E$1 stays fixed.
-
3. Cell B1 holds =A1. It is copied to D1. What does D1 hold?
- =C1
- =A1
- =B1
- =D1
Show answer
Answer: A. The copy moves two columns to the right, so the relative reference A1 also moves two columns: A to C, giving =C1.
-
4. A formula containing $B3 is copied one column to the right in the same row. The reference becomes
- $C3
- $B4
- $C4
- $B3
Show answer
Answer: D. The column B is locked by the dollar sign and the row does not change when copying across, so it stays $B3.
-
5. In the mixed reference B$1, what is locked?
- Only the column
- Both the column and the row
- Neither the column nor the row
- Only the row
Show answer
Answer: D. The dollar sign locks what follows it. In B$1 it stands before the row number 1, so only the row is fixed.
Sign in to keep track of the lessons you finish.