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 four spreadsheets.

In this lesson, you will analyse data, you will create charts in your spreadsheet.

And you will also use the functions SUM, COUNTA MAX and MIN.

Examples of these functions.

So SUM allows you to add cells or cells of data together.

COUNTA counts how many filled cells there are.

MAX finds the highest value in the selected cells.

MIN does the opposite it finds the minimum or lowest value in the cells that you have selected.

And all these functions are made up with a equal sign at the beginning of it as a formula because they are formulas, but we know them as functions.

There is a screenshot from Google Sheets on the right hand side where you can see how a SUM formula is calculated.

And later on, I will be demonstrating this.

So let's have a look at some data.

I'm now going to teach you how to do the functions and how to use them in order to make calculations.

So you can see I have two sets of data, data one and data two.

The first thing I need to do is work out the total or the SUM.

A SUM is another way of saying add or total.

I start with an equal sign because it is a formula, even though we know it as a function.

And you can see that a hint comes up, and the hint suggests how to do a SUM.

So you could follow this in order to help you if you are stuck when you come to do your own task.

I typed the word SUM, and I'm using capital letters here.

I then have an open bracket, to show that I'm going to add data together, and I highlight it.

Just before I close the bracket, the importance of brackets in spreadsheets.

Brackets, just like in maths, mean that you do something first.

Here, I want to add all this data together first.

Brackets are very good when you've got lots of data and you want to do two or three different things at the same time.

You can use them in order to allow the computer to know what you want to do first.

So going back I'm going to close my bracket and you can see appears in my formula bar, I press enter.

At any point if you do anything wrong, remember there is the undo button.

There is also the redo button in case you think to yourself that hold on, I didn't want to undo it.

So you can take that off.

MAX equals, I just follow, MAX, open bracket exactly the same way as how I did my sum.

Highlight the cells that I want to find the highest value in.

Remember, I'm not going to put the SUM in, because that would automatically be the highest value, but I don't want to look at that.

And I close my bracket.

I can now see that number four is the highest value or the maximum value in this range.

I'm going to do the same with the MIN or minimum to find the lowest value.

I can see that number one is the lowest value.

What does COUNTA do? Let's have a look.

Always remember to add your brackets.

Hmm.

COUNTA says eight, but I do not have a number eight in here.

Let me just highlight this and make it into a different colour.

I will make it green, just so you can see this is where the formulas were added.

Why does it say eight? Let me have a look.

How many pieces of data do I have here? One, two, three, four, five, six, seven, eight.

So what COUNTA is doing, it's counting how many cells have got data in them.

If I get rid of one piece of data, you will notice it is now blank.

And my data has now become seven instead of eight.

And this is how you can use your functions and formulas.

Let's pause the video to have a go at task one in your worksheets.

So here is the spreadsheet, using Google Sheets.

As you can see, there are sections highlighted in colour, there are cells that I have got in colour.

And this is where you need to add the formula of functions.

So let's have a look how this is done.

Equals, COUNTA, don't forget those brackets, highlight the data, close bracket, and you should get your answer which is eight.

Put chocolate chip muffins.

Now could retype this data two more times.

But as we know how to do autofill, we can use that in order to fill in our information.

And we should find all of it should say eight.

Why should it say eight? Let me give you some time to think.

The answer should be eight because there are only eight sets of data that we have highlighted or we're looking at.

Remember, that is what the COUNTA function does.

It tries to find out how many cells have data in them.

If there are any blank, it will not count this.

So well done if you got that right.

And well done if you got this answer right in your task.

Let's move on and have a look at some of the other things here.

So equals, SUM, open bracket, I highlight my data, I close it, I close my bracket, press enter.

I'm now going to copy this across.

And you will notice that because the other cells are not filled in, it has only got the first answer there.

I'm now going to highlight these.

Okay, let's do the COUNTA formula again.

There we go.

This time, I get seven.

It should be the same.

So let's have a check.

It is.

And we can do the same when it comes to cupcakes.

So equals, this time it is a SUM formula.

I highlight this data with my brackets and press enter.

I'm going to copy this across, just so you can see the information.

I'm going to do the same with MAX which is just equals MAX, I'm going to highlight the information, close bracket and I'm going to copy this across.

And same with MIN.

As you can see, once you are able to do these functions, it makes it very easy to analyse data.

And I'm going to highlight, and I'm going to close.

Now what I am going to do is, I'm just going to move this a little bit down so that you are able to see the answers.

And I'm going to move across.

So there we go.

That's our MAX.

This is our MIN.

And I highlight the data just there, and copy it across.

So hopefully you should be able to see the answers and check yours as well.

And there we go.

Just going to pause so I give you some time to have a look at your own spreadsheets.

