Lesson video

Hello, my name is Mrs. Holborow, and welcome to Computing.

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

We are going to be using spreadsheets in today's lesson.

We are going to identify some components of a spreadsheet and then see how we can apply formatting to cells in a spreadsheet.

Welcome to today's lesson from the unit, "Data Modelling." This lesson is called, "Formatting data in a spreadsheet." And by the end of today's lesson, you'll be able to identify the components of a spreadsheet and apply formatting to improve the presentation of data.

You'll need to have access to some spreadsheet software for this lesson.

Shall we make a start? We will be exploring these key words during today's lesson.

Cell.

Cell.

A rectangular box that holds data in a spreadsheet.

Column.

Column.

A vertical group of cells that runs from the top to the bottom.

Row.

Row.

A horizontal line of cells that runs from left to right.

Formatting.

Formatting.

Modifying the appearance of data in a spreadsheet.

There are two main parts to today's lesson.

We'll start by identifying the components of a spreadsheet, and then we'll move on to apply some formatting to spreadsheet cells.

Let's make a start by identifying the components of a spreadsheet.

What does this data show? Maybe pause your video and have a think.

Did you know what the data was showing? Neither did I.

There's some countries and then there's some numbers, but it doesn't really explain to us what the data is about.

You are part of a team of journalists doing research about the results from a summer Olympic games.

You've been given some data about the seven most successful countries, which you need to use for your report.

Ah, now this makes more sense.

We have a few labels.

So, we have the countries on the left hand side still, and then we've got the heading, "Medals won." And then rather than just the numbers, we have the words Gold, Silver, and Bronze.

So we know for example, that Germany won 17 gold medals, 10 silver medals and 15 bronze medals.

Examine the data and see if you can answer the questions that follow.

How many medals did Great Britain win? That's right, 67 in total, because we've added up 27, 23 and 17.

Who won the most gold medals? That's right, it was the United States of America, or USA.

Who won the most silver medals? That's right, it was the USA again.

Who won the fewest medals? That's correct, it was Japan.

What is data? Maybe pause the video whilst you have a think.

Data is a letter, word, number, et cetera, that has been collected for a purpose, but it is stored without context.

The data you saw at the start of the lesson had no context as there were no headings or labels to describe what the data was about.

So when we first had a look at the data, we didn't know what those numbers meant in relation to the names of the countries.

Sam is saying, "The data is much easier to read in the table and the totals have already been calculated." A spreadsheet is a software tool used to store and analyse data.

A spreadsheet organises data into a table.

Cells are spaces that hold data in a spreadsheet.

A cell reference tells you the location of a cell.

A bit like when you go to a stadium or a theatre and each seat has a number and sometimes a row so that you can find exactly you are meant to be sat.

Each cell reference is made up from the column name and the row number.

This is cell C3.

This is cell D2.

Notice that the column letter always comes before the row number.

Time to check your understanding.

What is this cell? Pause the video here whilst you have a think.

Well done.

it's cell B4.

Remember, the column letter always comes before the row number.

A cell range is a group of cells in a spreadsheet that have been selected.

So more than one cell.

A cell range is made up by the cell addresses of the first and the last cells in the range separated by a colon.

So, the cell range of these cells is A2:A5 because A2 is the first cell in the range and A5 is the last cell in the range.

Time to check your understanding.

What is the cell range of these selected cells? Is it A, B1 to B3.

B, B1 to D1.

or C, B1 to D3.

Pause your video here whilst you have a think.

Did you select B? Great job.

The cell range that is selected is B1 to D1.

And that's because B1 is the first cell that is highlighted and D1 is the last cell in the range that is highlighted.

Okay, we are now moving on to the first task of today's lesson.

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

So for task A part one, I'd like you to draw a dotted line around and label the following spreadsheet components.

So A, a cell, B, a row, and C, a column.

Pause your video whilst you complete the activity.

For part two, I'd like you to shade the cell ranges on the spreadsheet.

So for A, you need to shade areas A2 to D2.

For B, you need to shade the cell range E4 to E7.

And for C, you need to shade the cell range A4 to C4.

Pause your video here whilst you complete the task.

For the final part of task A, I would like you to explain how a spreadsheet can be used to organise and display data.

Pause the video whilst you have a think and complete the activity.

How did you get on? You were asked to draw a dotted line around and label the following spreadsheet components.

A cell, a row, and a column.

So here's a sample answer because you may not have highlighted exactly the same rows and columns as I have.

So for example here, I've drawn a dotted line around cell A4 and I've labelled that as a cell.

And then I've drawn a dotted line all the way across row 2, and I've labelled that as a row.

And then I've drawn a dotted line all around column D and I've labelled that as a column.

You were then asked to shade the cell ranges on the spreadsheet.

So for A, you needed to shade the cell range A2 to D2.

That should look something like this.

For B, you were asked to shade E4 to E7.

That should look like this.

And then for the last one, part C, you were asked to shade the cell range A4 to C4.

So that should look like that.

Remember, if you need to make any corrections, you can always pause your video.

For the last part of task A, you were asked to explain how a spreadsheet can be used to organise and display data.

Here's a sample answer.

