Hello and welcome to Computing.

My name is Mrs. Holborow.

I'm so pleased you've decided to join me for the lesson today.

We're going to be using spreadsheets in today's lesson and we're going to be using conditional formatting to apply a format to a cell based on criteria.

Welcome to today's lesson from the unit Data Modelling, this lesson is called "Applying Conditional Formatting to Data." And by the end of today's lesson, you'll be able to use conditional formatting and a range of tools to present data effectively.

You'll need access to some spreadsheet software for today's lesson, shall we make a start? We will be exploring this keyword during today's lesson, conditional formatting.

Conditional formatting applies a style or format to a cell based on whether a cell matches a certain criteria.

There are two main parts to today's lesson.

We'll start by applying conditional formatting to a range of cells and then we'll move on to combine tools to analyse and present data.

Let's make a start by applying conditional formatting to a range of cells.

Applying formatting to a cell can draw attention to particular pieces of data.

This is useful if you're trying to highlight key information to the reader, but Laura's got a really good point, applying formatting, like adding background colour to cells, can take a lot of time.

Conditional formatting applies a style or format to a cell based on whether a cell matches a certain criteria.

What criteria could we use to format cells in the Olympic medal spreadsheet? Maybe pause your video here and have a think.

Conditional formatting could be used to highlight the largest number of medals by applying a background colour.

For example, the United States of America had a total of 121 medals, so this has been highlighted with a background colour.

Or conditional formatting could be used to apply a different font colour and make all of the text bold in cells where the number of medals is greater than 20.

You can see here all of the medal totals that are greater than 20, the font colour has been changed to purple and bold.

Time to check your understanding.

Fill in the gaps to complete this sentence.

.

applies a style to change the look of a cell based on whether a cell matches a certain.

Pause the video here whilst you have a think.

How did you get on? Conditional formatting applies a style to change the look of a cell based on whether a cell matches a certain criteria.

Conditional formatting can use a range of conditions to check a cell and then apply formatting.

These include if a cell is empty or not empty.

Contains a specific term.

Contains an exact amount.

If the value is greater than a given value.

Or if the value is less than a given value.

Or if the value is equal to a given value.

So let's take some examples here.

We could apply conditional formatting to every cell that contains the exact amount of 10.

We could apply conditional formatting to all of the cells where the value is greater than 100.

Or we could apply conditional formatting to all of the cells which contain a specific term, like the word yes.

Time to check your understanding.

What criteria has been used to apply conditional formatting to the data in this spreadsheet? Pause your video here and have a think.

That's right, we've applied a background colour with conditional formatting, if the cell value is less than 15.

Here's an example of how conditional formatting is applied to a range of cells.

We highlight the cells and then go to Format, Conditional formatting.

Here, I'm looking for the condition if the cell is greater than, and then I'm typing in the value 40.

The default is green, so I'm going to accept this and press Done.

I'm going to highlight the same range of cells again, but I'm gonna add another rule this time.

This time I'm going to say if the condition is less than, so if the cell holds less than the value 20, this time I'm gonna change the fill colour to red and the text to bold and then I'm going to press Done.

So you can see that the same data can have more than one conditional rule applied to it.

If data within a cell is updated, then the conditional formatting will also update.

The formatting will be applied if it now meets the condition or removed if it no longer meets that condition.

So we don't have to manually go in and change the formatting, it's done for us.

So you can see here, change the value 17 to 21 and it removes the red background colour.

There, I changed the value from 26 to 45 and it added the green background colour.

You're doing a fantastic job so far so well done.

We're now going to move on to our first set of tasks for today's lesson.

For Task A part 1, download the weather data spreadsheet.

For part 2 use conditional formatting to highlight in different colours the following things: For a, rainfall levels of 0, you can apply a background colour and that can be any background colour of your choice.

For b, apply bold text to altitudes greater than 100 metres.

For c, change the text colour to blue for any temperatures of 15 degrees Celsius or less.

For d, apply a red text colour to temperatures of 20 degrees Celsius or more.

And for part e, apply a text colour of orange to temperatures between 15 degrees Celsius and 20 degrees Celsius.

Pause the video here whilst you complete the activity.

How did you get on? Did you manage to apply conditional formatting to the cells? For part a, you were asked to apply a background colour to rainfall levels of 0.

