Lesson video

Hi everybody, my name is Halima.

I am your computing teacher, and I will be teaching you about spreadsheets.

I hope you enjoy learning about spreadsheets as much as I enjoy teaching this as this is my favourite module.

In order to begin, you first of all need to get permission from your parent, carer or teacher if you are 13 years of age or below.

So please make sure you do this as you will need to create a Gmail account.

We are going to be using Google sheets and that is what's going to act as our spreadsheet.

So please make sure you have this prior to starting anything.

You will also require a pen, paper to make notes with and possibly a calculator if you want to double check anything.

So let's get started.

Lesson six spreadsheets.

In this lesson, you will learn about conditional formatting.

You will also apply all the skills that you've learned so far in this unit for spreadsheets.

So well done for getting to lesson six, let's begin.

Conditional formatting.

What is it? It's when the spreadsheet highlights cells of a similar criteria, and you can see an example of this in the screenshot, conditional formatting has been applied to this final column, and you can see that all the criteria that say yes, have been highlighted.

It's good for analysing data, as it means you don't have to go through everything to find the similar criteria.

Conditional formatting can be set to certain values so that they can be highlighted as seen in the image and as I explained.

So let's have a closer look at this, conditional formatting.

Let's have a look in Google sheets as our spreadsheet here is my data.

I have data here about people and what kind of sports they like to play or are involved in.

My last column column I is about, would you like to receive our newsletter? So you can see the survey was done here and data was collected.

We're ready to analyse that using conditional formatting at the moment I cannot see all the texts in my row one.

So to make maximise this or to make the row bigger, I can actually move the rows and you can do the same with columns so that you are able to see the full data you can expand them.

To start a conditional formatting you have to highlight the data that you would like to conditional format.

I'm going to do column I, you have two ways of having conditional formatting.

You could go to format there's conditional formatting, or at any point if you are stuck and you do not know in a spreadsheet where to find something, you could go to the help menu and type in conditional formatting.

And it will find that for you or any of the data that you cannot find.

I will select the cell is empty at the moment as that allows me to go to the conditional format rules, and this is how conditional formatting works, it works by having a set of rules.

You can choose to have a colour scale.

I'm going to go green and I'm going to select a single colour single colour because I only want to know one thing which is who would like to receive the newsletter.

If I have more options, maybe looking at grades of students, or other information I might decide to have a scale, which means that there will be shading applied to your actual data, allowing you to easily pick up who has the highest or the lowest or the mid points.

In my case, I am only looking for a single colour at the moment it's saying my data is empty that's not correct.

I want it to look for data, which has a certain text.

So text contains, yes.

And as you can see, it's automatically shown me as a preview, what this looks like.

Now this will not work until you actually come off the conditional format rules.

So just to come off it and click off it, you can now see it is applied this rule and it has highlighted the data.

And this is what makes conditional formatting very good for analysing data because you can quickly and easily find information.

Shading is also a brilliant way to find information so I hope you can use some of these skills later on in your task.

So let's highlight some of the skills you've learned in this unit for spreadsheets.

Well done for getting to lesson six that is a great achievement.

In this unit so far, we have learned about formatting the spreadsheet, sorting and filtering data, adding charts and knowing which are the correct charts to use, COUNTA, MAX, MIN, SUM, AVERAGE, IF, COUNTIF functions which we can create as formulas.

We've learned about conditional formatting and also at the same time, we've learnt about how data can be analysed.

What is data? What is information? Let's pause to do task one U Tub data this can be found in the worksheet.

Task one, let's have a closer look.

Here you're given some data from a website called U Tub and this data looks at videos, it looks at how many people subscribed to particular channels and how many people may have viewed the video.

It also grades them and ranks them.

So there are three tasks you have to do for task one.

The first one was you had to sort the data, the second one was to use conditional formatting and the third task here was to filter the data.

So this is all part of task one.

Let's have a look at the first thing that you had to do, which was to sort the data.

So the display name is what you had to sort.

So I'm going to highlight the display name and I'm going to right click and I'm going to click on sort range A to Z sort.

And that was the first thing we had to do.

The second thing we need to do is we need to add a conditional formatting on our videos.

So I'm going to highlight the videos I'm going to go to format, conditional formatting.

And in this case, I'm actually going to use the shading there you go, you can see the shading.

I can very quickly notice from the shading, those view, those videos, which have got the most views in it and those which have the few.

So the most viewed are the ones which are the lightest or white, and the least viewed are those that are darker.

If you've reached this point well done, don't worry if you didn't, you can always pause the video and go back.

Let's look at the last thing that you had to do for task one.

This was to do with filtering and we need to filter our grades.

Here I'm going to use the funnel as a filter, and I'm going to look for all the data in our case which is an A.

So I will untick the sections I do not need and this is what we should have as an answer.