A spreadsheet can be used to present information in a table.

The data is organised into columns and rows which make the data easier to read and analyse.

We are now moving on to the second part of today's lesson where we are going to apply formatting to spreadsheet cells.

Formatting changes the way data appears in a cell.

You can change the formatting of a cell by changing the font, changing the text alignment, adding a border, and adding a fill to change the background colour.

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

In this example, the table headings and largest number of medals have different background fill colours to make them stand out to the reader.

Time to check your understanding.

Why would you apply formatting to a cell? Pause your video whilst you have a think.

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

This is useful if you are trying to highlight key information to the reader.

Was your answer similar? Remember, you can always pause the video here and add extra detail to your answer if you need to.

You can change the fill or background colour of a cell by selecting the background fill tool and selecting the colour you want to use.

In different pieces of spreadsheet software, the tool may look and appear slightly differently.

However, it's normally something similar to a paint can.

Should we see how to apply background formatting in a spreadsheet? Okay, so here I've got a spreadsheet with four cells containing the words Red, Yellow, Green, and Blue.

And I'm going to apply a background colour to these cells.

So I'm going to start by selecting the cell and then I'm going to find the paint bucket tool or the fill colour tool, which is here.

I'm going to drop that down with the little arrow and I'm gonna select the background colour that I want.

So for this one I'm going to choose a red colour.

I'm gonna choose the next cell.

Go to the fill colour tool and choose a yellow.

Next one is going to be a green.

And then the last cell, which is D1, I'm going to select, go to the fill colour tool and then choose a blue colour.

You can add borders to the edge of a cell using the border tool.

Again, the tool may appear differently in different spreadsheet software.

Shall we see how to apply a border? Okay, this time I'm going to apply a border to my cells.

So I'm gonna highlight the cell range A1 to D1, which contains my data.

I'm going to go to the borders tool, which looks a bit like a window.

I'm going to select that and then I'm gonna choose All Borders, which will put a border around each individual cell in the range.

Now if I want to, what I can do is re-highlight that and I can change the properties of that border.

So if I go back into the borders tool, I can change the colour of the border and I can change the thickness.

But what I do have to do is reapply that all borders tool, so I'm gonna press that again, and then you'll see that this time there's a red border, which is a bit thicker around the edge of the cells.

You can change the colour of text in a cell using the text colour tool.

Shall we see how to change the colour of text in the spreadsheet? Okay.

Same text as I had last time with the Red, Yellow, Green, and Blue.

But this time I'm going to change the font colour in these cells.

So I'm going to start by selecting cell A1, which includes the word Red.

I'm going to go to the text colour tool, which is here.

I'm going to click it and then I'm going to select the text colour that I want to use.

So repeat that again.

So B1 this time.

Go to the text colour tool.

I'm gonna choose a yellow for that one.

C1, going to choose a green.

And then last one, D1, back up to the text colour tool, and I'm going to choose a blue colour.

Time to check your understanding.

Which tool would you use to add a background fill to a cell? Is it A, B, or C? Pause the video here whilst you have a think.

that's right, the correct tool was A.

The paint bucket tool is used to add background fill to a cell in most spreadsheet software.

Okay, we are now moving on to the final set of activities for today's lesson.

You're doing a great job so far, so well done.

I'd like you to start by opening a blank spreadsheet.

This can be in any spreadsheet tool of your choice.

You are then going to create a flag for a country by following these steps.

A, apply a blue fill colour to the following cells.

A1 to B15.

Leave the following cells white.

C1 to D15.

Apply a red fill colour to the following cells.

E1 to F15.

And then lastly, apply an outside border to cells A1 to F15.

Do you recognise the flag? Pause the video here whilst you complete the activity.

How did you get on? Hopefully you've created something similar to what's on my screen now and that you recognise that the flag was the French flag.

Okay, for the final activity of today's lesson, I'd like you to choose two more flags, and I've included some on the slide to help you.

I'd like you to start by writing the instructions to create the flag on a spreadsheet.

Imagine you are gonna give them to a partner and they need to be able to follow the instructions to create the flag.

I then want you to change the text colour of the instructions to match the colour that the fill is being used.

So for example, if you are saying that the fill colour should be green, then your instructions should be written in text colour green.

Then finally, follow your instructions to create the flags using the formatting tools.

Pause the video here whilst you complete the activity.

How did you get on? Hopefully you managed to create some instructions and use those instructions to create some flags.

Here's an example for you.

So I've chosen the Swedish flag and I've written my instructions.

So I've got apply a blue fill colour to the cells A1 to B6, A10 to B15, D1 to F6, D10 to F15.

And notice my instructions for the blue fill have been formatted in the colour blue.

And then I've got apply yellow fill colour to the cells C1 to C15, and then A7 to F9.

Remember, your instructions may look quite different because you may have chosen a different flag.

Okay, we've come to the end of today's lesson and you've done a fantastic job, so well done.

Let's summarise what we have learned.

A spreadsheet is a tool used to store and analyse data.

In a spreadsheet, data is stored in cells which are arranged in rows and columns.

Formatting can be applied to data in a spreadsheet to make the data easier to analyse.

I hope you'll join me again soon.

Bye.