GoalName any cell in a spreadsheet, tell labels from data, and write a formula that calculates from other cells.
New words
cell · a box in a spreadsheet where a row and a column meetcell reference · the name of a cell: the column letter and then the row number, such as C4label · a heading or word that explains the data next to itformula · a calculation that starts with = and uses numbers, cell references and symbols
Explanation
A spreadsheet is a grid of boxes called cells. The columns have letters and the rows have numbers. The name of a cell is its column letter followed by its row number, so C4 is in column C and row 4. This name is the cell reference. A cell can hold a label, a number or a formula. A label is text that explains the data, such as Price, and it is often made bold so that it stands out. A formula always starts with an = sign. It can use cell references and the symbols + (add), − (subtract), * (multiply) and / (divide). The cell shows the answer, and the formula appears in the formula bar.
D2fx=B2*C2
A
B
C
D
1
Plant
Price
Sold
Income
2
Basil
2
14
28
3
Mint
3
9
27
4
Tomato
4
6
24
5
Pepper
5
4
20
Worked examples
Cell reference
What it holds
Kind
B3
3
number: the price of mint
C5
4
number: how many peppers were sold
A4
Tomato
label
D1
Income
label
Cell references are like map squares. This table reads the plant-sale sheet above: it says what is stored in some cells.
A
B
C
D
1
Plant
Price
Sold
Income
2
Basil
2
20
40
3
Mint
3
9
27
4
Tomato
4
6
24
5
Pepper
5
4
20
A formula keeps the answer up to date. Here the club sold 20 baskets of basil instead of 14. Only C2 was changed, but the formula in D2 worked out the new income by itself: 2 × 20 = 40. A typed-in answer would have stayed at 28 and been wrong.
In maths
Formula
Result
3 × 9
=B3*C3
27
9 ÷ 3
=C3/B3
3
3 + 9
=B3+C3
12
9 − 3
=C3-B3
6
3 × 9 without =
B3*C3
shows the text B3*C3
The four calculation symbols look different in a spreadsheet. Use * for ‘times’ and / for ‘divided by’. Here B3 holds 3 and C3 holds 9. If you leave out the = sign, the spreadsheet treats your typing as plain text.
Class activity
Paper spreadsheet (no computer). On squared paper draw a grid with columns A to D and rows 1 to 5, and write the letters and numbers along the edges. Fill in a small table about your family’s fruit bowl: labels in row 1 (Fruit, Price, Number, Cost) and three fruits below. A partner calls out cell references such as ‘C3!’ and you point to the cell and read what it holds. Then write the formula for the first Cost cell, say which cells it uses, and work out the answer by hand. If you have a computer or tablet, an adult can help you type the same table into a spreadsheet and check your answers.
Practice
1
Look at the bakery sheet. Which cell holds the price of the cake, and which cell holds the number of rolls?
A
B
C
D
1
Item
Price
Number
Cost
2
Bread
3
10
3
Rolls
1
25
4
Cake
8
4
The price of the cake is in B4. The number of rolls is in C3.
2
In the bakery sheet, write the formula for D2 that works out the cost of the bread. What number does it show? Then write the formula for D4.
Three people typed a formula into D2 of the bakery sheet. Say whether each one works. If it does not, say what is wrong.
Typed in D2
Does it work?
B2*C2
?
=B2 x C2
?
=B2*C2
?
B2*C2 has no = sign, so the sheet shows it as text. =B2 x C2 does not work, because the letter x is not a calculation symbol; use *. =B2*C2 works and shows 30.
4
The baker sells 5 more rolls, so C3 changes from 25 to 30. Cost cell D3 holds the formula =B3*C3. What does D3 show now? What would happen if the baker had typed the answer 25 into D3 instead of a formula?
D3 shows 30 (1 × 30). If the answer 25 had been typed, D3 would still show 25, which is now wrong, because a typed number does not change when other cells change.