Spreadsheet Masters
National Curriculum Links - Computing KS2
The content of this plan cover the following National Curriculum strands:
- design, write and debug programs that accomplish specific goals,
- use logical reasoning to explain how some simple algorithms work and to detect and correct errors
- select, use and combine a variety of software (including internet services) on a range of digital devices to design and create a range of programs, systems and content that accomplish given goals, including collecting, analysing, evaluating and presenting data and information
Why this? What does it build on?
This unit builds directly on the Year 3 unit Databases, where spreadsheets are used in a simple way, where the focus is on collecting, sorting and searching data. The use of spreadsheet formulas and conditional formatting in this unit is supported by most of our coding units of work. Formulas are a type of algorithm to achieve specific goals, and conditions are added to the code to decide what happens and when it happens. This is referred to as selection in the national curriculum objectives.
What comes next?
The application and use of spreadsheets will continue in Key Stage 3 and beyond, as students apply the skills learned here to projects such as Making the News in Year 7 and Planning the Festival in Year 8. They will need to collect and analyse data for a variety of purposes.
View our full curriculum map
Take a look at our full curriculum map to see how units across all year groups, from Year 1 to Year 6 link.
Unit Resources
Lessons
Choose your system
For this unit of work, we have catered for the two most common systems being used in schools at the moment: Google's Workspace for Education and Microsoft's Office 365.
There are many similarities between the two systems, but also enough differences to make two sets of lessons useful for you. So please choose the lessons below that are most appropriate for your school.
Lesson 1
- Explain what a spreadsheet is;
- Describe how a spreadsheet could be used by someone at work;
- Label the different areas of a spreadsheet using the correct vocabulary;
- Create simple formulae in a spreadsheet.
Lesson 1
- Explain what a spreadsheet is;
- Describe how a spreadsheet could be used by someone at work;
- Label the different areas of a spreadsheet using the correct vocabulary;
- Create simple formulae in a spreadsheet.
Lesson 2
- Recall features of a spreadsheet
- Use cell references to complete formulae
- Use spreadsheets to complete formulae quickly and easily
Lesson 2
- Recall features of a spreadsheet
- Use cell references to complete formulae
- Use spreadsheets to complete formulae quickly and easily
Lesson 3
- Use a spreadsheet to sort data quickly
- Recall features of a spreadsheet
Lesson 3
- Use a spreadsheet to sort data quickly
- Recall features of a spreadsheet
Lesson 4
- Use a set of data in a spreadsheet to create an appropriate chart
- Use data in a spreadsheet to answer a set of questions
- Use information presented in a chart to answers questions
Lesson 4
- Use a set of data in a spreadsheet to create an appropriate chart
- Use data in a spreadsheet to answer a set of questions
- Use information presented in a chart to answers questions
Lesson 5
- Use tools in a spreadsheet to help someone understand the information more quickly
- Sort
- Conditional formatting
Lesson 5
- Use tools in a spreadsheet to help someone understand the information more quickly
- Sort
- Conditional formatting
Lesson 6
- Create a spreadsheet to test maths facts
- Use Conditional formatting to mark the questions
Lesson 6
- Create a spreadsheet to test maths facts
- Use Conditional formatting to mark the questions
Suggested Software
Spreadsheet software such as:
Key computing vocabulary for this unit
Ascending - When data is sorted it can arranged in a list. If the data is text it will be alphabetical A -> Z, if the data is numbers then it will be lowest to highest.
Cell - A spreadsheet is a grid and each ‘square’ is a cell. When you click in a cell and highlight it, it is known as the active cell.
Cell reference - Individual cells can be identified using the column (letter) and row (number) labels. For example A3, B26 or E251.
Chart - A graphical representation of data in a worksheet.
Column - A sheet is made up of columns, labelled with letters. These are vertical (run top to bottom) and are labeled at the top of the grid.
Conditional formatting - A spreadsheet tool that will change the appearance of a cell if the contents match a certain criteria.
Criteria - This is a set of rules or judgements that can be made to analyse data. E.g. All children in class 8 who play the recorder go to the hall. Criteria = Children (so not adults), Class 8 (not class 1-7), Play the recorder (no other instrument). Output = 6 children go to the hall (not the whole class of children and teachers)
Data - Data is raw, unorganised facts that need to be processed. Data can be something simple and seemingly random and useless until it is organised. When data is processed, organised, structured or presented in a given context so as to make it useful, it is called information. 2 is data.
Data set - A collection of data that can be analysed using a spreadsheet or other software.
Descending - When data is sorted it can arranged in a list. If the data is text it will be reverse alphabetical Z -> A. If the data is numbers then it will be highest to lowest.
Format - Arranging the contents of the cell to appear in a chosen way e.g. changing the font, size or colour.
Formula - A sum (or other mathematical statement) that can be entered into a cell, the ‘=’ is always entered first, cell references can be used instead of numbers e.g. = 4+6, = A5+B17, =67*E195
Information - Information is organised or presented data. Information gives context to data to make it useful. 2 apples, 2cm, 2 years old are all examples of information.
Label - Text (words or numbers) that are entered into a cell that will not be used in a formula. They are often seen as column or row headings.
Row - A sheet is made up of rows, labelled with numbers. These are horizontal (run left to right) and are labeled at the left of the grid.
Sort - Arranging data in a particular order. This can be alphabetical or numerical.
Tab - Each sheet in a workbook will have its own tab at the bottom of the screen. These can be renamed to help you remember what information is stored on each worksheet.
Workbook - When spreadsheet software is opened the whole document is called a workbook. A workbook can be made up of lots of different worksheets.
Worksheet - Each page of a spreadsheet is called a worksheet. A workbook can be made up of several sheets
Related units
Building Collaborative Websites
About this unit: Use Google apps for collaborative research as well as planning and creation of a group website, considering…
Communication and Collaboration
About this unit: Introduce students to email and online collaborative tools. Learn how to safely and appropriately make use of these…
Databases
About this unit: Explore different ways to collect, interrogate and present data collaboratively using a range of programs. What is…
Inside the internet
About this unit: Get under the skin of the Internet to investigate what the internet is, how the web works,…
Searching the Web
About this unit: Take a detailed look at all elements of searching the web with care and consideration, covering: searching…
What is a computer?
About this unit: Delve into what really makes a computer a computer. Is a TV a computer? Is a fridge…