Learning Excel: Absolute references
Locking it with a dollar
The rent is going up to £1.60 per square metre. Ruth has put the price in B1, on the Rent sheet, and wants the rent for every plot. She dragged a formula down, and now P02 suddenly costs hundreds of pounds.
What went wrong
In C5, =B5*B1 works out the rent for P01: 50 m² times £1.60. Correct.
But Ruth dragged that formula down. C6 now holds =B6*B2: the price slid down to B2, and that's where the £12 water charge is.
Dragging down shifts every reference, including the one to the price. And that one has to stay put.
=B6*B2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price per m² | 1.6 | ||
| 2 | Water charge | 12 | ||
| 3 | ||||
| 4 | Plot | m² | Rent | To pay |
| 5 | P01 | 50 | £80.00 | |
| 6 | P02 | 25 | £300.00 | |
| 7 | P03 | 50 | £0.00 | |
| 8 | P04 | 25 | ||
| 9 | P05 | 75 | ||
| 10 | P06 | 25 | ||
| 11 | P07 | 50 | ||
| 12 | P08 | 75 | ||
| 13 | P09 | 25 | ||
| 14 | P10 | 50 | ||
| 15 |
The dollar locks it
Put a $ before the column letter and before the row number, and the reference stays put: $B$1 always points at B1, wherever you drag the formula. That's called an absolute reference.
=B5*$B$1 dragged down becomes =B6*$B$1, =B7*$B$1, and so on. In Excel you add the dollars quickly with F4 while the cursor is in the reference.
=B7*$B$1
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price per m² | 1.6 | ||
| 2 | Water charge | 12 | ||
| 3 | ||||
| 4 | Plot | m² | Rent | To pay |
| 5 | P01 | 50 | £80.00 | |
| 6 | P02 | 25 | £40.00 | |
| 7 | P03 | 50 | £80.00 | |
| 8 | P04 | 25 | ||
| 9 | P05 | 75 | ||
| 10 | P06 | 25 | ||
| 11 | P07 | 50 | ||
| 12 | P08 | 75 | ||
| 13 | P09 | 25 | ||
| 14 | P10 | 50 | ||
| 15 |
Try it yourself
In C5, work out the rent for P01 with the price from B1, and drag the formula down to C14.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price per m² | 1.6 | ||
| 2 | Water charge | 12 | ||
| 3 | ||||
| 4 | Plot | m² | Rent | To pay |
| 5 | P01 | 50 | ||
| 6 | P02 | 25 | ||
| 7 | P03 | 50 | ||
| 8 | P04 | 25 | ||
| 9 | P05 | 75 | ||
| 10 | P06 | 25 | ||
| 11 | P07 | 50 | ||
| 12 | P08 | 75 | ||
| 13 | P09 | 25 | ||
| 14 | P10 | 50 | ||
| 15 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson