Computing · Year 6

Active learning ideas

Basic Formulae and Cell References

Active learning works well for this topic because spreadsheets are hands-on tools that require trial and error. Students learn best when they see immediate feedback from changing numbers and formulas, which helps them connect abstract concepts to real outcomes. This approach also builds confidence as they solve practical problems like budgeting or scoring.

• National Curriculum Attainment Targets
• KS2: Computing - Data Handling
• KS2: Computing - Information Technology

• 20–35 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Case Study Analysis
• 30 min · Pairs

Pairs Challenge: Shopping Budget Calculator

Pairs list 5-6 grocery items with prices in column A and quantities in B. They enter =A2*B2 in C2, drag to fill down, then use =SUM(C2:C7) for grand total. Partners change a price or quantity and observe updates, noting predictions first.

Analyze how cell references allow for dynamic and flexible calculations.

Facilitation Tip

During the Pairs Challenge, circulate and ask each pair to demonstrate how changing a price updates the total cost, reinforcing the link between cell references and live results.

What to look for

Provide students with a simple spreadsheet containing two columns of numbers (e.g., quantities and prices). Ask them to write a formula in a third column to calculate the total cost for each item, then change one of the prices and ask them to predict the new total.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 02

• Case Study Analysis
• 35 min · Small Groups

Small Groups: Formula Prediction Relay

Divide class into groups of 4. Each group gets a starter sheet with values but blank formulae. Students predict results on paper, enter one formula per turn, relay to next member, and discuss why outputs match or differ from predictions.

Predict the outcome of a formula if a referenced cell's value changes.

Facilitation Tip

For the Formula Prediction Relay, provide printed spreadsheets with incorrect formulas and have groups correct them before moving to the next station to build peer accountability.

What to look for

On a slip of paper, ask students to write down a formula for calculating the average of three cells (e.g., A1, A2, A3). Then, ask them to explain in one sentence why using cell references makes this formula more useful than typing the numbers directly.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 03

• Case Study Analysis
• 25 min · Whole Class

Whole Class: Class Event Budget

Project a shared spreadsheet for a fictional school trip. Class suggests formulae for costs like transport (=B2*C2) and totals (=SUM(B5:B10)). Vote on changes, watch live updates, and record observations in notebooks.

Construct a spreadsheet formula to calculate the total cost of items in a shopping list.

Facilitation Tip

In the Whole Class activity, project the shared budget spreadsheet and invite students to suggest formula changes, modeling collaborative problem-solving for the group.

What to look for

Pose the question: 'Imagine you have a spreadsheet calculating your weekly pocket money earnings. If you wanted to add a new day's earnings, how would you adjust your total formula?' Guide the discussion towards the benefits of using SUM functions or range references over listing individual cells.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 04

• Case Study Analysis
• 20 min · Individual

Individual: Personal Score Tracker

Each student builds a sheet for weekly scores in subjects across row 1, enters averages like =AVERAGE(B2:F2) per week, and totals. They input new scores and verify dynamic changes.

Analyze how cell references allow for dynamic and flexible calculations.

What to look for

Provide students with a simple spreadsheet containing two columns of numbers (e.g., quantities and prices). Ask them to write a formula in a third column to calculate the total cost for each item, then change one of the prices and ask them to predict the new total.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

A few notes on teaching this unit

Start by modeling simple live demos where you change a cell value and ask students to observe the formula cell updating. Avoid teaching formulas in isolation, as students need to see their practical use. Research shows that students grasp cell references faster when they see the immediate effect of their changes. Use clear language like 'link' or 'connection' instead of 'reference' to reduce confusion.

Successful learning looks like students using cell references confidently, writing correct formulas, and explaining why their spreadsheets update automatically. They should describe the difference between static values and live calculations. Small errors should be viewed as learning steps, not failures.

---

Watch Out for These Misconceptions

• During the Pairs Challenge: Shopping Budget Calculator, watch for students who think entering =A1+B1 gives a fixed answer. Correction: Have pairs alter the value in A1 and ask them to describe what happens to the result in the formula cell. Ask them to compare screenshots of the spreadsheet before and after changes to build understanding of live links.

  During the Formula Prediction Relay, watch for students who copy formulas expecting values to stay the same. Correction: Provide a spreadsheet with formulas like =A1+B1 and =A2+B2 side by side. Ask groups to drag-fill the first formula down and observe how the cell references adjust. Then, ask them to copy-paste the same formula and compare the results, linking the difference to relative versus absolute references.

• During the Small Groups: Formula Prediction Relay, watch for students who believe =A1+10 will always give the same result. Correction: Give each group a live spreadsheet and ask them to change the value in A1. Pause the activity to ask, 'Why did the result in the formula cell change?' Guide them to see that the formula is a live calculation, not a static number.

  During the Whole Class: Class Event Budget, watch for students who think cell references copy values instead of links. Correction: Start with a blank sheet and ask students to write a formula in B1, like =A1*2. Then, have them drag the formula down to B2 and B3. Ask them to explain why the cell references change automatically, using the phrase 'relative reference' to describe the behavior. Show how $A$1 locks the cell and ask why this might be useful.

• During the Individual: Personal Score Tracker, watch for students who apply order of operations incorrectly when writing formulas. Correction: Provide a calculator and ask students to calculate 2+3*4 themselves. Then, have them enter the formula =2+3*4 in a cell and compare the result to their calculation. Ask them to rewrite the formula to get the correct answer and explain why the order matters.

  During the Pairs Challenge: Shopping Budget Calculator, watch for students who enter formulas like =A1+B1*C1 and expect the addition to happen first. Correction: Provide a simple example with three cells: price, quantity, and tax rate. Ask pairs to write a formula for the total cost, including tax. After they test it, ask them to predict the result before calculating. Discuss why multiplication happens before addition in the formula.

---

Methods used in this brief

• Case Study Analysis