Before the lesson

One of the activities in this lesson requires students to have access to an Excel version of our spreadsheet called 'Students LESSON 3 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. 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 - This can be done during the lesson as requires some student input.

The process is described in detail below, but before the lesson, upload the 'Students LESSON 3 to copy' Excel File to your OneDrive.

 

 

Introduction

The purpose of this lesson is to look at spreadsheets as ways to sort and organise information into useful and more meaningful data sets.

For these exercises you will need to be able to move between the presentation and the example spreadsheet.  If you are using Chrome then you can arrange the screen to show both windows at the same time.

Open the Teacher’s presentation and your version of the Demonstration Spreadsheet in different Chrome windows, not as tabs in the same window (you can drag a tab into a new window by simply clicking the tab and dragging it down and away from the other tabs).

Find the Chrome taskbar at the top of your screen.

Click on ‘Window’.

 

Next choose ‘Tile Window to the Left of Screen’ for one the presentation.

 

 

Then repeat for the spreadsheet, this time selecting ‘Tile Window to the Right of Screen’.

You should now be able to work through the presentation and demonstrate using the spreadsheet.

Begin with a quick vocabulary check.  Ask: Can you define these terms

• Cell reference
• /
• Worksheet

Are there any other words relating to spreadsheets that you can recall?

 

Sorting data

Slide 34 asks the class a number of questions which they should be able to offer suggestions to by the end of the lesson.  Explain that all of the examples you show today and all of the practice they complete will help them answer these questions.

In ‘Demonstration spreadsheet activities’ open the tab ‘Sample Data’ sheet.

 

 

This world cup data can be viewed on one sheet and we could scan the information to find the biggest / smallest / earliest but in huge data sets that cover thousands of rows or columns that’s not possible.

Examples of when sorting would be carried out in school would include:

E.g. Sorting children

• into classes
• into school meals and packed lunches
• Children into family groups for school photos etc.

 

Discuss the question in the presentation.

• What does this spreadsheet show us?
• Who might use this information?
• Is it well organised?
• How else could we organise this data?
• Is it up to date? 
• Do we know any other data that could be added?

 

Model how to sort the data in column I showing goals scored, from the smallest to the largest number.  Following the instructions in the spreadsheet and recap with the screen recording demonstration to reinforce.

 

 

Ask the children to lead you through a new sort.  Sort column F, showing average attendance from largest to smallest.  Can the children suggest any other ways of sorting the data? - Smallest to largest

 

Main tasks

The students need to open their own copy of the spreadsheet 'Students LESSON 3 to copy'.

If you emailed this to them direct them to their inbox so that they can find and open the attached file.

If you plan to share with them via Teams or OneDrive, find the file and then click the Share button.

 

 

Add their email addresses or the class group email (if you have one set up).

This will give them all access to YOUR copy of the file. So then instruct the students to locate the file you shared with them via the three lines in the top left of OneDrive, then going to Shared and then open up the file.

 

 

Once opened, they should immediately click File > Save as > Save a copy online and rename their own copy with their name in the title.

 

 

They'll be able to tell they are in the correct file as it will show their name in the top left.

 

 

 

Task 1 and 2

Give the class some time to complete Task 1 and 2 in the Activity sheet and using the 'Series 20' tab on their LESSON 3 spreadsheet. 

They must first make their own copy of the spreadsheet and then practise sorting using the data about the LEGO minifigures:

 

Let’s practice sorting!

• Can you sort the table of Lego minifigures into alphabetical order from A ->Z?
  • Which character is third from the top of the list?
• Can you sort the table of Lego minifigures using ‘number collected’ from lowest to highest?
  • Which character is third from the bottom of the list?

 

Now try this.

• Can you enter the total number of minifigures collected in cell D1
• Can you add a new character called ‘Baby Koala’, there were 15 of these figures collected.
• Can you re sort the table using the same criteria we used earlier.
• Are the answers to the questions different?
• How can you change the formula in cell D1 to show a new total

 

Talk through some of the answers the children found and model their sorting on the Demonstration Spreadsheet.

Then ask all the children to switch to the 'Animation' tab on their spreadsheet. 

 

Task 3

Explain that the sheet shows data for 50 animated films.  It shows the amount of money each film made.  The sheet can be sorted in a different way to show when the films were released.

For the challenge use the ‘Animation’ tab from Demonstration Spreadsheet and the questions on slide 45.

Ask the children to sort the ‘Animation’ spreadsheet to answer each question in turn.  Work alongside them modelling each search.

 

Answers (also displayed on the presentation).

1. Which film had the lowest peak?

How to Train Your Dragon: The Hidden World

2. Which film was released first and what was the year?

The Lion King (1994 original), 1994

3. How many films peaked at 10 and what are they called?

1, Inside Out

4. How do we sort the table to get back to the original view?

Select the whole sheet, choose ‘Sort and filter’ > ‘Custom sort’ from the menu. Then tick ‘My data has headers’ and sort by ‘Rank’ > ‘Ascending’.

 

Plenary

Revisit the questions from the questions from the beginning of the lesson, can the children suggest answers to them now?

• How would you organise a column of numbers from smallest to largest?
• Why would you need to do this?
• When would a hospital administrator do this?