Teaching Spreadsheets - A guide to introducing Spreadsheets to Primary Students
Understanding Spreadsheets is often an area of the curriculum that is missed. I would like to offer some ideas on how Spreadsheets can be taught in school from Year 3 to Year 6. I have worked with many secondary schools and observed many teachers and all to often I see that older secondary students don't know how to use spreadsheets. These are tools that most professions use in some capacity, so embedding this understanding at the Primary School level is essential, as many secondary schools make the assumption that these skills have been covered.
One of the misconceptions when teaching spreadsheets is to teach Excel. This is wrong, as Excel is a brand of spreadsheet made by Microsoft. The way I describe this to students is to talk about the different brands out there and using Spreadsheet as the umbrella term. Under this we can put all the other brands, Google Sheets, Apple Numbers, OpenOffice Calc, Lotus 123 and I'm sure there are many more. So we must teach Spreadsheets as a generic term and ensure the students understand the different brands of spreadsheet out there.
So how can we teach spreadsheet effectively in Year 3, 4, 5 and 6? Giving the students a basic understanding of what they do and then ensuring they understand the power of a spreadsheet. I often tell the students that spreadsheets can often make mathematics much easier, as they do the calculations for you, therefore giving more time to understand the mathematical concepts.
Year 2 & 3
In Year 2 & 3 I have been teaching spreadsheets so they can achieve the following skills:
Recognise the elements of a spreadsheet (cell, row, column)
Move to a specific cell on a spreadsheet (eg C7)
Enter text or numbers & move among cells in a spreadsheet
Select & format a cell or range of cells
Task 1
To start with we go on a Spreadsheet Treasure Hunt. This can be done in any type of spreadsheet, but the example below has been created in Google Sheets.
The treasure is hidden behind one cell. It is a link to a game of Battleships, but you can change this to a link to anything.
NB: To convert this to either Excel, Numbers or other types of spreadsheet, open the spreadsheet in Google by clicking the link and copying, then download the spreadsheet as an xlsx (Excel) spreadsheet. You can then open this in many Spreadsheet applications.
The students will then need to start at the start position and go the cell instructed. Then each cell from then on, they use the Fill tool to change the cell colour. They will then see the new instruction.
Task 2
Following on from this I would then ask them to replicate a spreadsheet that I display on the board. Similar to one below. In this second exercise, they need to insert text and use their formatting skills to change the cell colours.
To distribute both of these tasks I would use Google Classroom by creating a new assignment. The students would need to have Google accounts so they can access Google Classroom.
To understand how you can use Google Classroom, you can watch the video below which explains how to create an assignment.
Year 4
With Year fours we want to move the students on further with additional skills. New skills to consider would be:
Organise and present data in a data handling application
Use own collected data to create and format graphs (eg, bar chart, pie chart)
To start this we would revisit the Year 3 spreadsheet above and add a graph. So a starter challenge would be recreate the above spreadsheet, but in addition the students would need to find out how to add a graph to the spreadsheet. The students can experiment with different types of graph and then open up a class discussion to determine which graph is best to give an analysis of the data.
Students would then need to come up with three questions they need to ask about the data. Such as :
Which colour car passed the school gate the most?
Did how many more or less yellow cars were there than the red cars?
Further lessons would then follow by linking to a science experiment where the students create a spreadsheet based on the results of the experiment.
Year 5
In Year 5 we start to look at simple formulas and how the students can use them. The skills we look are :
Enter & use simple formulae (eg A2+B2)
Use basic functions (eg, SUM, AVERAGE etc)
Use drag and copy to apply functions & formulae to a range of cells
Task 1
Open the spreadsheet below so students can start working through how they can add formulas to a spreadsheet. The first tab is a Starter challenge. The students would need to work out how to do the final cell, as this may have not been taught. They would need to know that all formulas in a spreadsheet start with an "=" sign.
Task 2
In this task students use international traffic cams to record data. Students to work in two or threes. Assign each group to a Traffic cam from around the world.
Traffic webcams around the world
Starter Activity
Show the different traffic cams around the world. Decide on how much time you will have to count the traffic when you watch the traffic cam.
Activity
Students to watch the traffic cams at exactly the same time for the exact same duration. The should record the number of vehicles using a tally chart on a piece of paper.
Once the time limit is up, students to transfer their data onto the spreadsheet.
Ask students to start asking questions about the data they have gathered.
Year 6
With Year 6 we want them to use many of the skills that they have used since Year 3. So I devised a set of tutorials where students create their own Times Table Quiz using a spreadsheet. See the info video opposite. These tutorials tackle all the following skills:
The use of cells, columns and rows
Copying down of cells
Central aligning
Using single quote to enter special characters as text
Basic formula
Using the Dollar sign to fix a formula position
Number sentence formula
The asterisk as a multiplication symbol
Conditions using the IF Function
Conditional Formatting
Hiding columns
Embedded functions with other functions
Formating cells with the fill option
Colouring cells based on conditions
This project has many extensions and can be linked with teacher Algebra. The video below outlines the project and what the students we do to create their own Times Table Quiz.
Lesson 1
This lesson will look at how to achieve the following:
The use of cells, columns and rows
Copying down of cells
Central aligning
Using single quote to enter special characters as text
Basic formula Using the
Dollar sign to fix a formula position
Number sentence formula
The asterisk as a multiplication symbol
Times table Quiz using Spreadsheets Lesson 1 - alignment, basic formula, dollar sign, asterisk, copy
Lesson 2
In this lesson you will use the following to turn this spreadsheet into a quiz.
Conditions using the IF Function
Hiding columns
Times table Quiz using Spreadsheets Lesson 2 - If then else conditions, hiding columns, Google sheet
Lesson 3
During this lesson you will embed two functions with the same formula. Both these functions will be the IF statement. You will understand more fully how the IF statement works.
Times table Quiz using Spreadsheets Lesson 3 - embed functions, IF THEN ELSE condition, Google Sheet
Lesson 4
This lesson will focus on formatting. You will find out how to format the spreadsheet with different colours. This is a good teaching strategy for teaching younger children.
This lesson will also expand on the conditions used from previous lessons by looking at conditional formatting. This changes the colour of a cell depending on a condition.
Lesson 5
Using this timestable method within a spreadsheet you can teach some concepts of algebraic equations by using the spreadsheet to substitute. You could make these as complex as possible getting students to develop a number sentence in the spreadsheet based on the equation.
The Spreadsheet Challenges
The following links are Spreadsheet Challenges for student in secondary or student in upper primary years that want to take spreadsheets to the next level.
