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 two, spreadsheets.

In this lesson, you will use basic formulas with cell references for calculations.

You will also use the AutoFill tool to replicate cell data.

So let's begin.

A calculator or a spreadsheet.

How would you work out two times six in your head? So you would probably take the number two.

And one of the ways you might do this is add to six times, or you may take the number six and add that two times.

Alternatively, as it is a very simple equation, you might find, you know, the answer for two times six without needing to work the answer out.

How would you work out six times seven on your calculator? You would type six times seven.

Okay.

Easy as.

But what if you have very complicated numbers, how would you work that out? What if I was to ask you 126 times 748 or, 621, add 1074.

They might not be as easy to work out in your head or as easy to work out on your calculator.

Therefore, we may need to make the use of a spreadsheet.

Spreadsheets are much faster, especially when you have a lot of equations that you need to work out.

Examples include shops who have lots of stock, and they need to know how many things they sold, how many things they haven't, how much profit they've made, or how much money they owe somebody.

This can all be done on a spreadsheet.

A career in accountancy would be a very good place where spreadsheets are used.

Your teachers probably already use them as well.

When they do your grades, they probably want to know information about you or how many grades or marks you are less in getting a certain grade or a Mark.

So spreadsheets can be very useful.

So your first formula.

A formula is an equation or a calculation, which you would do in spreadsheet.

It starts with eight an equal sign.

With a pen and paper, you may want to make a note about this because as we go through the lessons, you will need to know what a formula starts with and how to create one.

There are lots of functions and formulas that can be used in spreadsheet.

And over the next few lessons, we will be exploring those.

Just to get started, looking at a basic formula.

So we know that a formula starts with an equal sign.

Therefore, we will need to put an equal at the beginning.

There are two symbols that I would want you to remember today.

That is a times symbol or a multiplication symbol, which on your keyboard is a star.

The next symbol is a divide symbol, which is a slash.

I will give you a bit of time just to find that on your keyboard.

Okay, so on my slide, I have got an example of a formula, which has got three times one, but we don't do it as three times one, we use cell references, and as you can see, it has a cell reference in there of equals C20 star, which means times E20.

But let's explore this further in our spreadsheet.

So let's have a look at this spreadsheet, which is in Google Sheets.

I have some numbers here that you will be able to see.

So random numbers, one to four, I've typed in one column.

And some that I've typed in another column.

If you like, you could provide the title.

So you know what the numbers are about, um, Number of Cakes, Number of Hats.

So it could just be that you would like, I'm just going to change that to a capital.

And it might just be that you would like to know, um, by adding these, how many items you have for a party as an example.

Um, and I'm going to demonstrate this for you.

So a formula always starts with an equal, so please make sure you have an equal sign at the start of your formula.

Now, instead of having the number one, so instead of saying one, I'm actually going to add a cell reference.

So the number one is in the cell C4.

If I click on C4, you will notice the computer has automatically found C4.

And this is an easier way to do a spreadsheet equation or a formula, because it is able to find that reference for you without you needing to know what it is.

It does help to know where this number is in your cell reference, because it may not always be correct.

So please make sure that you do check.

If I'm adding a number together, I simply just use the add symbol.

And then I add my second number, which is located in D4.

As soon as I press enter, it changes to number three.

And I can do this all of my equations.

And so you can see it, it's taken me a bit of time.

So I will be showing you how to do a little bit of a cheat, once you know how to do the first formula.

But to start us off, I'm going to do a few there.

And this uses the App Tool.

Okay, let's say it could be that you want to know App Tool.

But i'll call it, add for now.

So you know what I have done in this column.

The next column am going to do is going to involve a multi, in fact I'll call times or multiplication, which is times.

Again, I use an equal symbol.

I'm going to go and select the cell that I would like to multiply and select the second cell that I wanted to be multiplied by.

So here you can see as equal C4*, remember a star means times on a computer, T4.

And I will do this for a few of these so that you can actually see how it works.

I forgot to put my equal sign.

Now I have it, it should work.

And one more equals, and there we go.

I have my times.

The last one that I would like to show you is a divide.

So am going to add equals, equals, equals.

As you can see, I have now added, I have times and I have a divide.

And each time I click on any of these cells at the top, there is a, um, a section called FX.

It shows you the formula that's been typed into the cell.

This section is called a Formula Bar.

And that is where you can see what the formula is, if you would wanted to check anything.

And that is how to do a basic formula.

Here's another example I can show you.

I'm going to do the first one for you, just so you can see how it works.

So I'm going to do equals C6.

And on this occasion, it tells you to times E6, and my answer is 98.

And that is how you would do a basic formula.

Let's pause the video so that you can do the activity.

This activity can be found in your worksheet.

So let's have a look at your answers.

So the first one is done for you, the answer was 98.

