Last week we looked at referencing cells and entering simple formulae, so to build on these skills this week, I spent a lesson teaching the children why we use cell references (and not the actual number values) in formulae.

I began by showing them a blank *Excel *sheet and asking them to copy the following table into it:

We then had a quick recap about how to work out a formulae before they then went and inputted twelve individual formulae themselves to work out the product of each pair of numbers (e.g. =A4*B4). Once done, I then told them that I now wanted them to show me the 8 times table (cue some unhappy faces) – by changing the number B values to the number 8 (**cue lots of happy faces when they realised the formulae results updated automatically**). This is always an interesting task because you get to see which children followed your instructions and which children decided to cheat earlier on and input the correct answers instead of the formulae – despite me telling them (though thankfully it was only a couple this year).

Once they’d spent some time testing and experimenting with their spreadsheet by producing different times tables, I then let them spend 5 minutes changing its appearance to make it look more attractive. At this early level I simply let them experiment with different cell fill colours and border styles to get them used to the tools – discussing conventions like only using thicker borders for the outline will come later. **Doing this sort of thing is always something children love so it’s no surprise that they easily managed to make their tables look really fancy. **

For the next task I then asked them to try completing a short spreadsheet model investigation about a sweet shop. It’s an idea I originally got from a secondary school but which I then modified a bit to make it more Year 5-friendly – notably by making the numbers easier to understand (so £5 instead of £500,000) and changing the context (from zoo animals to sweets in a shop). In the investigation, the children need to find the correct number of sweets Paul can buy so that he has 14 sweets that cost £10 altogether.

Download Sweets Problem Spreadsheet

I first asked them to enter the formulae into the two blue cells to work out the totals for the quantity and price columns (thus helping them realise that a formula can contain multiple cell references). Once done, I then gave them about 10 minutes or so to use trial and error to solve the problem by entering numbers into the yellow cells. We managed to find two solutions today, but I’ve added a space for recording a third solution in the spreadsheet just in case they find one (and to keep them busy if there isn’t). I also encouraged them to try and spot any trends in the numbers (e.g. *Can you buy more packets of jelly babies than cola bottles with £10?*).

Once done, I then ended the lesson with a plenary discussing the advantages of using a spreadsheet to solve such problems compared to using a pencil and paper – notably **that it lets you try out lots of different possibilities more quickly, neatly (no rubbings out) and more accurately.**