Simon Haughton's Blog

Introducing Spreadsheets - Planning

Spreadsheet Quiz

Here's a nice activity idea I've just come up with to help consolidate children's understanding of basic formulae whilst at the same time teaching them how to use conditional formatting on cells.

Introducing Spreadsheets - Week 5

For the final week of the spreadsheet topic, I decided to set the children two investigations to try and solve to reinforce the formula skills taught previously and to help them become more appreciative of the range of applications spreadsheets can be used for.

The first challenge required them to work out which method of getting pocket money over a ten week period and to then decide which one they would they would choose to have (i.e. they had to identify which one would give them the most money in total after ten weeks). Since the four possible methods were very similar it meant that mental methods couldn’t be used at all and that a structured table in a spreadsheet would be the most convenient way to find a solution. Whilst this only took the children about 15 minutes to complete they did seem to like doing it and clearly understood what they were required to do.

Download Pocket Money Problem

The second challenge required them to enter pretend data into an imaginary class register and to then use this to work out their overall attendance for the week as a percentage. This required them to enter a range of formulae: =SUM() to work out each child’s total attendance, =SUM() to work out the class’ overall attendance and a / and * to convert this to a percentage (no. sessions attended / total no. possible sessions * 100). To ensure that the task was achievable, I tried to keep it as simple as possible – so just 1s and 0s to represent attendance (no different codes for authorised/unauthorised absences!), only 11 children’s data to work with and the final percentage formula’s structure clearly explained so they knew how to work it out. Once done, I also opted to challenge the more confident children to try exploring the effects of changing data in the register to try and reach particular target attendance percentages (e.g. 80%, 95%, 100% etc.) Again, they all completed the investigation well and seemed interested in learning about how the school works out who wins the weekly attendance award given out each Friday in our achievement assembly.

Download Attendance Register Investigation

We finished with a discussion about the benefits of using spreadsheets to solve such investigations compared to using mental methods or handwritten calculations (considering: accuracy, neatness and the concept of formulae results updating automatically).

Some people don’t always think that spreadsheets are an exciting topic to teach, but hopefully these last five posts of me sharing some of the activities I do with our children to introduce them to the basic (but fundamental) skills and key concepts have helped to inspire you and made you realise the enjoyment which can be had from learning how to work with them.

Introducing Spreadsheets - Week 4

For the fourth lesson I decided opt for a bit of an unusual spreadsheet activity to let the children consolidate their understanding of =SUM() formulae from last time and to also teach them how it can be adapted to provided statistical information on a set of numbers.

 

To begin, I let them all have a go at playing an online game called Starry Night where the aim was to keep the stars in the air by bouncing them on a moving bubble controlled with the mouse (you could easily use something different here but I picked this because it was: short, intuitive to play and had an outcome based on mouse control skills as opposed to curriculum knowledge). Having completed each game, I asked the children to go to a quick Google Form I’d made to type in their score. After playing for three goes – which took only a few minutes - they then had to submit their three scores so that they could be displayed on the main Google Spreadsheet I’d displayed on the IWB (which is always greeted with a sense of wonder when it updates in real-time). 

 

Next, I asked them to load up a blank Excel sheet and to design a table to analyse the scores achieved by five of their friends. This only needed to be simple – one column for the child’s name, three for each of their scores and a third column for their total score. Once done, they then had to input the scores taken from the form results table they had opened in their web browser on their laptop (which by now contained everyone’s scores since Google only updates tables published as web pages every 5 minutes), before then entering =SUM() formulae to work out each child’s overall total score. I reminded them of the importance of checking formulae results seem reasonable here too as a way of checking for mistakes.

Following this, at the bottom of the table, I then asked the children to construct two additional formulae:  

• a =MIN() formula to identify the minimum total points scored by a child;
• a =MAX() formula to identify the maximum total points scored by a child; 

