Formulas in a spreadsheet model
Lesson details
Learning outcome
I can use formulas to perform calculations in a spreadsheet model and replicate formulas across a range of cells.
Key learning points
- Relative and absolute cell references are used in spreadsheet formulas to control how cell addresses change when copied.
- A relative reference changes automatically when the formula is copied to another cell.
- An absolute reference stays fixed on one cell, even when the formula is replicated.
- Using the correct type of reference helps ensure formulas calculate correctly when using tools like the fill handle.
Keywords
Replicate - to copy or repeat something
Relative - a cell reference that changes when a formula is copied or replicated to another cell
Absolute - a cell reference that remains constant no matter where it is copied or replicated
Common misconception
The fill handle can only be used to replicate formulas.
The fill handle can replicate formulas, text and numbers.
Teacher tip
This lesson contains downloadable spreadsheet files that pupils will need for the lesson. You could download these files prior to the lesson and make available to pupils on your schools shared drive or cloud storage area.
Equipment
Pupils will need access to a device with spreadsheet software. Examples in this lesson use Google Sheets https://oak.link/google-sheets
Files needed for this lesson
Tuck shop 5.29 KB (XLSX)
RSC seat sales 9.4 KB (XLSX)
RSC seat sales solution 10.1 KB (XLSX)
Download these files to use in the lesson.
Licence
Lesson video
Loading...
Prior knowledge starter quiz
6 Questions
Q1.What is a cell in a spreadsheet?
Q2.What is the purpose of spreadsheet formatting?
Q3.What does a formula do in a spreadsheet?
Q4.What is a function in a spreadsheet?
Q5.Type the calculation to add the values in cells A1 to A5. Make sure you use a function.
Q6.What formula would you use to multiply the values in cells B2 and B10?
Assessment exit quiz
6 Questions
Q1.What can the fill handle in a spreadsheet be used for?
Q2.What happens to a relative reference when a formula is copied to another cell?
Q3.When should you use an absolute reference in a formula?
Q4.Match the terms with their descriptions:
changes automatically when copied
stays fixed when copied
tool to replicate formulas
performs calculations in cells
Q5.How do you indicate an absolute reference in a spreadsheet formula?
Q6.What formula would you use to divide the value in cell A3 by the value in cell B10, ensuring that the reference to cell B10 remains constant?
To help you plan your 11 computing lesson on: Formulas in a spreadsheet model, download all teaching resources for free and adapt to suit your pupils' needs...
To help you plan your 11 computing lesson on: Formulas in a spreadsheet model, 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 4 computing lessons from the Spreadsheets unit, dive into the full secondary computing curriculum, or learn more about lesson planning.