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 D2 holds a single fixed commission rate shared by every row.
The grid
A fares grid with headings in row 1: column A holds three Grab trip fares (RM), column B is for commission. D2 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 A2 and the rate from D2:
=A2*D2In row 2, with the rate sitting right there in D2, this is fine.
Before you run it — A2 is 82, D2 is 0.15. What commission comes back?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fare (RM) | Commission | Rate | |
| 2 | 82 | =A2*D2 | 0.15 | |
| 3 | 45 | |||
| 4 | 120 |
Copy it down, and D2 moves too
Copy that same formula to row 3 and Excel doesn't repeat "multiply by D2" — it shifts every reference by the same distance the formula moved. =A2*D2 in row 2 becomes =A3*D3 in row 3. D3 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.
A3 is 45. D3 is empty. What does the shifted formula return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fare (RM) | Commission | Rate | |
| 2 | 82 | 0.15 | ||
| 3 | 45 | =A3*D3 | ||
| 4 | 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$2 freezes both — copy =A4*$D$2 to any row and it still reads the rate from D2, never D3 or D4.
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 B4, compute row 4's commission using a locked reference to D2. Then fill the column: row 3's 0 should turn into a real commission.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fare (RM) | Commission | Rate | |
| 2 | 82 | 0.15 | ||
| 3 | 45 | |||
| 4 | 120 |
Three flavours, not just two
$D$2 locks both column and row — use it for a single fixed cell like this rate. $D2 locks only the column, letting the row shift — useful when copying sideways across columns but down through rows tied to that column. D$2 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$2 is almost always what you want.