A copied formula goes wrong when a reference that should stay fixed is allowed to move. The fix is to lock that reference with dollar signs, for example $E$1, before you copy.
This page explains the rule with a worked example, names the usual mistake, and gives you a quick test.
What does “relative” really mean?
A relative reference such as B2 is a position measured from the cell holding the formula: “one column to the left, same row”. When you copy the formula, the relationship is kept, so the reference shifts with it. Copy down one row and B2 becomes B3. Copy right one column and B2 becomes C2.
This is useful for filling a column, because each row then uses its own row’s values. It causes trouble when one of the cells is meant to be shared by every row.
Worked example: a tax column
This uses a generic spreadsheet with original data. Copy-and-paste behaviour and the dollar-sign form are common, but check them on your own software and version.
A school canteen sheet lists prices in B2 to B5: 4.50, 6.00, 3.20 and 8.00. Cell E1 holds the tax rate 0.06. Column C should show tax for each item.
Attempt 1: In C2, type =B2*E1 and copy down.
- C2 = B2 × E1 = 4.50 × 0.06 = 0.27, which is correct.
- C3 became =B3*E2. E2 is empty, so the result is 6.00 × 0 = 0.
- C4 and C5 become =B4E3 and =B5E4, also 0.
The first row was right, which hides the problem. Both references moved down, but only the price should have.
Attempt 2: In C2, type =B2*$E$1 and copy down.
- C2 = 4.50 × 0.06 = 0.27
- C3 = 6.00 × 0.06 = 0.36
- C4 = 3.20 × 0.06 = 0.192, which displays as 0.19 if shown to two decimal places
- C5 = 8.00 × 0.06 = 0.48
Now B2 moves with each row, and $E$1 stays. A bonus: if the rate changes to 0.08, changing one cell updates the whole column.
The mistake to watch for
Mistaken approach: Check only the first formula, see the correct number, and assume the rest are right.
The correction is to click on the last cell in the column and read its formula. If it points at an empty cell, you have found the problem. Change one input, such as the rate, and watch whether every row updates.
What about a reference that moves in only one direction?
A mixed reference locks one part. Suppose a multiplication grid has row headers 1, 2, 3 in A2 to A4 and column headers 1, 2, 3 in B1 to D1. In B2, type =$A2*B$1.
- $A2 keeps column A, but the row moves.
- B$1 keeps row 1, but the column moves.
Copy the formula to D4. It reads =$A4*D$1, which is 3 × 3 = 9. One formula now fills the whole grid, which is faster and avoids typing errors.
A quick test before you submit
- Click the last copied cell and read its formula.
- Ask, “Which cells should never move?” and lock those.
- Change one input and confirm every dependent cell updates.
- Check two values by hand.
- Reopen the saved file and look again.
The spreadsheet reference practice grid shows the copy trace and recalculated values for small original grids, so you can see the movement without building a full sheet. It has a bounded formula engine, so do not assume it matches every spreadsheet feature.
Where do I learn this properly?
Start with building a formula with correct relative references, then using an absolute reference deliberately, and finish with diagnosing a formula copied into the wrong range. The full module is spreadsheet formulas, and bigger models are in spreadsheet modelling and charts. Test yourself with the ICT original practice.
When might one-to-one help?
Some students understand the rule but lock the wrong cell when a sheet has several inputs. A teacher can give you fresh grids and ask you to predict each copied formula before you check. Online one-to-one ICT tuition starts with a paid one-hour trial at the assigned teacher’s confirmed rate, from RM80.