Year 8 Science · Unit 4 · Lesson 9

Using Spreadsheets for Data

Challenge Worksheet

Name

Date

Class

Learning Goals

Identify and explain errors in a spreadsheet layout, formula syntax and chart labelling.
Design a complete spreadsheet template for a multi-variable, multi-week class investigation.
Evaluate the scientific advantages of collaborative data sharing in Google Sheets.

Warm Up

Find the mistake

A student's spreadsheet contains four errors. For each error, identify what is wrong, explain the consequence, and describe how to fix it.

Error description	What is wrong?	Consequence if not fixed	How to fix it
The formula reads `=AVERAGE(B2-B6)` (uses a minus sign instead of a colon)
The student calculated averages before all three trials were entered — two cells in the range are still empty
The chart x-axis is labelled "Concentration" with no units
Trial results and calculated averages are mixed together in the same column

Go Deeper

Design challenge

You are designing a spreadsheet template for a Year 8 class of 30 students investigating the effect of light intensity on plant growth over 4 weeks. Each student measures the height of their plant (cm) once per week. The class wants to pool all data and analyse it together. You need to design the full spreadsheet layout before anyone starts entering data.

(a) Design the column header row for this spreadsheet. List every column header you would include, the units for each measured variable, and explain your reasoning for the layout (one row per student per week OR one row per student with weeks as separate columns — choose and justify).

Challenge 4 marks

(b) Write the formula cells you would include for the class analysis — at minimum: class average per week, maximum height recorded, minimum height recorded, and range (max − min). Write each formula using realistic cell references and explain what it calculates.

Challenge 4 marks

(c) What chart type would you use to display how average plant height changes over 4 weeks? Justify your choice and describe two settings you would adjust (beyond the default) to make the graph scientifically useful.

Challenge 3 marks

(d) Describe one specific scientific advantage of sharing this template as a collaborative Google Sheet rather than emailing separate spreadsheet files to each student. Consider what happens when data is entered and updated in real time.

Challenge 2 marks

Wrap Up

In one sentence, what was the main idea of this lesson?