If you’re a teacher of computing, spreadsheets are a great way into computational thinking, ie breaking down a problem into its components, deciding how to represent them in a model, and generalising the solution so that it will work with different data. They are a good vehicle for teaching abstract concepts like conditional statements, eg IF x THEN y. Spreadsheets are also able to put a completely new spin on other subjects too. For example, an English teacher might use one to show her pupils how a play or book develops from start to finish, plotting things like the number of amusing passages and negative incidents to create a visual representation of the plot. Geography teachers could get kids to use spreadsheets to plot rainfall in the local area; whilst in science, they can be brought into play in order to model the solar system or how plant growth is affected by light. In other words, the spreadsheet is an incredible useful and flexible tool. Moreover, you don’t have to be a ‘techie’ to use one very effectively.
WHY TEACH THIS?
don’t have to teach them any more, do we?” Strictly speaking, probably not – but if you did you would tick quite a few boxes. Moreover, they can profitably be used across the curriculum and in the context of work-related learning, as well as in computing itself..
STARTER ACTIVITY
Here’s a quick way of grabbing the students’ interest. Put them into pairs and give each pair £100 (well, an imaginary £100, unless you’ve just won the Lottery). Then get them to spend it on a party they are organising.
Pretty easy, right? Well, yes and no. To make the exercise more interesting, you have to put in constraints. For example, they mustn’t spend a penny more or a penny less than the £100. Give them a list of items to choose from and their prices, or tell them to get the information from the web. Finally, they have to spend the money on a range of products; they’re not allowed to buy 200 bags of Twiglets and shriek “Job done!”
What would they be learning in this, possibly their very first spreadsheet activity?
- How to use the SUM function (built in formula), which provides a fast way of adding all the items in a list.
- How the spreadsheet grid works, because they would have to have a formula that says, in effect, ‘Subtract the total spending from the total budget’; in the spreadsheet, that might look like: =D15-D14.
- Importantly, they will be doing modelling, especially if you throw in the occasional curved ball like “Sorry, KitKats have just gone up by 5p”, ie “What if prices change? How will that affect my spending?”
Consider taking this simple model further. For example, pupils could insert a formula that displays a message like “Sorry, you’ve gone over your budget” or “Hey, you still have some money left in the pot!” (Hint: you would need to use the IF function.)
Note that this is more purposeful than the usual type of spreadsheet starter, in which you bore the kids to death with questions about the grid (eg, What’s in cell B23?) It’s much better to give them a reason for the activity, and to get them modelling straight away. as possible!
MAIN ACTIVITIES
A good way to approach the use of spreadsheets is to think of the kinds of questions to which you want answers.
SUGGESTION 1
For example, take the question, “Where is a good place to go on holiday during the Christmas break?” Start with an assumption (constraint) or two, like “It has to be hot, but not too hot”. The first thing the students will need to do is agree on what terms like “hot” and “not too hot” mean in terms of numbers. Next, they might to try and draw what the spreadsheet will look like, based on how they will use it (in other words, we’re starting from the end point). In short, they will need to be able to look at a table of temperatures and see straight away the places that meet their criteria, with no effort at all. students a spreadsheet containing the names of every country in the EU, plus Australia, NZ, Russia and USA, and their average temperatures from mid-July to the beginning of September (a longish list makes it harder for them to spot the ‘answer’ immediately). Providing a pre-filled in spreadsheet containing all the data – but none of the formulae – allows the students to concentrate on modelling rather than data entry.
They should set up the model such that as soon as they have entered the data the spreadsheet tells them exactly what they want to know.
Here are a few things to look out for when you are checking students’ work:
- Inclusion of the Average function.
- Inclusion of an IF function that says, in effect, ‘If the temperature is between X and Y then this country is OK, otherwise it isn’t.
- Use of ‘Conditional Formatting’, eg the ‘OK’ cells have a yellow background while the ‘not OK’ ones have a red background.
This sort of spreadsheet can be used to ask other sorts of questions, especially if you add in other types of data as well, such as levels of rainfall.
SUGGESTION 2
Another nice idea to try is to make the spreadsheet super user-friendly. In the party spreadsheet, for example, get the students to figure out how to have the spreadsheet display sentences like “You have gone £21.13p over budget” (tip: they will need to use the & operator). In the country spreadsheet, display a sentence like “The ideal holiday destination for you is Portugal” (hint: they will probably need to use the Index and Match functions).
SUGGESTION 3
If you use Excel, introduce programming through using Visual Basic for Applications by recording a macro from the Developer menu and then using Alt-F11 to examine (and alter) the code (you may need to ask your technician to change the security settings before trying this).
SUMMARY
Students need to understand that spreadsheets are for modelling, not just putting data into a neat table or being used as an overgrown calculator. Check that they have used formulae rather than just typed numbers in by getting them to press Ctrl and the ` key.
HOME LEARNING
Ask students to come up with a list of areas where spreadsheet could be used. Alternatively, tell them to create a spreadsheet that could be used for one of the following situations:
- Managing the school’s budget.
- Handling sports day results.
- Dealing with lateness and absenteeism.
- A room booking system for parents’ evenings.
- Dealing with hard questions, like: How would having a school bus affect pupil safety outside the school? Should the school spend a government grant on a new building or a complete refurb?
ABOUT OUR EXPERT
Terry Freedman is an independent educational ICT and computing consultant, and publishes the ICT & Computing in Education website at www.ictineducation.org and the Digital Education ezine, found at www.ictineducation.org/ newsletter
