Chapter 03 · basic · 4 min
References and the $: relative vs absolute
Every Excel formula that mentions another cell is making a choice, whether you notice it or not: should that reference move when the formula moves, or stay put? Get it wrong and the formula that worked perfectly in row 1 quietly returns garbage in row 2 — with no error, just a wrong number that looks plausible.
We'll compute driver commission for a small fares grid throughout this chapter: column A is trip fare, column B is where the commission formula goes, and D1 holds a single fixed commission rate shared by every row.
The grid
A fares grid: column A holds three Grab trip fares (RM). D1 holds one fixed commission rate applied to every trip.
A reference that moves with the formula
Commission is fare × rate. The obvious first formula reads the fare from A1 and the rate from D1:
=A1*D1In row 1, with D1 sitting right there, this is fine.
Before you run it — A1 is 82, D1 is 0.15. What commission comes back?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 82 | =A1*D1 | 0.15 | |
| 2 | 45 | |||
| 3 | 120 |
Copy it down, and D1 moves too
Copy that same formula to row 2 and Excel doesn't repeat "multiply by D1" — it shifts every reference by the same distance the formula moved. =A1*D1 in row 1 becomes =A2*D2 in row 2. D2 is empty, and an empty cell reads as 0 in arithmetic, so the row silently returns 0 instead of erroring. Nothing flags it; the number just looks like a trip with no commission.
A2 is 45. D2 is empty. What does the shifted formula return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 82 | 0.15 | ||
| 2 | 45 | =A2*D2 | ||
| 3 | 120 |
Locking a reference with $
A $ before a reference's column letter or row number freezes that part so it doesn't shift when the formula moves. $D$1 freezes both — copy =A3*$D$1 to any row and it still reads the rate from D1, never D2 or D3.
This is the fix, not a workaround: any reference to a single shared value — a rate, a threshold, a fixed date — should be absolute, because it's meant to stay the same no matter where the formula using it lives.
Commission for the third trip, locked
In B3, compute row 3's commission using a locked reference to D1 — one that would still read the rate correctly if filled down to other rows.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 82 | 0.15 | ||
| 2 | 45 | |||
| 3 | 120 |
Three flavours, not just two
$D$1 locks both column and row — use it for a single fixed cell like this rate. $D1 locks only the column, letting the row shift — useful when copying sideways across columns but down through rows tied to that column. D$1 locks only the row, the mirror case for copying down but not across. Reach for the mixed forms when a formula needs to fill both a grid of rows and columns from one header row or key column; for a single constant like a commission rate, $D$1 is almost always what you want.