GoalExplain relative and absolute cell references, and write a conditional formatting rule that highlights important data.
New words
relative reference · a cell reference that changes when the formula is copied to another cellabsolute reference · a cell reference with $ signs, such as $B$1, that stays the same when copiedfill handle · the small square at the corner of a cell that you drag to copy its formulaconditional formatting · a rule that changes how a cell looks only when its value meets a condition
Explanation
When you copy a formula down a column, each cell reference moves down by the same number of rows. This is a relative reference, and it is usually helpful, because each row uses its own numbers. But sometimes one value must stay in the same place, such as a single price. Putting $ before the column letter and before the row number locks the cell: $B$1 is an absolute reference and never changes. In a spreadsheet you drag the fill handle to copy a formula. Conditional formatting is a rule with three parts: the cells, a condition such as = 0 or > 40, and a format such as a green fill. The format is shown only when the condition is true. Change a number in the model and the colours change, which helps people see quickly what is happening.
Formula in C3
Copied to C4 it becomes
Kind of reference
=B3*2
=B4*2
relative: moves down
=$B$1*B3
=$B$1*B4
$B$1 is absolute, B3 is relative
Worked examples
C4fx=B2*B4
A
B
C
1
Ticket price
4
2
Class
Tickets
Income
3
3A
12
48
4
3B
9
#VALUE!
5
3C
15
180
6
3D
7
63
The school play sells tickets at the price in B1. Ravi typed =B1*B3 in C3 and copied it down. The relative reference B1 moved down too, so C4 now points to B2, which is a label, and C5 uses the wrong numbers. 12 × 15 = 180 is not the income of class 3C.
C4fx=$B$1*B4
A
B
C
1
Ticket price
4
2
Class
Tickets
Income
3
3A
12
48
4
3B
9
36
5
3C
15
60
6
3D
7
28
The fix is to lock the price: =$B$1*B3. Now every row uses B1, and only the number of tickets moves down. All four incomes are correct: 4 × 12 = 48, 4 × 9 = 36, 4 × 15 = 60 and 4 × 7 = 28.
A
B
C
1
Ticket price
4
2
Class
Tickets
Income
3
3A
12
48
4
3B
9
36
5
3C
15
60
6
3D
7
28
A teacher sets this rule: cells C3:C6, condition ‘greater than 40’, format green fill. Only the incomes above 40 are shaded: 48 and 60. The values 36 and 28 are not above 40, so they stay plain. If the ticket price is changed from 4 to 5, the incomes become 60, 45, 75 and 35, and the shading moves to three cells.
Class activity
Colour rule game (no computer). Draw a table of ten numbers on paper, for example the temperature at noon on ten days. A partner calls a rule such as ‘less than 15’ or ‘equal to 20’ or ‘greater than 25’, and you colour only the cells that meet it. Check each other’s colouring. Then play ‘copy the formula’: write =B2*3 in C2 on paper, and then write by hand the formulas that would appear in C3, C4 and C5 after copying down. Finally write one with a locked cell, such as =$E$1*B2, and copy it down again. If you have a computer or tablet, an adult can help you try a colour rule.
Practice
1
The formula =B2*C2 is in D2 and is copied down. Write the formulas that appear in D3 and D5.
D3 holds =B3*C3. D5 holds =B5*C5. Both references move down with the formula.
2
A stall sells cups of juice at the price in B1. Write the formula for C3 so that it can be copied to C4 and C5, and work out the three incomes.
The rule is ‘cells B2:B6, less than 10, green fill’. Which cells turn green?
A
B
1
Day
Litres
2
Mon
14
3
Tue
8
4
Wed
10
5
Thu
3
6
Fri
12
B3 (8) and B5 (3). The value 10 in B4 is equal to 10, not less than 10, so it stays plain.
4
Write a conditional formatting rule (cells, condition, format) that shows a red fill when a library book has fewer than 2 copies left, in cells D2:D9. Why is a colour rule better than checking every number by eye?
Cells D2:D9, condition less than 2, format red fill. A colour rule works automatically every time a number changes, and the important rows stand out at once, so nothing is missed.