Spreadsheet formulas
Lesson details
Learning outcome
I can calculate new data in a spreadsheet using formulas.
Key learning points
- Formulas in a spreadsheet are used to perform common mathematical operations using data from cells.
- Only cells that have a numerical data format can be used as part of a formula.
- Spreadsheet formulas can use cell references as a part of a calculation.
- Spreadsheet formulas can be duplicated to other cells and change their cell references accordingly.
Keywords
Calculation - working out an answer using numbers and mathematical operations
Operation - a mathematical action like adding, subtracting, multiplying, or dividing numbers
Formula - performs calculations in a spreadsheet
Replicate - to make a copy or repeat something in the same way
Common misconception
Spreadsheet forumlas need to use values that are typed directly into the formula.
A formula in a spreadsheet should use cell references where possible. Whilst a forumla can use a typed value, cell references saves having to retype values and also benefits from updating the results of the calculation if data changes.
Teacher tip
An empty box is a useful metaphor for cell references. The label on the front of the box could be the actual cell reference, while the data value is what is placed inside the box. If the data needs changing, what is inside the box should be changed.
Equipment
Pupils will need access to a device with spreadsheet software. Examples in this lesson use Google Sheets https://oak.link/google-sheets
File needed for this lesson
Using formulas download 68.12 KB (XLSX)
Download this file to use in the lesson.
Licence
Lesson video
Loading...
Prior knowledge starter quiz
6 Questions
Q1.Match the term with its definition.
facts and figures in their raw form
a line of data that runs from left to right
a group of data that runs from top to bottom
a rectangular box used to store data
Q2.What is a way to identify a cell?
Q3.Which of these is a cell reference?
Q4.How is data organised in a spreadsheet?
Q5.Why is it important to organise data?
Q6.Why is data formatting used in a spreadsheet?
Assessment exit quiz
6 Questions
Q1.Match the term with its definition.
working out an answer using numbers
how a calculation is performed
something that performs calculations in a spreadsheet
Q2.Order the steps for creating a formula.
Q3.Which operator is used for division in a spreadsheet formula?
Q4.Write the formula you would use to add a value in cell A1 to a value in cell B1.
Q5.What happens when you replicate a formula in a spreadsheet?
Q6.Order the steps to replicate a formula.
To help you plan your 6 computing lesson on: Spreadsheet formulas, download all teaching resources for free and adapt to suit your pupils' needs...
To help you plan your 6 computing lesson on: Spreadsheet formulas, download all teaching resources for free and adapt to suit your pupils' needs.
The starter quiz will activate and check your pupils' prior knowledge, with versions available both with and without answers in PDF format.
We use learning cycles to break down learning into key concepts or ideas linked to the learning outcome. Each learning cycle features explanations with checks for understanding and practice tasks with feedback. All of this is found in our slide decks, ready for you to download and edit. The practice tasks are also available as printable worksheets and some lessons have additional materials with extra material you might need for teaching the lesson.
The assessment exit quiz will test your pupils' understanding of the key learning points.
Our video is a tool for planning, showing how other teachers might teach the lesson, offering helpful tips, modelled explanations and inspiration for your own delivery in the classroom. Plus, you can set it as homework or revision for pupils and keep their learning on track by sharing an online pupil version of this lesson.
Explore more key stage 2 computing lessons from the Introduction to spreadsheets unit, dive into the full primary computing curriculum, or learn more about lesson planning.