This was quite straightforward since the formulae required all used a similar structure to the =SUM() formula. (Incidentally, choosing a game of skill rather than knowledge for them to play for this lesson avoided any unnecessary humiliation of them working out who scored the least and thus who is the least able academically.)

Finally to finish, I let the children use the formatting tools to improve the appearance of their work – including encouraging them to add borders around the table cells and to correctly align the numbers to the right.

I felt that this lesson was highly successfully since it managed to teach the children new skills in a fun and exciting way whilst at the same time also demonstrated to them the benefits of using an online collaboration tool to collect data for analysis. The only thing I would have added to extend it further if time had allowed would have been to let them try replacing the children used with other children’s scores so that they could see how the statistical information (i.e. the formulae results) would update automatically in such a model – but hey, I was quite impressed to fit game playing, Google forms and spreadsheet creation into an hour’s slot on such a hot day!

Introducing Spreadsheets - Week 3

Week three of the spreadsheets unit and time to introduce =SUM() formulae. In the past when I’ve shown children this, I’ve tended to make the mistake of making the initial usage a little too hard – so this year I opted to let them begin with a very simple task so the numbers involved wouldn’t be too difficult for them to interpret:

Download Race Points Spreadsheet

This seemed to work well with a lot fewer children making mistakes this year (and asking for help when circular reference errors come up). I also created this slide to display on the IWB for them to refer to if needed, which also seemed to help:

For the next task, I then asked them to do a common modelling activity to reinforce their understanding of the formula and to show them an example of a real-world application of a spreadsheet which included currency. The idea was easy – the children had to select 12 items of food/drink which they would buy for a week’s worth of lunch box meals and then work out how much change they would get from £10.

Download Shopping Bills Spreadsheet

After copying each desired item’s name and price over from the prices sheet onto the receipt sheet, they then had to enter the appropriate formulae needed to calculate the total cost, thus helping them appreciate the benefits of using =SUM() over adding each figure together in one long formula. They also had to enter a simple subtraction formula then to work out the amount of change that would be received from £10 – which potentially might have required them to amend their earlier purchases if the result was a negative number (i.e. they had overspent).

This activity also worked nicely, with all the children seeming to enjoy beginning to use a spreadsheet for more meaningful/relevant purpose, and with a few even managing to go onto my extension task of pricing up the ingredients needed to bake a cake.

Introducing Spreadsheets - Week 2

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.

Introducing Spreadsheets - Week 1

‘Spreadsheets’ isn’t the most creative of topics, so I try to make the way I introduce it in Year 5 as interesting as possible for the children.

Following a short talk about some key spreadsheet terminology – ‘row’, ‘column’ and ‘cell’, I begin with a simple activity to help the children learn how to reference cells correctly – with each cell automatically turning green when a child types in the correct cell reference.

Download Wizard's Challenge

Following this, I then teach them how to input simple formulae to make the spreadsheet quickly and accurately perform a calculation with two numbers. To do this, I ask the children to complete an activity I originally made three years ago now called ‘The Gold Mine’. This contains three levels (each on separate sheets) which each have pairs of numbers to perform calculations on and which turn gold if the correct formulae are entered to reach the correct answer:

• Level 1 – Just addition calculations (+) 
• Level 2 – A mixture of addition and subtraction calculations (+ -) 
• Level 3 – A mixture of additions, subtractions, divisions and multiplications (+ - / *)

Download The Gold Mine

This usually takes the class about half-an-hour to complete and they always like doing it – not only because it is disguised well with lots of colours and clip art to distract them from what isn’t the most thrilling of tasks, but also because they seem to enjoy the challenge of working out what needs to be included in each formula and the solving of any problems which they might encounter (i.e. the many cases of referencing cells incorrectly or doing the wrong operation).

Hopefully these couple of activities might be helpful to anyone else who teaches simple spreadsheet skills and if I feel in the mood I might share some of the other spreadsheet activities I do over the coming weeks too.