So you can see in my example, I've used a grey background colour to all the cells that are 0.

For b, you were asked to apply bold text to altitudes greater than 100 metres.

So you can see here any altitudes greater than 100 are bold.

For c, you were asked to apply a blue text colour to temperatures of 15 degrees Celsius or less.

For d, you were asked to apply a red text colour to temperatures of 20 degrees Celsius or more.

So you can see there's some red ones in the list there.

And then for e, you were asked to apply the text colour orange to temperatures between 15 degrees Celsius and 20 degrees Celsius.

So you can see here that the majority of the temperatures sit between 15 degrees Celsius and 20 with a few exceptions.

We only have one that is blue because it is under 15 degrees Celsius.

Applying conditional formatting to this data has allowed us to easily identify these key pieces of information.

Okay, we're now moving on to the second part of today's lesson where we're going to combine tools to analyse and present data.

What tools can be used in a spreadsheet? Ah, Sam says, "You can use formulas "and functions to calculate.

"You can also use sort and filter to organise information." Some great ideas from Sam.

Laura says, "You can use formatting "and conditional formatting to pick out key information," some great ideas again and Jun says, "You can display information in charts or graphs." Some great ideas of tools that you can use in a spreadsheet there.

Sam says, "A formula or function can be added "to a cell by putting the equals symbol "and then either the formula or function." So we've got some examples here.

In cell D1, we've got the formula =A1*C1.

Remember, the asterisk is used for multiplication in spreadsheets.

In cell D2, we're using the sum function to add up the value of the range of cells A2 to C2.

In cell D3, we're using the COUNTA function to return the number of non-blank cells in the range A3 to C3.

And in cell D4, we're using the COUNTIF function to count how many times the number 2 appears in the range A4 to C4.

Sam is reminding us that a sort can be applied to data to organise it.

Let's watch this animation to remind ourselves how we can apply a sort to data (no audio) Sam says, "A filter can be applied "to temporarily remove or hide data from a data set." Again, let's watch the animation to remind ourselves how this can be done.

We highlight the data, go to Data, Filter, and then using the filter icon at the top of the column, we select the data that we're filtering for, in this case, A+.

Jun is saying, "You can create a chart from a data set to display information visually." We highlight the data, we go to the chart icon and we change to the type of chart that we want to use.

We can also customise the chart with chart titles and axes labels.

(no audio) These tools can be combined to present data.

Here, I've got the Olympic medal data, so I've used conditional formatting to highlight the highest number of medals.

The total has been calculated using a formula and a graph has been created to present the information.

Time to check your understanding.

A spreadsheet has been created to store the scores of tests that pupils have completed each week in a class.

The teacher wants to calculate the average score of each pupil.

Which tool would they use? A, chart, B function, or C, formatting? Pause the video here whilst you have a think.

That's right, they'd use a function.

In this case, they'd use the AVERAGE function to calculate the average test score for each pupil.

A spreadsheet has been created to store the scores of tests that pupils have completed each week in a class.

The teacher wants to easily identify pupils who scored an average grade of less than 40%.

Which tool would they use? A, conditional formatting, B, function, or C, formatting? Pause the video here whilst you have a think.

That's right, they'd use conditional formatting.

This would allow the teacher to easily identify any average scores less than 40%.

And they'd use conditional formatting to do this automatically over a large data set, rather than just going through and individually formatting the cells themselves.

You're doing an absolutely fantastic job so far, so well done.

We're now moving on to Task B.

For part 1, download the UK Forestry spreadsheet.

Use the sort and filter tools to a, select the entire sheet and sort the data by ascending size in hectares, making sure that the column headings in the first row stay where they are at the top of the sheet.

And then for part b, use the filter to display only forests located in Hampshire and Snowdonia.

Pause the video here whilst you complete the activity.

For part 3, use functions to work out each of the following in the spaces provided on the spreadsheet: a, the size of the largest forest.

b, the size of the smallest forest.

c, the total area covered by all the forests.

d, the average forest size.

e, the number of entries in the size column.

f, the number of forests in England.

And as a hint, you need to find how many times England appears in the country column.

And then for the final part, part g, the number of forests that are greater than 10,000 hectares in size.

Pause the video here whilst you complete the activity.

