Absolute references ($): practice exercises
$G$1, $A2, B$1 and the F4 key. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
The assessment was marked out of the number in G1. In C2, divide the mark by G1 (column C is formatted as a percentage). Lock G1 so it doesn't move when the formula is copied down to C10.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Mark | Percentage | Max mark | 60 | ||
| 2 | Grace | 48 | |||||
| 3 | Tom | 31 | |||||
| 4 | Priya | 57 | |||||
| 5 | Marek | 39 | |||||
| 6 | Nia | 52 | |||||
| 7 | Ollie | 22 | |||||
| 8 | Lucy | 44 | |||||
| 9 | Hamza | 35 | |||||
| 10 | Rachel | 50 |
How it works
When a formula is copied, its references move with it. That's usually what you want, but not when every row needs the same cell, such as a price in F1.
A $ locks the part that follows it. $F$1 always points at F1. $A3 keeps column A but lets the row change, and B$2 keeps row 2 but lets the column change.
Shortcut: press F4 with the cursor in a reference to cycle through the options. It works here too.
=B2*$F$1 =$A3*B$2
What happens when you copy a formula
When you copy a formula down, every reference in it moves down too. That's what makes copying useful: =B2*2 in C2 becomes =B3*2 in C3, =B4*2 in C4, and so on.
But sometimes every row needs the same cell: a price, a target, a total. Then the moving is a problem. The next card shows why.
Worked example: the problem
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Quantity | Cost | Price | £4.00 | |
| 2 | Pens | 3 | ||||
| 3 | Pads | 5 | ||||
| 4 | Tape | 2 | ||||
| 5 | Clips | 6 |
=B2*F1in . In C2, quantity times price:=B2*F1gives £12. Right.=B2*F1in . Now copy it down. In C3 the formula has become=B3*F2: the price reference moved down too, onto an empty cell, so the answer is £0. The same happens all the way down.
Worked example: the fix
Shortcut: with the cursor in or just after a reference, press F4 to add the $ signs. Press it again to cycle through $F$1, F$1, $F1 and F1.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Quantity | Cost | Price | £4.00 | |
| 2 | Pens | 3 | ||||
| 3 | Pads | 5 | ||||
| 4 | Tape | 2 | ||||
| 5 | Clips | 6 |
=B2*$F$1in . A $ locks the part straight after it.$F$1locks the column and the row, so it always points at F1.=B2*$F$1in . Copy it down again. In C4 it's=B4*$F$1: B4 moved with the row, F1 stayed put. Every answer is right.
Locking just one part
| A | B | C | |
|---|---|---|---|
| 1 | Pay for each shift | ||
| 2 | Hours ↓ Rate → | £10.00 | £12.00 |
| 3 | 2 | ||
| 4 | 3 |
=$A3*B$2in . Pay is hours times rate.$A3locks column A but lets the row move.B$2locks row 2 but lets the column move.=$A3*B$2in . Copied to C4 it's=$A4*C$2: hours still come from column A, and the rate still comes from row 2. 3 hours at £12 is £36.
What you'll practise
- The assessment was marked out of the number in G1. In C2, divide the mark by G1 (column C is formatted as a percentage). Lock G1 so it doesn't move when the formula is copied down to C10.
- Staff are paying for the summer social in instalments, and each instalment is the amount in G1. In C2, work out how much each person has paid so far.
- Make a multiplication grid. In B2, multiply the number in column A by the number in row 1. Your formula will be copied across to G and down to row 7.
- Total sales are in B12. In C2, work out each region's share of the total. Your formula will be copied down to C11.