Junior Secondary ICT (S1) | Spreadsheet basics prep guide (for tutors) | Cell references, formulas and charts
- 2026-11-21
Students type 78+85+92+66 into a cell, then retype everything when one mark changes, and divide by the wrong count when asked for an average. This lesson reduces a spreadsheet to two ideas: where a cell sits, and what happens when a formula is filled down.
01 Prerequisites (3-minute check)
If any of these fails, fix it before the lesson starts.
| Skill | Quick check | Pass mark |
|---|---|---|
| Arithmetic and percentages | What is 30% of 78? | 23.4, that is 78 times 0.3 |
| Averages | What is (78+85+92+66) divided by 4? | 80.25, add the four marks then divide by four |
| Chart sense | Which chart best compares four subject marks? | A bar chart, because the heights can be compared directly |
02 Lesson flow (60 minutes)
| Time | Stage | Tutor does | Student does |
|---|---|---|---|
| 0-5 min | Opening question | Write 78, 85, 92, 66 on the board and ask how many times they would recalculate if one mark changed | Say how they usually do it, calculator or by hand |
| 5-20 min | Act 1: cells and references | Select B2:B5, name the column and row, type =B2+C2 and fill down | Select a four-cell range and read its address aloud |
| 20-35 min | Act 2: formulas and functions | Show =SUM(B2:B5) giving 321 and =AVERAGE(B2:B5) giving 80.25, then demonstrate $C$1 | Write a weighted formula and get 84.3 |
| 35-45 min | Act 3: choosing charts | Draw the same data as bar, line and pie, and say which question each one answers | Choose a chart for three questions and justify each |
| 45-53 min | Independent practice | Circulate, stepping in only when a $ is missing or doubled | Finish three questions and show the formula bar |
| 53-60 min | Exit check | Mark the three questions on the spot; two or more wrong means reteaching Act 2 | Self-assess and note which act to review |
03 Teaching script (read it out)
Scene 1: A cell has an address; a formula points at it
The key sentence for any spreadsheet: a cell holds a value, a formula holds a pointer. B2 is not “78”, it is “the cell at column B, row 2”. So =B2+C2 means “add the two cells on my left”, and changing B2 updates every formula that points at it.
Demonstrate a full fill-down: put 78, 85, 92, 66 in column B and the weights 30%, 50%, 20%, 0% in column C. Type =B2*C2 in D2 to get 23.4, drag the fill handle to D5, and the four cells show 23.4, 42.5, 18.4 and 0, totalling 84.3. Point out that the formula shifts itself to B3*C3, B4*C4 and B5*C5 while filling: that is a relative reference. Write it as =B2*$C$1 instead and only the row part moves, because the dollars lock column and row.
A formula stores a position, not a number; dollars lock the part that must not move.
Scene 2: A function is a ready-made formula
=SUM(B2:B5) adds the four cells and returns 321; =AVERAGE(B2:B5) returns 80.25; =MAX and =MIN name the top 92 and the bottom 66 straight away. Give three reasons to use functions: the range updates itself, no cell is skipped, and a reader can see what is being calculated.
Then show =IF(D2>=60,”pass”,”fail”) and name its three parts: the test, the answer when it holds, the answer when it does not. Finish with weighted marks: multiply row by row with =B2*C2, total with =SUM(D2:D5) to get 84.3, and compare it with the plain average of 80.25. The weighted score is higher because the subject scored 85 carries half the weight, and that single comparison is what students need to see for themselves.
A function is a range plus an action; a weighted score needs row-by-row multiplication first.
Scene 3: A chart is the answer, not decoration
Before choosing, ask what the reader should see at a glance. To compare four subjects, a bar chart works because bar height can be ranked; to follow five tests across a term, a line chart works because the slope is the change; to show what share of the total each subject takes, a pie chart works because one full circle is 100% and the slices must add to 100%.
Demonstrate three mismatches on the spot: a pie chart with ten items leaves slices too thin to read; a line chart across four subjects with no order draws a slope that means nothing; a bar chart with a non-zero axis start exaggerates the gap. Change the vertical axis to start at 60 and the same data looks three times more dramatic, which is exactly the lesson students remember.
Compare with bars, track with lines, show shares with pie; always start the axis at zero.
04 Question types and worked steps
| Type | Example | Steps | Answer |
|---|---|---|---|
| Fill a formula down | B2 is 78 and C2 is 30%; D2 must multiply them | Type =B2*C2 in D2 and drag the fill handle to D5 | 23.4, 42.5, 18.4 and 0 |
| Range functions | Total and average of 78, 85, 92, 66 | =SUM(B2:B5) and =AVERAGE(B2:B5) | 321 and 80.25 |
| Absolute reference | Add 5 marks to everyone, with the bonus stored in C1 | Write =B2+$C$1 then fill down | Average rises from 80.25 to 85.25 |
| Chart choice | Show each subject as a share of the total | Check the percentages add to 100%, then insert a pie chart | 30%, 50%, 20% and the remainder, totalling 100% |
05 Independent practice (3 questions)
| Question | Answer | What to watch |
|---|---|---|
| Change the marks to 80, 80, 80, 80. What are the total and the average? | Total 320, average 80 | Check a function was used, not a chain of plus signs |
| Which chart shows five tests from September to January? | A line chart, because the change over time matters | Check the student names the trend as the reason |
| Which is higher, the average 80.25 or the weighted 84.3, and why? | The weighted score, because the subject scored 85 carries half the weight | Check the reason is weight, not arithmetic error |
06 Common mistakes and what to say
| Mistake | Student writes | Say this |
|---|---|---|
| Typing values instead of referencing cells | Changing B2 leaves the total unchanged | You typed numbers, I typed positions. Replace =78+85+92+66 with =SUM(B2:B5) and this cell will follow when you change B2. |
| Forgetting the absolute reference when filling down | All four bonus answers come out the same, or all wrong | Row 2 needs the bonus in C1, row 3 would look at C2, but you want the same bonus in all four rows, so the formula must say $C$1. The dollar sign stops it sliding. |
| Missing a cell in the range | The range reads B2:B4 and drops the 66 | Look at how many cells the blue box covers. B2:B5 is four cells; missing one pushes the average up, so count the range every time. |
| Using a pie chart to compare ten items | The pie is cut into slices too thin to rank | A pie answers “what share”, a bar chart answers “which is bigger”. Tell me what you want the reader to see, then we pick the chart. |
07 Homework (paper, section and question numbers)
- Redo the four marks with cell references only, no typed numbers in formulas — Submit the formula bar, not just the answers
- Change the weights to 40%, 40%, 20%, recompute the weighted score and explain why it differs from 84.3 — Show the new working and the reason
- Open the spreadsheet paper from the S1 ICT chapter index and do the formulas and charts sections (question numbers as printed) — Write the function used beside each answer
08 Exit check (last 5 minutes)
| Question | Answer | Decision |
|---|---|---|
| What is the difference between =SUM(B2:B5) and =B2+B3+B4+B5? | Same result, but the function needs one edit when the range changes | If wrong, reteach the range idea in Act 2 |
| What is the average of 78, 85, 92 and 66? | 80.25 | If wrong, reteach AVERAGE in Act 2 |
| Which chart compares four subjects? | A bar chart with the axis starting at zero | If wrong, reteach chart choice in Act 3 |
09 Tutor FAQ
Is it acceptable for students to use a calculator and type the answer?
It passes tonight but fails the moment a number changes. Require at least one =SUM and one =AVERAGE, because the skill being tested is “change one value and everything follows”.
Should absolute references be taught this early?
Yes, and through a task they will break: the 5-mark bonus. Let the fill-down fail first, then fix it with $C$1, which sticks far better than a definition.
Is there a practice paper to go with this?
Yes. The homework section links the matching paper and the chapter index, split into formulas and charts, with question numbers as printed.
Find a tutor — freeTutor 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.