For part 4, use formulas and functions to: a, add a new column called "Size" in acres there in brackets And in that column, convert all of the hectare measurements to acres using the formula acres=hectares*2.

47.

And then for part b, add another new column called "Size category." The entries in this column should read either "small" if the area is less than 1,000 hectares and large otherwise.

Pause the video here whilst you complete the activity.

For part 5, use conditional formatting to highlight forests that are larger than 10,000 hectares in size.

You can use any colour you like.

For part 6, a survey is regularly conducted in the UK to measure public opinion of forestry.

One question asks people how often they visit forests in the summer.

The results of this question are shown in the spreadsheet.

For part a, make a pie chart that shows the proportion of each response in 2009.

And then for part b, make a pie chart showing the proportion of each response in 2019.

Pause the video here whilst you complete the activities.

For the final part of Task B, what does the data in the charts tell you about the trends of summer visits to forests in the UK? Pause the video here whilst you have a look at your charts and complete the activity.

How did you get on? There are an awful lot of tools that you had to use for these activities so well done.

For part 2a, you were asked to select the entire sheet and sort the data by ascending size in hectares.

If you've done this correctly, then Garscadden Wood should be at the top of your list.

For part b, you were asked to use a filter to only display the forests located in Hampshire and Snowdonia.

So you can see here my location only has Hampshire and Snowdonia in the results.

For part 3, you were asked to use functions to work out the answers to the questions.

So for part a, you were asked to use a function to calculate the size of the largest forest.

So hopefully, you used the MAX function here and the answer is 97,000.

For part b, you were asked to use a function to calculate the size of the smallest forest.

Hopefully, you used the MIN function here and the answer is 16.

94.

For part c, you were asked to calculate the total area covered by all the forests.

Hopefully, you used the SUM function here and you should have the total number 407216.

94.

For part d, you were asked to calculate the average forest size so hopefully, you used the AVERAGE function here, and the correct answer is 7,683.

For part e, you were asked to calculate the number of entries in the Size column, so you would've used a COUNTA function here 'cause remember that returns the number of non-blank cells.

So the correct answer for this is 53.

For part f, you were asked to calculate the number of forests in England, and as a hint, you were asked to find how many times "England" appears in the country column.

So for this example, you will have used the COUNTIF function and the correct answer would be 35.

For part g, you were asked to calculate the number of forests that are greater than 10,000 hectares.

Again, you'd use the COUNTIF function here, and this time the correct answer is 10.

For part 4, you were asked to use formulas and functions to first add a new column called "Size" in acres and then convert all of the hectare measurements to acres using the formulas =hectares*2.

47.

So you can see here on my screen grab, that I've got a new column called "Size" in acres and the first one is 0, and the next one for Afan Forest Park is 27,170 and so on.

For part b, you were asked to add another new column called "Size categories," and the entries in the column should read either "small" if the area is less than 1,000 hectares or "large" otherwise And in this example, you needed to use an IF function.

So you can see here in the "Size category" column I've got either small or large, depending on the size in hectares of the forest.

For part 5, you were asked to use conditional formatting to highlight all of the forests that were larger than 10,000 hectares in size and you could use any colour that you liked.

So here I've got a screen grab and you can see that there are two forests here that are highlighted with a green background colour.

For part 6, you were asked to create some charts to represent a survey that's regularly conducted in the UK.

The first chart should be a pie chart that shows the proportion of each response in 2009.

And the second should have been a pie chart that shows the proportion of each response in 2019.

You can see here we've got two pie charts as an example with the correct titles.

And finally, for part 7, you were asked what does the data in the charts tell you about the trends of summer visits to forests in the UK? Here's a sample answer.

The data shows that the number of summer forest visits has increased in general, with the number of never responses reducing from 5% in 2009 to 4% in 2019.

The number of people visiting a forest several times a week has increased from 11.

9% in 2009 to 16.

8% in 2019.

Well done, you have done an absolutely fantastic job today and you have combined so many spreadsheet tools.

Let's summarise what we have learnt in this lesson.

Conditional formatting can be applied to cells that match a certain criteria.

A range of spreadsheet tools can be combined to effectively analyse and present data.

For example, formulas and functions can be used to create calculations and then, the data from these calculations can be displayed in charts.

Thank you very much for joining me for today's lesson, bye.