DSE ICT | Spreadsheets and data models: lesson-prep guide for tutors

2026-11-02

Tutor lesson-prep guides · Secondary ICT

Students can type =SUM but the column breaks the moment they fill down. This lesson replaces cell-by-cell typing with one data model and one formula chain.

Objectives — Students can: (1) build a sheet in three layers — header row, raw data, calculated column — and name the data type of each column; (2) write a formula that survives fill-down and say when a $ is needed; (3) turn #DIV/0! and #VALUE! into readable text with IFERROR.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Starting a formula	Which character must a formula begin with?	The = sign, then a function or a cell
References	What is the difference between B2 and $B$2?	B2 shifts when copied; $B$2 is locked
Basic function	Which function adds 12 numbers?	SUM, written as =SUM(B2:B13)

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0–5 min	Opening question	Show an old sheet full of #DIV/0! and ask which cell broke first	Vote by show of hands, one reason each
5–15 min	Prerequisite check	Hand out three quick checks (the = sign, relative vs absolute, SUM range)	Written answers, marked on the spot
15–35 min	Three-part script	Teach the three layers, absolute references and IF; ask one student to recap each part	One-sentence recap after each part
35–45 min	Worked questions	Model price × quantity, then fill-down, then total, three times	Copy and label each column
45–55 min	Independent practice	Circulate; ask only whether the formula will drift	Do three questions, self-mark
55–60 min	Exit check	Collect the three answers and count the errors aloud	Written, no talking

03 Teaching script (read it out)

Scene 1: Part 1: build in three layers

Before typing a single number, spend three minutes on structure: a header row (item, unit price, quantity, amount), then raw data, then one calculated column. The calculated column is always produced by a formula, never typed, because typed totals break silently when a price changes.

Which column must be produced by a formula, never typed?

Scene 2: Part 2: where the $ goes

When you fill down, a relative B2 walks down the column, which is what you want for prices. A fixed tax rate must be locked as $B$1. The test is one question: should this cell change when the formula moves down? This sheet has 12 rows, so one wrong reference breaks 12 cells at once.

Which coordinate needs a $ before you fill down, and why?

Scene 3: Part 3: turn errors into sentences

#DIV/0! is not a crash, it is a zero denominator. Instead of hunting cell by cell, wrap it: =IFERROR(formula,”no data yet”). Break a formula on purpose once and repair it in front of the class; students remember the message, not the definition.

When #DIV/0! appears, which cell do you inspect first?

04 Question types and worked steps

Type	Example	Steps	Answer
Single formula	The question gives column names and a range and asks for one formula.	(1) Find the output column; (2) find the inputs; (3) choose the function; (4) check the first and last row of the range.	Example: =SUM(D2:D13) gives 572.
Fill-down	The question asks for a formula across a whole column with one fixed value.	(1) Write the first cell; (2) decide which coordinates to lock; (3) fill down; (4) spot-check the last row.	Example: =C2*$B$1 filled down keeps B1 fixed.
Conditional logic	Grade values or guard against errors.	(1) Write the condition; (2) the true value; (3) the false value; (4) wrap division in IFERROR.	Example: =IF(E2>=100,”on target”,”below target”).
Chart choice	Pick bar, line or pie from the data type.	(1) Comparison uses bars; (2) trend uses a line; (3) share uses a pie; (4) title and units are mandatory.	Example: a 12-month revenue trend uses a line, not a pie.

Sanity check (teach this): Sanity check: the raw block is 12 rows by 3 columns, 36 cells, plus 12 formula cells. Verify the total with SUM: 572, and the mean over 12 rows: 47.67. If a student disagrees with either number, check the first and last row of the range before anything else.

05 Independent practice (3 questions)

Question	Answer	What to watch
Given 12 rows of unit price and quantity, write the amount formula and fill the column.	=B2*C2 filled down to row 13, with =SUM(D2:D13).	Check for a missing $ or a range that stops one row early.
Write the mean amount per row and handle a zero quantity.	=IFERROR(AVERAGE(D2:D13),”no data yet”).	Check that IFERROR wraps the division and the range matches the data.
Count rows with an amount above 50.	=COUNTIF(D2:D13,”>50″).	Check that the criterion is quoted and that COUNTIF, not COUNT, is used.

06 Common mistakes and what to say

Mistake	Student writes	Say this
Typing numbers instead of formulas	The amount column is filled with 36, 40, 50 by hand.	Change one price and the column stops matching reality. Delete it and start from the formula.
Forgetting the absolute reference	=C2*B1 filled down multiplies the blank cells below B1.	By row three it reads B3, an empty cell. Lock it as $B$1 and fill again.
Range missing the last row	=SUM(D2:D12) leaves the final row out.	12 data rows means the range ends on row 13. Count the last row out loud.
Overwriting an error with a zero	Seeing #DIV/0!, the student deletes the formula and types 0.	Zero is a fake number. Find the zero denominator, then wrap the formula in IFERROR.

07 Homework (paper, section and question numbers)

Secondary ICT index: finish the three exercises, then practise unit conversion in the data representation chapter — Exercises 1–3 on this page

08 Exit check (last 5 minutes)

Question	Answer	Decision
What are the three layers of a sheet?	Header row, raw data, calculated column	Cannot answer: reteach part 1
What question do you ask before filling down 12 rows?	Should this cell change as the formula moves down?	Cannot answer: reteach part 2
What causes #DIV/0! and how do you handle it?	A zero denominator; wrap it in IFERROR	Cannot answer: reteach part 3

09 Tutor FAQ

Which year is this for?

S4 core ICT; also useful for S5 revision and the ICT electives.

Do we need real spreadsheet software?

Yes — Excel or Google Sheets. The point is observed fill-down and $ behaviour.

Is there a matching paper?

The homework link goes to the secondary ICT index, which pairs with the site practice papers.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.