When a copied formula goes wrong, the cause is nearly always a reference that moved when it should not have, or did not move when it should. Diagnosing means reading the formula, not guessing. The invented sheet below shows the method.
How do you diagnose a copied formula?
- Look at the symptom. Zero, an error such as
#DIV/0!, a total that is too small, or a warning about a circular reference. - Click the first bad cell and read its formula in the formula bar.
- Compare with the formula in the last good cell. Find the reference that shifted.
- Ask what it should do: move, or stay fixed?
- Fix it, copy again, and hand-check one value.
Worked example
An invented daily sales sheet: B2:B5 holds 120, 95, 140 and 105. B6 holds =SUM(B2:B5), which is 120 + 95 + 140 + 105 = 460. Column C should show each day’s share of the total.
The attempt: in C2 the student types =B2/B6, which gives 120 ÷ 460 = 0.261, or 26.1%. It looks right. Copied down, C3 becomes =B3/B7. B7 is empty, so C3 shows #DIV/0!. All the cells below show the same.
Diagnosis: C2 and C3 differ because both references moved down. The numerator B3 should move, but the divisor B6 is the total, which must stay fixed.
The fix: C2 becomes =B2/B$6. Locking the row is enough because the formula is only copied down. Copy down again:
- C3: 95 ÷ 460 = 20.7%
- C4: 140 ÷ 460 = 30.4%
- C5: 105 ÷ 460 = 22.8%
Hand-check: 26.1 + 20.7 + 30.4 + 22.8 = 100.0%. If the shares add to 100%, the divisor was used consistently.
The mistake to watch for
Mistaken fix: the student retypes
=B3/460in every cell.The shares are right today, but if a day’s sales change, the total no longer matches. The formula is frozen again.
Replace typed numbers with the total cell and lock it. The other common mistake is a total range that accidentally includes the total: if =SUM(B2:B5) is in B6 and gets copied to B7, it becomes =SUM(B3:B6), which drops B2 and counts the old total in B6 as if it were a sale. Always check the range ends of a copied total.
Check yourself
Try these, then open each answer.
1. =SUM(C2:C4) is in C5. It is copied to C6. What is the formula in C6, and what is wrong?
Show answer
It becomes =SUM(C3:C5). The range slid down one row, so it now leaves out C2 and includes the total in C5.
2. =B2*$E1 is in C2 and copied to C4. What is it, and what goes wrong?
Show answer
It becomes =B4*$E3. The column is locked but the row is not, so E1 moves to E3, which is probably empty. The result is likely 0. The fix is $E$1.
3. =SUM(B2:B5) is in B6 and copied to D6. What is the formula, and is it right?
Show answer
It becomes =SUM(D2:D5). It is right only if column D holds the numbers to total. The range moved with the column, which is usually what you want.
Where this leads next
Try the spreadsheet formulas practice set, which mixes all five skills. Revisit absolute references if the locking was the issue, and use the spreadsheet reference practice grid to rehearse predictions. Back to the module: spreadsheet formulas.
If diagnosing alone still feels like guesswork, our teachers can practise it live with you in online one-to-one ICT tuition.