The Formula That Breaks When Copied Down
A clerk has unit prices in B2:B11 and quantities in C2:C11. In D2 they type =B2*C2 and copy it down to D11 - it works. They then add a tax rate in a single cell, F1 (say 0.2), and change D2 to =B2*C2*F1. When copied down to D11, every row shows the same total as D2, or an error. The clerk is confused because 'the first formula worked when copied'.
1. Explain why the revised formula fails when copied and how to fix it.
Read the worked answer
F1 is a relative reference: when copied down, it becomes F2, F3 … F11, which are empty, so the product becomes 0 (or an error). The fix is an absolute reference: =B2*C2*$F$1. The $ locks the column and row so every copied row still points at F1. The first formula worked because B2 and C2 should shift with each row; F1 should not. The judgement is knowing which references must move (data in the same row) and which must stay fixed (a single constant).
2. The clerk also wants the tax rate in a named cell so formulas read clearly. Describe the steps and a risk to check.
Read the worked answer
Select F1, type a name such as TaxRate in the Name Box, press Enter. Then use =B2*C2*TaxRate. Named ranges are absolute by default, so copying down is safe and the formula is self-documenting. Risk to check: if the name is later reassigned or the cell is deleted, formulas return #NAME? or #REF!. Verify by selecting the name in the Name Box to confirm it points at F1, and recalculate (F9) to confirm totals update when the rate changes.