Most broken Excel formulas are broken for one reason: a cell reference moved when the formula was copied, and it should not have. Understanding the dollar sign fixes this for good.
Relative reference (A1)
When you copy a formula, the reference shifts. If B2 has =A2*2 and you copy it to B3, it becomes =A3*2. This is what you want for a column of similar rows.
Absolute reference ($A$1)
The dollar signs lock both the column and the row. =A2*$F$1 copied down always multiplies by F1, for example a tax rate or an exchange rate.
Mixed reference ($A1 or A$1)
$A1locks the column only.A$1locks the row only.
Mixed references are the secret of a multiplication table: =$A2*B$1 works across and down from one formula.
The F4 shortcut
Click inside the reference in the formula bar and press F4. It cycles through A1, $A$1, A$1 and $A1. On some laptops press Fn + F4.
Example: price with GST
GST rate 18% in F1, prices in B2:B100. In C2 use =B2*(1+$F$1) and copy down. Change F1 once and all rows update.
Common mistakes
- Forgetting the $ in lookup ranges.
VLOOKUP(A2, D2:E50, 2, 0)copied down slides the table. Use$D$2:$E$50. - Locking too much. If you lock a reference you want to move, the copied formulas all show the same result.