/en-tutor-prep-s1-ict-spreadsheet-basics-formulas-charts/
/en-tutor-prep-s1-ict-spreadsheet-basics-formulas-charts/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

Junior Secondary ICT (S1) | Spreadsheet basics prep guide (for tutors) | Cell references, formulas and charts

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.

Objectives — Students can: (1) explain that a cell address is a column letter plus a row number, that B2:B5 covers four cells, and select a range with both mouse and keyboard; (2) use formulas to get the total 321 and the average 80.25 for the marks 78, 85, 92 and 66, and explain why =SUM(B2:B5) beats typing plus signs; (3) tell relative from absolute references, so B2*C2 filled down becomes B3*C3 while $C$1 still points at C1, and use it to get a new average of 85.25 after adding 5 marks; (4) pick a chart that fits the question: bar to compare subjects, line for a trend, pie for proportions, and compute the weighted score 78×30% + 85×50% + 92×20% = 84.3.

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%
Sanity check (teach this): Sanity check: an average must sit between the lowest and highest value, so 80.25 must fall between 66 and 92; an answer of 100 or 40 means the range is wrong. Weights must total 100%, and 30 plus 50 plus 20 does exactly that, so a total below 100 signals a missing cell. After filling down, inspect the last cell: D5 should read =B5*C5. If it still reads =B2*C2, the dollars locked the reference, all four answers will be identical, and that error is easily mistaken for correct work.

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)

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.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →