Confident Cells

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.

Your turn

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.

ABCDEFG
1EmployeeMarkPercentageMax mark60
2Grace48
3Tom31
4Priya57
5Marek39
6Nia52
7Ollie22
8Lucy44
9Hamza35
10Rachel50

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

ABCDEF
1ItemQuantityCostPrice£4.00
2Pens3
3Pads5
4Tape2
5Clips6
  1. =B2*F1 in . In C2, quantity times price: =B2*F1 gives £12. Right.
  2. =B2*F1 in . 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.

ABCDEF
1ItemQuantityCostPrice£4.00
2Pens3
3Pads5
4Tape2
5Clips6
  1. =B2*$F$1 in . A $ locks the part straight after it. $F$1 locks the column and the row, so it always points at F1.
  2. =B2*$F$1 in . 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

ABC
1Pay for each shift
2Hours ↓ Rate →£10.00£12.00
32
43
  1. =$A3*B$2 in . Pay is hours times rate. $A3 locks column A but lets the row move. B$2 locks row 2 but lets the column move.
  2. =$A3*B$2 in . 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

  1. 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.
  2. 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.
  3. 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.
  4. Total sales are in B12. In C2, work out each region's share of the total. Your formula will be copied down to C11.