Did you get the same, well done if you did, and this is what task one would look like.

So well done for having a go at that.

Task two can be found in your worksheet U Tub Function and Formula.

You can pause the video now.

Let's have a closer look at task two and how it could have been done.

First of all, doing a total so doing a total involved, a SUM formula.

Do you remember what a formula starts with? That's correct, it is an equal sign.

SUM represent add or total.

I have brackets I highlight my cells and close the bracket and this gives me the total.

Well done if you got this answer, let's move on to have a look at other formulas.

So, ranking.

How do we find the highest value? Do you remember the formula? Yes, it is MAX, equals MAX open bracket and here I am looking at videos.

So I'm going to highlight those brackets and this will find the highest value or highest amount of viewers for a particular video.

Let's have a look.

Actually, what I've done here is for column F is that correct? Well done If you did spot that.

Do you remember how to undo something? Yes by using the undo.

So let's try this formula again, equals MAX, my videos is in column D and that should be my value.

Well done if we got the same value.

let's try MIN as the lowest value, well done if you did get that correct, let's have a look at the AVERAGE and it's all the same column in D, looking at videos notice I'm not selecting the whole column, I'm only selecting the cell range that I'm looking at and non blanks is COUNTA let's find how many have been filled in? All of them have been filled in, so it should give me a value for all of them.

And the answer is 13, well done.

Let's have a look at our IF formula now we already have one prepared in here.

So equals IF E3 so that's my subscribe at if it's more than 50 million, it will say over 50 million subs, or subscribers for short or too low and that should be the IF formula there.

I'll just give you a moment to have a quick read of that.

You can check this against your answer, and if you haven't got this answer, do not worry.

You can use this to help you to change your answer.

Let's create an AutoFill now to make sure I don't have to type the formula again.

And we can easily see which one are Over and which one are Too, if you wanted you can use the skills of conditional formatting to highlight the data that you want to look up.

Let's move on to have a look at our grades.

Count how many AA gradings there are.

My grading is in column B so my formula is going to be equals COUNTIF open bracket, I select the range don't forget the comma, because this is what splits it.

And I'm looking in this case with a capital A plus plus, you spot what the error is here Why did this formula not work? Let me give you a time to think.

Well done if you did get it right, it needed quote marks.

The A plus plus because it has values in it so not just numbers, but letters and characters, you would need to use quotes, otherwise the computer will not know what it is.

This has now worked and given us the value of too.

Now you could easily do an AutoFill on all of these and then change the value to being A plus B plus, which will probably be the easier way.

But just to recap how this is done so equals COUNTIF, Select the range with a comma, and this time I'm looking for A plus.

I notice I had an error again I was too quick and forgot my quotes, so don't forget the quotes.

In this occasion, I am going to do a AutoFill, but something has happened here.

If you notice that in this AutoFill, the range of cells is actually changing.

So that one says B4 to B16, that says B5 to B17.

So AutoFill is very good for working out equations quickly, but sometimes you have to check it and in this case it has not worked.

So I am going to now delete all those and do then individually by hands.

Equals, COUNTIF, select my range, comma, quote, not forgetting your quote A and this is where I should tap and it should be quite quick and easy to do the rest.

Don't forget to close your quotes as well are very important otherwise it will still not work.

So just to make this a little bit quick enough for us, I add my quote and it is B and my last one is going to be COUNTIF my range comma And it is B minus And I'm just going to move this spreadsheet a little bit down so you're able to see those answers just below there.

And that's how you do task two.

Let's have a look at task three now in our worksheets, this is your final task.

So well done for all the achievements you've been doing so far in completing your tasks in the worksheet.

This one is called U Tub Charts.

Okay, so task three let's have a look how to do this.

This is where you had to create a chart, so you were asked to create a chart on grades.

I'm going to highlight this data I'm just going to move the spreadsheet little up so you can see the data there we go.

We already used a function COUNTIF to work out how many grades for each we have.

We're going to put this into a chart in a picture format so it makes it easier for us to analyse that data.

So I highlight that data, including the titles.

I go to insert chart and the computer has selected a pie chart, but that's not what I want to use.

I'm going to select the bar chart.

The title needs to be something more appropriate or just call it grades, good, call it Graded U Tub so you know what it's about, and that is how you would make your chart.

You can of course change the chart, make it much more interesting with the colours and changing the type of lecturing the font by applying with the creator of the chart.

You can do this by double clicking and going to the chart editor, but that is how you do number three.

Well done for your achievements.

If you managed to create a chart and even more well done if you're able to select the appropriate chart for this task, Why not share the good work that you've been doing with Oak National? You can find us on Facebook and Twitter and Instagram, just make sure you get permission before you post anything.

We really look forward to seeing your work.

Thank you, bye bye.

And see you again soon.