Introduction

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

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 version of ‘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 range’ function.  Using the Sort Range 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.  Explain that you want the class to use the ‘Scores’ tab to practice a new skill.  You are going to teach them the skill on a table they have already seen ‘Lund’. 

Using your version 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 ‘mark’ an answer.

There are step by step instructions in the Teacher’s presentation for the statement 

• Mark all temperatures that are above 20 C

There is also the following screen recorded example for for the statement

• If any cell contains a temperature lower than zero, highlight the whole box blue.

 

 

Here is another example.

1.  Select ‘Format’ from the taskbar and then ‘Conditional formatting’ from the menu.

 

 

2. The following window will open on the left of the screen.

 

 

3.  Select ‘Apply to range’ and highlight the area to be set.

E.g. column D

 

4. Click in the ‘Format cells if …’ box - the following list of rules will appear.

 

5.  Select ‘Less than or equal to’.

6. Type ‘0’ in the box labelled ‘value or formula’.

7. Choose the ‘fill colour’ tool and change it to blue.

8. Click 'Done'.

 

Main tasks

Ask the children to open the Activity sheet and to complete Task 1, open the demonstration spreadsheet ‘Students 'Scores' to copy’.  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 spreadsheet ‘Student ‘Scores’ to copy’, 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 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? 

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.