Spreadsheet Expert

Overview

In lesson 3 of 6, pupils build on basic spreadsheet skills from previous lessons to design a charity-event budget. They will learn how cells and references work, use at least one formula, and add a chart to represent their data clearly.

Learning intentions

• WALT organise data effectively with spreadsheets.
• WALT use cells and cell references to make calculations.
• WALT create and edit formulas to automate budgeting.
• WALT use charts to communicate information from a spreadsheet.

Success criteria

• I can enter budget items and costs into the correct spreadsheet cells.
• I can use at least one formula (e.g., addition for totals) using cell references.
• I can check my formulas for accuracy.
• I can create a chart that matches my budget data and is readable.

Curriculum links

• Applying computing to program, monitor and control products (using spreadsheet formulas as “programmed” calculations).
• Technical knowledge: strengthen, stiffen and reinforce more complex structures (applied here as “structuring” data into a reliable model with clear layout and checks).
• Subject content: engage in an iterative process of designing and making (input–test–improve their budget spreadsheet).

Lesson structure (60 minutes)

1. 0–5 min · Starter: Spreadsheet live demo

• Teacher displays a partly completed budget spreadsheet and highlights key cells and totals.
• Students discuss in pairs: “What changes if one cost value changes?” and write one prediction.

2. 5–15 min · Direct teach: cells, references, formulas

• Teacher explains how formulas use cell references (e.g., total = cost1 + cost2) and demonstrates entering and copying a formula across rows safely.
• Students follow along on their blank “Budget” sheet template: label columns and enter sample data for one charity category.

3. 15–28 min · Guided practice: build the budget model

• Teacher checks step-by-step: correct headings, tidy number formatting, and consistent cell references.
• Students add at least 4 budget categories (e.g., food, printing, venue, decorations) and compute:
• a row total per category (if needed)
• a grand total for the event using a formula that references their cells.

4. 28–38 min · Formula checking: test and fix

• Teacher models a quick “sanity check” method: change one input cost and observe if totals update; fix any errors (e.g., wrong reference, missing cell).
• Students run one test: change one cost deliberately (by a small amount) and confirm the grand total updates correctly, then undo the change.

5. 38–50 min · Teach: charts from spreadsheet data

• Teacher shows how to select the right range (labels + values) and choose a suitable chart type (e.g., bar chart for categories).
• Students create a chart for their charity budget and ensure it has a title and readable axes/labels.

6. 50–58 min · Review against success criteria

• Teacher circulates with a checklist, asking targeted questions: “Which cells does your formula use?” and “What does your chart show?”
• Students improve one thing: either correct a formula, adjust formatting, or improve chart clarity (size/labels).

7. 58–60 min · Exit ticket

• Teacher collects a quick response: students write (a) the formula they used and (b) one way they checked it worked.
• Students submit responses at the end.

Resources

• Excel or spreadsheet software (class devices) with the provided “Budget” template
• Projector/teacher laptop with a demo spreadsheet
• Printed “Formula checklist” for students (inputs, formula, output)
• Worked example sheet (for common formulas and cell reference cues)
• Dyslexia-friendly reading strip version of instructions (short lines, large spacing)
• Colour-coded number formatting guidance (inputs vs totals)
• Optional: sticky notes for “mistake fixes” during the test phase

Assessment

• Formative checks during guided practice: teacher observes formula entry and reference choices.
• Target questions during circulation: “Which cells are you summing?” “How do you know it updated?”
• Exit ticket: identify the formula used and one verification method.

Differentiation

• Support (all learners needing scaffolds): provide a worked example of a completed “Grand Total” formula and a sentence starter: “My formula adds cells … because …”
• Support for dyslexia: dyslexia-friendly instruction cards, reduced text on task sheets, and highlight only the next step; allow audio explanation and reading pairs (one reads, one inputs).
• Targeted support: teacher groups for 2–3 pupils to pre-check headings and reference ranges before chart creation.
• Extension (advanced learners): add a “What if?” column (e.g., increased costs by 10%) and create a second total using a more complex formula (e.g., multiplying inputs by a percentage), then make an additional chart comparing original vs adjusted totals.
• Challenge for confident learners: create two charts (one for category costs, one for adjusted costs) and choose chart types that best communicate the budget; justify choices verbally to a partner.

Success criteria for each lesson

• Lesson 3 focus: Build a functional charity budget spreadsheet with at least one formula and a chart (accuracy checked).
• Overall unit direction (Lesson 3 contribution): using spreadsheet structure and verification to support an iterative improvement process in future lessons.