Or feel free to pause the video at any point that you need to.

Analysing data.

So data can be analysed in order to compare and find information easily and that is why we analyse data.

The outcome of the analysis can be a report, or as you can see in this image, it can be a chart.

Functions can help us also to analyse this data, as we have just been working with.

Charts.

So, charts is one way that we can analyse data.

And here you can see an example of a pie chart.

This looks at popular fruits that've been eaten.

So, I've had to collect this data as a survey in order to then create a chart which allows me to analyse the data.

From the data, I can actually see that strawberries is the most popular.

This is why a chart is very good at analyse data.

Because it makes the information quicker to find.

Charts is a good way of presenting data in a picture format.

And there are different types of charts that you can have.

So let's have a look in Google Sheets, how to create charts.

You can see I have three charts here that I have already made.

And I have data that I have already collected, a while ago.

So there are three different types of charts I have here.

I'm just going to move this chart so that you're able to see it a bit better.

I have a line chart, which is this one here.

And this line chart is looking at the number of students entering school each year.

I have which fruits are popular, and I also have a chart for favourite colours.

So data for this was collected through a survey.

And now it's being analysed.

You'll notice in some of the charts, while in the line chart and the bar chart, you can't read all the data.

If you want to expand the chart using the squares in the corners, rather than the squares up and down, which can squash it, you will be able to see the full chart and the full data.

So I'm just going to get rid of my charts and show you how to make the charts.

Again, favourite colours if I expand it, you able to see it a bit better.

Okay, so my first data is about number of students entering the school year.

I decided to use the line chart for this, as I'm able to analyse data and see how it's peaking, whether it's going up or down, et cetera.

And it makes it easier with the line chart.

So I'm going to highlight the information first, and that's what we do, in order to make a chart, I then go to insert, chart.

And as you can see, it has found a chart for me.

But this chart is called a scatter chart, and it uses only dots.

And I'm not really interested in that.

So in the chart editor, and I can open the chart editor by double clicking the chart at any point, I'm going to select the line chart.

You can make other changes here as well if you wish, and that can include changing the title, by double clicking on the title, adding colour, and lots of other things that you can do here.

But I'm happy with this.

And I'm going to close that.

And I'm now going to move this chart just below.

It's a bit big, so I'm going to make it a bit smaller, at the moment, I know that there is some data missing, including the title that is fine.

I can always expand this chart later.

The next chart I'm going to make is a pie chart.

Notice when I'm making these charts, I'm not selecting the name pie chart, but I am selecting the headings for the columns.

Important to do this, because when you do this, the computer will automatically generate an X and a Y axes.

Label, so it could be used for labelling of charts, you know exactly what you're reading the data for.

Insert, chart, and it has found me, a pie chart automatically, which I'm happy with.

But if it didn't, I can always change that.

Making it a bit smaller.

I'm going to move this just here.

Okay, my final chart is going to be a bar chart.

So I'm going to insert my chart.

It is decided to use a pie chart so the computer does recommend, but I don't want it to be a pie chart.

I am going to click on the name, and I'm going to change that.

And this is my bar chart, I now easily have all three charts.

And this is how you create charts in your spreadsheet.

Charts and different types of charts.

So as you have seen, I have shown you how to make a line chart, a pie chart and a bar chart.

There are different charts that you can have and there are many others that I have not discussed today that you can use.

For the purpose of this lesson, we only need to consider the three.

So line charts, they are very good when you have lots of data that you want to analyse over a long period of time.

So it might be that you want to analyse data for a few years or a few months and it's very good to see where the line might peak or rise or lower.

Bar charts are very good when you have whole data.

So if I'm collecting simple data, such as favourite fruits, favourite colour, it can give me that data very easily and show me which is the highest amount of colour or fruit that has been selected.

And pie charts are very good with percentages.

So it's very important to make sure when you create charts you select the correct one, because selecting the correct one will help you to analyse the data much better.

Okay, so there is now task two, creating a chart task.

You can find this in the worksheet.

So let's have a look at the answer.

I'm going to select chocolate chip muffins.

I'm going to select the ingredients, the measurements and quantity and highlight all this data using my mouse, I'm going to go to insert and chart.

And I can see is already selected the correct chart for me.

But if it didn't, I can select one from here.

Now you might notice that some charts have a combo chart, which means you can have a line and a bar chart together.

In our case, I only need the bar chart, as shown.

And that is how I would create my chart.

And I can see that flour has the highest amount or quantity in my cake, with egg being the less least, or salt is even more less than eggs.

So there is less salt in my recipe, but there is more flour.

And that is how I can analyse my data and put that data into information.

Well done if you did that correctly.

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.