Learning Excel: Mixed references
Locking just half
The committee still can't decide on the price: £1.40, £1.60, £1.80 or £2 per m²? Ruth wants a table with the rent for every size of plot at every price. One formula, filled in every direction.
A table in two directions
On the Rates sheet, the areas run down column A and the prices run across row 2. Every cell of the table needs area times price.
Drag a formula to the right and the columns move along. Drag it down and the rows move along. Here, only half of each reference should move along.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rent per plot at each price per m² | ||||
| 2 | m² | £1.40 | £1.60 | £1.80 | £2.00 |
| 3 | 25 | ||||
| 4 | 50 | ||||
| 5 | 75 | ||||
| 6 | 100 | ||||
| 7 |
One dollar
You can also lock just one thing. $A3 keeps column A fixed, but the row moves along. B$2 keeps row 2 fixed, but the column moves along. A reference like that is called mixed.
=$A3*B$2 in B3, filled across the whole table, always takes the area from column A and the price from row 2. Look at the formula in D5.
=$A5*D$2
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rent per plot at each price per m² | ||||
| 2 | m² | £1.40 | £1.60 | £1.80 | £2.00 |
| 3 | 25 | £35.00 | £40.00 | £45.00 | £50.00 |
| 4 | 50 | £70.00 | £80.00 | £90.00 | £100.00 |
| 5 | 75 | £105.00 | £120.00 | £135.00 | £150.00 |
| 6 | 100 | £140.00 | £160.00 | £180.00 | £200.00 |
| 7 |
Try it yourself
In B3, write the rent for 25 m² at £1.40, in a way that lets you fill the formula across the whole table B3:E6.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rent per plot at each price per m² | ||||
| 2 | m² | £1.40 | £1.60 | £1.80 | £2.00 |
| 3 | 25 | ||||
| 4 | 50 | ||||
| 5 | 75 | ||||
| 6 | 100 | ||||
| 7 |
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