Before the lesson

Later in the lesson, your students will need a copy of the spreadsheet called Scores - Student version.

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

It would be useful to show the Teacher’s presentation and your Excel copy 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).

This lesson will focus on how to set the spreadsheet to automatically show data that falls into a particular criteria.  E.g. A cell automatically highlights if the number is larger than 100 or a cell automatically changes colour if it contains a particular name.  This is called conditional formatting.

 

Conditional formatting

Conditional formatting is a spreadsheet tool that will change the appearance of a cell if the contents match a certain criteria.

E.g. In your class list spreadsheet, you might choose to highlight the names of all of the children who have birthdays in May.  Or, you may choose to highlight the names of the children who have reading scores greater than 30.

Open your Excel version of the ‘Demonstration spreadsheet’ ‘Scores’ tab. 

 

 

Explain that the numbers in column A are the class numbers for classes in a school and column B are wet playtime quiz scores for a day.  It was very wet so each class in school had 3 quizzes today!  The table is initially organised into break times.

As in previous lessons recap and experiment with the ‘Sort ’ function. 

 

 

Using the Sort menu option, sort the data to show all three quiz results for each class in a group.

 

 

Ask the class:

• How can we re-sort the data to show the scores from highest to lowest?
• How would you re-sort the data to show the score from lowest to highest?

 

Introduce the new skill of conditional formatting. Conditional formatting applies formatting to a cell based upon its contents. For example, if the cell contains a particular word (or not), or if the number in the cell is above or below a value, there are many possibilities.

The students should have used conditions in their coding lessons, which can also be referred to as selection. In Scratch an 'if then' block is an example of a simple condition in action:

 

 

Kodu's method of coding by adding actions to When and Do also uses conditions, everything on the 'When' side is the condition for what happens to the characters in the game.

 

 

Whatever coding language you are using, you will find conditions as they are a vital tool for making programs work and respond to things.

First, let's take a look at an example of using conditional formatting using the Lund weather data table we used previously.

Using your Excel copy of the ‘Demonstration spreadsheet' tab ‘Lund’, explain that you want to add a scale to show when Lund is warmest.  Demonstrate to the children how to use conditional formatting to highlight cells.

In this example we want to highlight all temperatures that are above 20 degrees.

 

 

First, select ‘Home’ from the task bar and then click the ‘Conditional formatting’ icon from the menu ribbon. Select New Rule.

 

 

If the icon is not visible, try clicking on the three dots a the end of the menu for more options.

 

 

Click in the Apply to range box and select the cells containing numerical data on the spreadsheet

 

 

Then in the Rule Type section choose to Highlight cells with a Cell value Greater than 20.

 

 

Then click the Done button to save this rule.

 

 

 

Now let's add a second rule to colour any cells equal to, or below zero green. In the conditional formatting panel, click on Go to manage Rules, then click the + sign to add a new rule.

 

 

Again, highlight the cells you want to use in the Apply to range box, then set it to highlight cells with a cell value less than or equal to 0. Then choose Format with > Customised format > Blue and click Done. 

 

 

Here's a video example of adding conditional formatting to your table.

 

 

Your table should look like this.

 

 

 

Main tasks

Ask the children to open the Activity sheet and the demonstration spreadsheet you have shared with them called 'Scores - Student version’.  Allow the pupils time to complete Task 2 and review the result.  Everyone should have only one cell that is highlighted grey (Class 11 = 3 points), if they have sorted their table it will be cell B2.

Allow the students more time to use the conditional formatting in the ‘Scores’ spreadsheet.

Using the same spreadsheet, ask the children to experiment with other spreadsheet functions that they have used in previous weeks.

Can they:

• sort the scores from Highest to lowest?
• find the whole school total?
• find the difference between class 8 and the whole school?
• find the (mean) average score?
• create a chart?
• demonstrate another function they were able to carry out?

 

Plenary

Review the student's understanding of the tools they have used in this lesson. Ask the class:

• What is conditional formatting?
• Give me three examples of different conditions that could be used to highlight a cell?
• What conditions did you use in your spreadsheets today?
• Where else have you come across the concept of conditions?