I did that as a demonstration prior to starting the worksheet task.

Question number two, equals C7 plus E7.

That gives you 235.

Question number three, equals C8 minus E8.

Question number four, C9 times or star E9.

And then lastly, equals C10 divided by E10.

I noticed that each time I have not used the number, but I have used the cell reference.

Why have I done this? What makes spreadsheets so special? Let me show you.

Because I have a formula that references a cell reference.

It doesn't matter what numbers or figures you have in your cell.

So if this was to change to 20, you will notice that my answer also changes.

Let me show that again, keep an eye out on the answers.

I'm going to change it to 25 and the answer changes.

I notice I can do this for all the different cells I have.

The plus times divide depends on what I've been shown in my column D and that's where I picked that ane from.

And this is why we use a formula.

If I do the same formula as 14.

So I forgot the equal signs, that equals, please don't forget, 14 times 7, enter.

It will give me the same answers question one.

But, I can't change it.

Okay, notice if I change the above because a formula, it will change the answer.

I would probably need to go into the formula itself in the formula bar to make changes, to get a different answer.

But like here you I don't remember what number might have been before.

140 is what I changed it to.

Here, I've got 20, but I've got 14.

And if my formula changes, if my numbers, sorry change my formula will also change.

Here, I've got no numbers next to it.

So I cannot go back to see what numbers I have used in order to make this.

But again, because it's not using a cell reference, it's using a number in order to times things together.

If I use the cell reference, it's looking at what in the cell, rather than the number itself.

I hope that explains it much better Autofill, so what is it? Autofill, is when you can automatically copy a formula along, down a column or a row.

So you don't have to re input the formula again.

You will notice that on this slide, I am pointing, there's an arrow pointing towards a blue square in a cell.

This square is always on the bottom right hand side of the cell.

When you hover your mouse over it, your cursor will become across.

And in a moment, I will demonstrate this for you.

So when you click on the blue square, you drag it down and it will copy your formula.

Now at the moment, the cell that you're looking at has no formula in that.

But I will show you in a minute, how this can be done.

So Autofill, let me do a demonstration now to show you how it actually works.

You see on my spreadsheet, I'm using Google Sheets here.

I have a formula which adds up two numbers together.

If you do want to check your formula, you can check it in the formula bar, which is just here.

I noticed when I clicked on the formula, it selects the two cells, which are also involved in the formula.

Here, I am adding two numbers together.

So my formula is equal C4 plus D4.

Okay, so once the cell is clicked, there is a blue square on the bottom right hand side of the cell.

This is where you need to hover your mouse.

And if you remember, I mentioned that my cursor will turn into a cross, which it has.

I can therefore click using my mouse and keeping my finger on the click of the mouse.

I'm going to drag this formula down.

It now appears with numbers, but if I click each of the cells, you will notice there is a formula.

And you can tell if you look at the formula bar.

Let's try this again.

But this time we are going to times two numbers together.

Easy as, let's do the same with the divide.

And that is how you use a Autofill.

So please make sure you try having a go at using Autofill when you come to do your task.

Okay, so let's pause here in order for you to complete the worksheet task.

So let's have a look at our Google Sheets.

I've already got the first few answers for each of my cake recipes here.

These tables tell you for depending on which cake or cupcake you would like to make, the measurements, the quantity and then you are asked in column D and E and F to write a formula.

So the two times table would basically be the quantity followed by the number two.

So you will notice at the top, it says equals C3 times two.

So far, I have been using cell references.

So this was a bit of a challenge to think about that you would need to use the number two instead of another cell reference.

Well done, if you got that correct.

And I go work my way down and I do exactly the same.

If you used the Autofill, you would have been able to copy that formula down for each of the equations you've been asked to work out and very easily be able to complete this worksheet.

I am just doing this now.

So just a reminder, the Autofill is when you have a cross, you put it on top of the square, the blue square, and you drag it down.

And that is exactly what I am doing over here.

You should notice you are getting the same answers as me, if you have made the same worksheet.

So I'm just going to complete this.

So here's the two times, the three times, And the divide by two, remember divided by two is a slash it doesn't use a star.

Star is only if the times.

This side, everything from A to F is to do with muffins, everything from column G.

So everything from column G up until out is to do with cupcakes.

Now you will notice that some of my columns and rows are bigger than others.

How did I do this? I can simply go between the columns and I can expand them by clicking and dragging them in and out.

And that's how I made them bigger or smaller.

You can do the same with the rows.

You can make them bigger or you can make them smaller.

Remember if you make a mistake, that's fine.

It's all about learning.

If you to go back to something that you had previously done, and you did make a mistake, there is the toolbar, and there is also redo if you change your mind from undoing something.

So they are very useful tools that you could use in Google Sheets.

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.