Spreadsheets and Data — Year 4 Lesson Plan
National Curriculum: Computing KS2 — select, use and combine a variety of software to accomplish given goals, including collecting, analysing, evaluating and presenting data and information.
Overview
Pupils are introduced to spreadsheets as a tool for organising, calculating, and presenting data. They learn the structure of a spreadsheet (cells, rows, columns), enter a class dataset, and use the SUM and AVERAGE formulas to perform calculations automatically. Pupils sort and filter data to answer questions, and create a simple bar chart from the data, developing the understanding that formulas and charts are powerful ways to process and communicate information.
Learning Objectives
- Describe the structure of a spreadsheet, including cells, rows, columns, and cell references.
- Enter data accurately into a spreadsheet and format it clearly.
- Use SUM and AVERAGE formulas to calculate totals and means from a dataset.
- Sort a dataset and create a chart to present findings clearly.
Key Vocabulary
Suggested Lesson Structure
Give pupils a printed table of data (e.g. points scored by six teams over five weeks) and ask them to calculate the total for one team by adding the numbers manually. Discuss: how long did that take? How many mistakes might we make if we had 500 rows? Introduce the idea that a spreadsheet can do these calculations instantly and accurately.
Open a blank spreadsheet and demonstrate: label row 1 as headers (Name, Week 1, Week 2, Week 3, Total, Average); enter data for four fictional pupils across three weeks; click cell F2 and type =SUM(B2:D2) and explain that the colon means 'from B2 to D2'. Press Enter and watch the total appear. Repeat for AVERAGE. Then sort the data by Total (descending) to find the highest scorer. Insert a bar chart to show the totals visually, adjusting the chart title and axis labels.
Pupils open a pre-prepared spreadsheet file with a partially completed dataset. They fill in missing data, write SUM formulas for the Total column, and write AVERAGE formulas for the Average column. Pairs compare their formula results and discuss any differences. Teacher circulates, prompting: what does the colon in the formula mean? What would happen if you accidentally included an extra cell?
Pupils create a new spreadsheet from scratch to record data from a short class survey they conducted in a previous lesson (or a provided dataset). They must include at least one SUM and one AVERAGE formula, sort the data to find the highest and lowest values, and insert a bar chart with a title.
Display a deliberately incorrect formula: =SUM(B2,D2) instead of =SUM(B2:D2). Ask: what is wrong here? What would this formula actually calculate? Establish the difference between a comma (two separate cells) and a colon (a range). Discuss: why is accuracy in formula writing important?
Common Misconceptions
- Pupils frequently forget the equals sign at the start of a formula and type SUM(B2:D2) as plain text — the spreadsheet displays it as text rather than calculating, which confuses pupils who expect a number.
- Some pupils believe that sorting the data deletes or rearranges the original data permanently — clarify that sorting rearranges the display but the data itself remains intact and can be re-sorted.
Prior Knowledge
Pupils should already be able to:
- Experience collecting data and creating pictograms or bar charts in earlier years.
- Ability to identify rows and columns in a table from Maths work.
Want a personalised version of this lesson?
Use Staffroom to generate a complete lesson plan tailored to your class — add context about ability, recent learning, or specific pupils and get a plan ready to teach. Free trial, no card required.