18 min read
Relative and Absolute References
$ locks a column or row. Multiply every total by a tax rate in $H$1.
Relative vs Absolute References
When you copy a formula, relative references shift with the copy. =SUM(B2:E2) in F2 became =SUM(B3:E3) in F3 when you dragged the fill handle.
B2 is *relative* — the row number moves with the copy.
An absolute reference uses $ to lock a column, row, or both. 1 always points at cell I1 no matter where you copy the formula.
See Relative References Shift
You already saw this on NorthgateBudget.xlsx: copying =SUM(B2:E2) from F2 to F6 gives =SUM(B6:E6) for Arts.
Each row's formula reads its own quarter columns B through E on the same row number. That is relative referencing working correctly.
Put a Rate in a Fixed Cell
In H1, type Contingency rate. In I1, type 0.05 (5% as a decimal). In J1, type With contingency (£).
Every department's annual total in column F will be multiplied by the same rate in I1. The rate must stay fixed when you copy the formula down.
Lock I1 with $
Click J2. Type **=F2*(1+1) and press Enter**.
The $ before I and 1 locks the reference to I1 no matter where you copy. Copy J2 down to J6.
Click J4 (Science). Formula bar should show **=F4*(1+1) — F changed, 1** did not.
Mixed reference: $I1 locks the column only; I$1 locks the row only. 1 locks both.
When to Lock a Cell
Lock cells that hold rates, tax percentages, or global settings: I1 for contingency, a VAT rate, or a governor-approved uplift.
Do not lock the row references in =SUM(B2:E2) when copying down — each department needs its own B3:E3, B4:E4, and so on.
Example: **=F2*(1+1) adds 5% contingency to English's annual total in F2. Copied to J4, it becomes =F4*(1+1)** — Science's total with the same rate.
Practice
More lessons in Microsoft Excel · Next: The IF Function · Previous: SUM and AVERAGE