Spreadsheet references
Replicate a formula down a column with and without dollar signs, and watch the tax rate silently walk off into empty cells.
- Relative references
- Absolute references
- Replicating formulae
| B — price | C — qty | D — total inc. tax | F | |
|---|---|---|---|---|
| 2 | 12.50 | 3 | =B2*C2*(1+$F$1)43.13 | 0.15 |
| 3 | 8.00 | 10 | =B3*C3*(1+$F$1)92.00 | empty |
| 4 | 24.99 | 2 | =B4*C4*(1+$F$1)57.48 | empty |
| 5 | 5.25 | 8 | =B5*C5*(1+$F$1)48.30 | empty |
Reference type
absolute
Rows calculated correctly
4 / 4
Error reported
none
Predict
The formula =B2*C2*(1+F1) is copied from row 2 to row 5. Which cell does the tax now come from?
Experiment
- 1Start with $F$1 and replicate six rows. Check every total includes the tax.
- 2Switch to F1 and watch the totals below the first row lose the tax silently.
- 3Work out, for row 5, which cell the relative reference has landed on.
- 4Explain why B and C should stay relative even though F must be absolute.
Explain
A relative reference changes when the formula is copied — it describes a cell by where it sits relative to the formula. An absolute reference, fixed with dollar signs, always points at the same cell no matter where the formula goes.
The test to apply to every reference in a formula is simple: when this formula moves down a row, should this reference move with it? The price and the quantity are on the same row as the formula, so they should. The tax rate lives in one cell for the whole sheet, so it must not.
What makes this dangerous in practice is that the spreadsheet reports no error. An empty cell is treated as zero, so the totals are simply wrong — which is exactly why exam questions ask you to write the formula “so that it can be replicated”.