A relative reference is a cell address such as B2 that moves when you copy the formula. If you copy a formula one row down, every relative reference in it moves one row down. This is why one formula can fill a whole column.
It appears in almost every spreadsheet task. The sheets here are invented practice material.
How does a relative reference move?
Think of B2 as an instruction, “the cell two steps left of me”. Copy the formula to a new cell and the instruction is repeated from that cell, so the address changes.
Copy one row down: the row number goes up by one. Copy one column right: the column letter moves to the next letter. Copy two rows down and one column right: both change.
Build the formula step by step
- Decide the result cell. Say the total goes in D2.
- Start with an equals sign. Every formula begins with
=. - Click or type the cells that hold the inputs, not their values.
- Press Enter and check the number looks right by hand.
- Copy down and read one copied formula to confirm the pattern.
Worked example
An invented shop sheet:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Unit price | Total |
| 2 | Pen | 10 | 1.50 | |
| 3 | Ruler | 4 | 2.25 | |
| 4 | Notebook | 6 | 3.20 | |
| 5 | Grand total |
Step 1: in D2 type =B2*C2. Result: 10 × 1.50 = 15.00.
Step 2: copy D2 to D3 and D4. D3 becomes =B3*C3, giving 4 × 2.25 = 9.00. D4 becomes =B4*C4, giving 6 × 3.20 = 19.20.
Step 3: in D5 type =SUM(D2:D4). Check by hand: 15.00 + 9.00 + 19.20 = 43.20.
Each copied formula keeps the same pattern: multiply the cell in column B by the cell in column C on its own row.
The mistake to watch for
A student types the values instead of the cell addresses.
Mistaken formula in D2:
=10*1.5It shows 15.00, so it looks correct. Copied down, D3 and D4 also read
=10*1.5and show 15.00, which is wrong for both.
The formula contains no references, so there is nothing to move. The fix is =B2*C2. A good habit: whenever you see a bare number inside a formula that came from the sheet, replace it with the address of its cell.
Check yourself
Try these, then open each answer.
1. The formula =B2+C2 is in D2. What does it become when copied to D4?
Show answer
It moves two rows down, so =B4+C4.
2. The formula =D3-B3 is in E3. What does it become when copied to F5?
Show answer
One column right and two rows down. D becomes E, B becomes C, and 3 becomes 5. The result is =E5-C5.
3. =SUM(B2:B4) is in B5. What does it become in C5?
Show answer
One column right, same row: =SUM(C2:C4). Both ends of the range move together.
Where this leads next
Some references must not move. That is the next skill: use an absolute reference deliberately.
When you are ready, try the spreadsheet formulas practice set, and use the spreadsheet reference practice grid to predict copied formulas. Back to the module: spreadsheet formulas.
If you can build the formula but still lose track once sheets grow, our teachers can work through a live task with you in online one-to-one ICT tuition.