Before the lesson

Later in the lesson, your students will need a copy of the spreadsheet called Student 'Sports Day' to copy.

So before the lesson, open the Google version of our file from the link above. Click File > Download > Microsoft Excel and save the file to your computer. You will then need to share the file with each student so they have their own personal copy. As described in lesson 3, you can do this in two ways, email the Excel file to each of them as an attachment or share the file with them via Teams or OneDrive. If you choose the latter, students will again need to open the file you share with them and make their own copy by clicking File > Save as > Save a copy online and rename their own copy with their name in the title.

 

 

This process is described in more detail in lesson 3, if you need to go back and recap it.

 

Introduction

This lesson will recap vocabulary, revisit sorting and introduce the idea of creating charts from data in a table.  The charts can then be used to check for errors or unexpected results or to answer questions.

It would be useful to show the Teacher’s presentation and your Excel version of the Demonstration Spreadsheet at the same time on the board.  Use the Chrome option in ‘Window’ to tile both windows (full instructions can be found in lesson 3).

Open ‘Demonstration spreadsheet’ ‘Lund’ tab.  Show the class the table and explain that it shows weather statistics for the city of Lund in Sweden.

 

 

Recap how to sort the data then try sorting using the following criteria

• sort by Average Highest temperature lowest to highest
• sort again Average rainfall highest to lowest
• ask the children for another sort criteria
• Can the class suggest two other sort criteria?

 

Adding charts to spreadsheets

Show the chart on slide 51.  Explain that this chart was created in Excel using the information in the table about Lund.

Demonstrate how to make a chart that shows the average highest temperature (column C).

 

1. Working in your Excel version of the ‘Demonstration spreadsheet’ ‘Lund’ tab.

2. Highlight cells B1 to B13 (the months column). his will be the first data category in our graph.

 

 

3. Then hold down Ctrl and highlight cells C1 to C13 (the average highest temperature column). This will be the second data category in our graph.

 

 

4. Then go to the Insert menu and click on the bar chart icon.

 

 

5. The chart will appear in your spreadsheet.

 

 

You can drag it around to position it and also resize it from the corner handles. This will also stretch out the axis to make it look better.

 

 

If you double click the chart, formatting options will appear at the side of the screen. Here you can do lots of things such as edit or turn on or off the title and axis labels, format the text, adjust the scale of each axis, add and format the grid lines on the chart.

 

 

Have an explore through the options to style your chart however you wish, but you can certainly improve the look of your chart with a bit of formatting, for example...

 

From this

 

To this

 

Main tasks

Ask the children to open the Activity sheet.

Task 1

Task 1 asks them to open the spreadsheet they created in week 1 and create a chart using their 'tricky times tables' data.

Allow the children some time to create their own chart.  Each chart produced should have columns that get larger in regular steps but as the pupils all picked their own number the charts won’t all be the same.

After they have built their chart they are asked:

• In the spreadsheet that contains your tricky times table data try changing one number at a time in your first or second columns.
• What do you notice?  Look carefully at the table and then the chart.
• Remember to change the cells back to the original numbers.
• Change one cell and then drag your chart away from the table.  Can the person next to you identify where you made the change?

 

Show the class the charts on slide 52 and 53.  These are charts from the ‘tricky times tables’ where the number being multiplied was 6.765.

Ask the children: Do you notice anything about the charts? Encourage them to explain their answers in terms of data in columns or cell references.

e.g.

• The data in the spreadsheet has been changed in cell H2,
• This was changed from 2 to 10,
• The calculation being carried out has changed from 2 x 6.765 to 10 X 6.765.
• The data in the spreadsheet has been changed in cell I11,
  • 6.765 was changed to a much bigger number so that the product was bigger,
  • The calculation being carried out has changed to 11 x 24.

 

Ask the children to change some of the data in columns H and I.  Is the answer immediately obvious in column J?  Or does the chart give us a better view of anomalies (unusual results)?

 

Task 2

Now show the class the tab labelled ‘Sports Day’ and explain that all the teachers took part in a secret teachers’ sports day and these are the results from the 1500m race.  Explain that the spreadsheet is full of information that can be used to answer the questions. 

 

 

The children will need to use all of the skills so far that they have learned in the previous weeks. 

Use the tabs in ‘Demonstration spreadsheet activities’ that have been used in the previous weeks to model how the children could find answers, practice writing formulae or create charts if more practice or explanation is needed.

They should then open their own copy of the students 'Sports Day' to copy spreadsheet as described at the top of this page. Explain at this point how they should access this spreadsheet, depending on the method you have chosen for sharing it with them.

 

Plenary

The answers to ‘Sports Day’ are shown in the next tab ‘Sports Day ANSWERS’.  Take feedback from the class as you work through the solutions with the class.

To finish, recap the use of spreadsheets for wide ranges of jobs. Assess the children's progress against the lesson objectives with the following questions:

• Why might we add a chart to a spreadsheet?
• What advantages can using a chart over raw data have when analysing a set of data?