Year 7 – Spreadsheets Lesson plan
Lesson 5 – Level up your data skills
Lesson 5: Level up your data skills
Introduction
This lesson will introduce learners to three more functions — COUNTIF, AVERAGE, and IF — and
to how they can sort and filter a spreadsheet. Learners will work on a larger data set to get a feel for
analysing real-world data using spreadsheets.
Learning objectives
● Analyse data
● Use a spreadsheet to sort and filter data
● Use the functions AVERAGE, COUNTIF, and IF in a spreadsheet
Key vocabulary
Header, filter, average, criterion/criteria, condition, conditional formatting
Preparation
Subject knowledge:
You need to be familiar with the spreadsheet software you use in your setting so that you can
demonstrate how to:
● Sort a whole data set by different columns, taking into account headers
● Apply filters to a data set
● Use the functions AVERAGE, COUNTIF, IF
● Apply basic conditional formatting
○ The lesson only covers how to highlight cells that match a given criterion (selecting
from predefined criterion options only); explorers will be prompted to try applying a
colour scale and using a custom formula to highlight a whole row
You will need:
● Sticky notes
● Slides
● The slides contain examples of how to use the newly introduced functions, but you
will need to change the cell references according to the spreadsheet you use
● Starter activity – Functions warm-up
● For activity 1:
○ The spreadsheet containing the data collected in lesson 3
Page 1 Last updated: 08-06-21
Year 7 – Spreadsheets Lesson plan
Lesson 5 – Level up your data skills
● Activity 2 data sets and accompanying exercises — pick one of the data set options for all
learners, or allow each learner to choose which one they want to work with
○ Option 1 (Olympics) – Data, Questions and Answers
○ Option 2 (Premier League) – Data, Questions and Answers
○ Option 3 (YouTube) – Data, Questions and Answers
● Homework activity – Plastic cleanup report should be printed
For activities 1 and 2, make sure each learner has their own individual copy of the spreadsheet file to
work with.
Assessment opportunities
The starter activity assesses learners’ knowledge of the functions they’ve learned to use so far, and of
how to use formulas and reference ranges of cells. You can assess learners’ progress as they work
through the practice questions in activity 2; the question sheets also contain some exercises to revise
functions from the previous lesson and to get more practice with charts.
Outline plan
Please note that the slide deck labels the activities in the top right-hand corner to help you navigate
the lesson.
*timings are rough guides
Starter Warm-up routine for data masters
activity
(slides 2–20) Distribute the worksheets in print or electronic format. The questions are a recap
of formulas from the previous lesson, and of functions and cell references.
5–10 mins
Answers are provided on the slides.
Activity 1 Level up your skills
(slides 21–
29) Using the data collected in lesson 3, demonstrate:
- Sorting with headers
- Filtering
15–20 mins
- Three new functions (COUNTIF, AVERAGE, IF)
Learners should try out each thing in their own separate copies of the spreadsheet.
Note: as before, you’ll need to change the cell references in the examples
according to the spreadsheet you are using.
Activity 2 Over to you
(slide 30)
Page 2 Last updated: 08-06-21
Year 7 – Spreadsheets Lesson plan
Lesson 5 – Level up your data skills
Learners now practise their new skills on a larger data set. There are three data sets
15–25 mins to choose from; each one is accompanied by its own set of questions.
We recommend that learners work on this task individually.
Instructions and practise questions are provided in a separate document, and all
work should be done directly in the spreadsheet containing the data set.
Explorer tasks:
● The question sheets include a selection of explorer tasks that prompt
learners to further explore spreadsheet functions and more advanced
conditional formatting options
● If some learners work through the exercises much faster than others, they
may attempt exercises from the other data sets
Sources of data:
Olympics – Wikipedia
Premier League – Wikipedia
YouTube – SocialBlade
Plenary Two stars and a wish
(slide 31)
Distribute sticky notes to learners and ask them to write down two new things that
5–10 mins they have learned over the course of the past five lessons, and one thing that they
have a question on or need more practise with.
The notes should be stuck on the board or a wall and used to prompt a discussion
on the whole unit. The discussion serves to refresh learners on the material in
advance of the homework and final assessment.
Homework Plastic cleanup report
(slide 32)
We recommend that you distribute the homework in paper format.
It is a revision exercise covering formulas, functions, and cell references. No
actual calculation is necessary; learners write the correct formulas into the spaces
provided, as if they were filling in cells in a real spreadsheet.
Resources are updated regularly — the latest version is available at: [Link]/tcc.
This resource is licensed under the Open Government Licence, version 3. For more information on this licence, see
[Link]/ogl.
Page 3 Last updated: 08-06-21