Hello, welcome to today's computing lesson.

My name's Mr. Little, and I'm delighted to be learning with you wherever you are.

I know if you work hard and try your best in today's lesson, it'll be a success.

The title of the lesson today is "Formatting a Spreadsheet," and that's from the unit "Introduction to Spreadsheets." Are you ready to get started? Great.

Let's go.

By the end of today's lesson, you'll be able to identify cells in a spreadsheet and know how the data has been formatted.

There are two keywords in today's lesson that I want you to look and listen out for.

The first one is cell.

That's a rectangular box in a spreadsheet that's used to store data.

The second keyword is data format, and that's the way in which data is arranged and presented in a spreadsheet.

We're gonna be learning all about these words today, so let's get started.

This lesson is broken down into two parts.

First, we're gonna identify spreadsheet cells.

In a spreadsheet, a cell is a rectangular box used to store data.

Think of it like a rectangle in a big grid.

Each cell can hold a number, a word, of formula, or other things.

Now, spreadsheets usually contain lots of cells.

The image on the left is of one cell.

The image on the right is of a number of cells because the plural of cell is cells.

Let's check your understanding.

A cell is a rectangular box in a spreadsheet used to store data.

Is that true or false? That's right, it's true.

A cell is a rectangular box in a spreadsheet used to store data.

Inside an individual cell, you can store data.

Here in this example, the cell has the word product written in it.

You might have seen spreadsheets with other data in such as numbers in the cells or other written text.

And there are lots of different things you can write or type into cells, and we're gonna be learning about them throughout this unit.

To help navigate your way round a spreadsheet, one of the things you need to know is what a row is.

Now in a spreadsheet, a row is a line of data, and that's the same as it might be in a table that runs from left to right or horizontally.

Here, you can see five cells in row one and they run from left to right horizontally.

The opposite of a row is a column of data in a table.

And instead of running horizontally, it runs vertically from top to bottom.

Here, you can see seven cells in column A.

Let's check your understanding of what rows and columns are.

I want you to answer this question.

Which answer correctly explains what rows and columns are in a spreadsheet? Think carefully on this one.

Is it A, rows run from top to bottom? columns run from left to right.

Is it B, rows run from left to right, columns run from top to bottom? Or is it C, rows and columns both run from top to bottom? It's B and it's a bit of a tongue twister, isn't it? Rows run from left to right.

Columns run from top to bottom.

Well done if you got that right.

Each cell in a spreadsheet has a cell reference, and a cell reference is a little code to help you identify or help you to realise which cell someone is talking about or which cell you want to enter data into.

Cell references combine the column letter and the row number to identify each cell.

So let's take a look at an example.

Here, we've got an image and we've got two columns, column A and column B, and we've got two rows, rows one and two, down the left-hand side.

Each of the cells here has some data in it.

It's either written text or a number.

I've highlighted a cell here.

We would reference this cell as cell A1.

This cell highlighted would be B2.

Cell A1 has the word product written in it.

Cell B2 has the number four written in it.

Using the cell reference makes it easy for you and others to locate a specific cell because when you're using spreadsheets, you'll need to use specific cells.

Now, Izzy asks a really great question here.

Does it matter which way round I use the letters and numbers for a cell reference? Alex is spot on.

He says, "Yes, you should say the column first and then the row.

For example, B5 rather than 5B." So when you're using cell references, you should use the column and then the row.

So quick reminder, remember you can find the column letters at the top of a spreadsheet and the row numbers on the left-hand side.

Let's look at some more examples here.

Column letters are on the top, A, B, C, and it'll keep going all the way along.

And numbers along the left-hand side, 1, 2, 3, 4, 5, 6, and it'll keep going all the way down.

Here, the word trainers is written in cell A2.

So I'm going along the top to column A and then down to row two.

And the word trainers is written in that cell.

Cell A3 has the word jumpers in.

Cell B5 has the number five written in.

So for this one, I'm going along the top.

I'm going A and then B, then I'm counting down 5, 1, 2, 3, 4, 5, and the number five is written in that cell B5.

In cell C1, I'm gonna go along the top A, B, C, and down one, and the word cost is written in cell C1.

Let's look at cell C6.

So I'm going along to column C and I'm gonna count down six.

And there's eight pounds written in cell C6.

Okay, time to check your understanding here.

On the left-hand side, I've got a spreadsheet with some data in and I want you to use the rows and columns to find the cell reference.

So the question is, which cell has the word hat written in it? Is it A, B4? Is it B, A4? Is it C, 4A? Or is it D, C4? That's it.

It's A4.

Well done if you selected that.

So A4 has the word hat written in it.

Let's try another one now.

So use the rows and columns to find the cell reference.

For this question, what's written in cell C2? Is it A, the word cost? Is it B, 12 pounds? Is it C, 20 pounds? Or is it D, four? It's 20 pounds.

Well done if you got that right.

In cell C2, it's 20 pounds.

Okay, time for a practise task now to put all that knowledge about cell references into practise.

I want you to answer the questions using the spreadsheet below.

So on the left-hand side is a spreadsheet.

And on the right-hand side.

there are some questions to work through.

The first question is, which cell has the word trainers in it? So look at the spreadsheet on the left-hand side and work out the cell reference for the cell that has the word trainers in it.

Question two is, which cell has the word shorts in it? Question three, which cell has the number three in it? Question four, write the word that is in cell C1.

Question five, write the number that is in cell B6.

And question six, which cell tells you the cost of shorts? So pause the video now and try and answer those questions.

Let's take a look at some of the answers.

So remember, you are answering the questions using the spreadsheet.

The answer to number one, which cell has the word trainers in it, that's A2.

So cell A2 has the word trainers listed in it.

I'm gonna go along my column to A and down two to cell A2 and that's the word trainers.

So A2 has the word trainers in it.

The answer for number two, which cell has the word shorts in, it's A5.

Cell A5 has the word shorts written in it.

Question three, which cell has the number three in it, that's cell B4.

So I'm gonna go along my columns B, down to the number three and I've gone to cell B4.

Question four, write the word that's in cell C1, that's the word cost.

Question five, write the number that is in cell B6, that's the number seven.

And finally, question six, which cell tells you the cost of shorts? That's cell C5.

In the second part of today's lesson, we're gonna look how to format data in spreadsheet cells.

Izzy and Alex are having a discussion here and they're talking about how their classroom is set up in their school.

They're focusing on how shared stationery is organised into trays, and I'm pretty sure you might have your classroom organised in a similar way.

So let's have a look at what they're talking about.

Izzy says, "My classroom has labelled trays with different items of stationery organised in them.

There are trays for scissors, colouring pencils, paper and other things." Does your classroom have anything similar? Alex says, "This helps keep things organised, and it makes it really clear what's inside and helps me find out what I need easily." Like in your classroom, labelling trays helps keep things organised.

In a spreadsheet, you can organise data and format data to make it clear what's in each cell.

Let's go back to Izzy and Alex's conversation.

Izzy says, "Each cell in a spreadsheet can have a data format, which is like a label." And Alex says, "Each cell in a spreadsheet can be formatted so it's clear what's in each cell, and it often makes it look neater too." Let's check your understanding with this multiple choice question.

I want you to fill in the blank.

In a spreadsheet, you are able to organise and format data to make it clear what is in each.

Is it A, box? B, rectangle? Or C, cell? That's right, it's cell.

Remember, in a spreadsheet, you can organise and format data to make it clear what's in each cell.

Cell is the word we use for those rectangular boxes on a spreadsheet.

Now, like other computer programmes, before you might format data in cells, you can change the style and that might be the style of the font, the colour of the font, or the background colour of a cell.

And you might have done that in computing lessons before working on PowerPoints or other documents where you've changed the look of things or changed the style of things.

And sometimes this can make the table's heading clearer.

So let's go back to our product quantity and cost spreadsheet for our items. Let's look how I formatted the style of these cells to make it a bit clearer what the headings are.

So by changing the colour of row one to have a different background colour and font colour, I've tried to make the table headings clearer.

Now, to make these changes, you need to use the background colour icon and the font icon and they're on the screen now.

They'll be in the toolbar at the top of a spreadsheet.

So notice how I've made the background colour of the cells grey and the font colour white, and that makes it clear that row one is product, quantity, and cost.

And they're my headings for my table.

Now when you enter data into a spreadsheet, you can choose the data format of each cell.

Now, the data format is the style and format of the data.

We've looked at what the style is, so let's take a closer look at the format of the data.

Let's look at these cells.

So in column C, we're looking at cell C2, 3, 4, 5, and 6, and they're highlighted now.

I formatted these cells to currency and that will automatically add the pound sign and add decimals, and that makes the numbers look neater.

So let's have a look before and after.

So if I didn't format these cells in column C to currency, it might not be completely clear what the amounts in column C represent.

We know it's the cost, but is 20 20-pence or is it 20-pounds? So before formatting, it looks like this.

Now take a look after.

What do you notice? Yeah, the spreadsheets added the pound sign and added a decimal place, so it's easy to represent what pounds and pence are, and it makes the data look neater too.

Now, there's not just currency you can format data as there are lots of different data formats you can choose and here are some that you might use on a spreadsheet.

So I've organised it into a table.

On the left-hand side, we've got the data formats, we've got an explanation of what each one is and an example.

So let's look at the first row here.

So plaintext is a data format for letters or words.

So for example, if you've typed trainers into a cell, you would select the format of that cell to be plaintext.

The second one number is a data format for numbers.

So whether it might be your shoe size, your height, or any other number, a score for a game perhaps, you can type in a number.

For example here, 10.

5.

Now often on spreadsheets, you might use percentages.

So if I want the data format to be a percent, I would select my data format to be percent.

For example, 50% here.

Currency, we've talked about already, and that's a data format for money when we need to represent pounds and pence.

I can also format the data as a date, and that's often on a spreadsheet with a day, a month, and a year.

So look at the example here of the 25th of December 2024.

That's Christmas Day, isn't it? So that's how the date is represented on a spreadsheet.

And I can also represent data as time.

Now look carefully on this one.

The data format for time is often hours, minutes, and seconds.

So in this example, it's 10:30 and one second.

We have to be really specific here with the time.

So let's take a look at how to actually format data, how to choose plaintext in this example for the format of the data.

So the first thing I need to do is select the cells I want to format, and I've used my mouse here to click and highlight this column.

And this column is my product.

And we've got trainers, jumpers, hats, shorts, and skirts.

And you can see, it's got that light blue colour, so it's been selected.

The next thing I do is go to the top of my spreadsheet into my toolbar and I click on Format.

Then from the list, I choose what I want the data format to be.

And here, I'm choosing plaintext, so the second one down.

Now on the spreadsheet programme you're using, it might be in a different order, but look out for plaintext.

So let's look now how to format for currency.

I've selected some cells here.

Now, I've only selected the cells with the numbers in them.

I haven't selected the word cost, so I only want the sales with the numbers 20, 12, 5, 8, and 8 written in it.

I haven't selected the cell that's got cost written in it.

Again, I go to the top of my spreadsheet to my toolbar and I click on Format.

Then I'm thinking, what do I want this data format to be? It's the cost of something, so I'm gonna select it as currency from my list.

Let's check your understanding now.

True or false.

Before formatting the data format of a cell, you need to select the cell you want to format.

Is that true or false? It's true.

Now, it might just be one cell or it might be multiple cells.

So I think this question to say, before choosing the data format of a cell, you need to select the cell or cells you want to format.

Let's go back to Izzy and Alex.

They've collected data in a spreadsheet on the meals chosen by Alex in the school canteen.

So Izzy and Alex have worked together at lunchtime.

Izzy has collected data about which meals Alex has chosen.

Izzy says, "My data shows the meal choices that Alex made at the school canteen this week." Alex has correctly said, "You can use the spreadsheet to keep the data tidy and we can decide on the data format of each cell." Let's have a look at that data.

So let's look at column A, that's got the dates in, we've got heading in A1 of date, and then we've got the dates going down the column.

So the first date, the 31st of January 2025.

It looks like we've got the next day, the 1st of February, the 2nd of February, the 3rd of February, and the 4th of February.

In column B, we've got the times.

So that's what time Alex ate his lunch.

In column C, we've got the meals he chose.

So it looks like on the first day, the 31st of January at 12:20, Alex ate some meat and fruit.

And then column D has the cost.

So that's the cost of the meal that Alex paid for.

Now here for the data that's got text written in it or plaintext, we've chosen the data format plaintext, and that is for all of row one, so where it is date, time, meal, and cost, that's plaintext.

And also all of the data in column C, so the meals, meat and fruits, vegetarian, vegetarian, meat and dessert, and vegan and dessert, they're all plaintext as the data format.

For column A or from A2 down to A6, the data format is the date.

So we've selected cells A2, A3, A4, A5, and A6, and then got up to my toolbar, clicked format and chosen date.

For cells B2 to B6, the data format I want to choose is the time.

And for cells D2 to D6, the cost of the school meals, I'm gonna choose the data format currency, like the example.

A really great question from Izzy here.

She's asked, "Do spreadsheets automatically format data?" So what she means here is when I type something into a cell or a group of cells, does the spreadsheet automatically recognise what format it might be in? Now, spreadsheets are clever.

They can help format data, but you still need to format the data in the cells correctly to make sure it would be displayed exactly how you want it to be.

The software is clever, but it doesn't always get it right.

Let's check your understanding with a true or false question for this statement.

Software can always work out the type of data in a cell, so you don't need to choose the format.

Is that true or false? It's false, but why is that? Remember, spreadsheet software can help, but you still need to choose the format of the cells to make sure it's correctly, the data in the sales is correctly displayed exactly the way you need it.

Software doesn't always get it right.

Time to put all of that knowledge about how to format data in spreadsheet cells into practise.

To do this, you need to download the formatting spreadsheet file for this lesson.

Using the second tab on the spreadsheet provided, I want you to format the data in the cells.

Or if you want to use your own data, you can use the first tab to fill in each cell with a date, time, meal, and cost.

Now when you're formatting the data, you need to remember to select the cell or multiple cells you want to format the data for.

Once you've selected the cell or cells, you need to then go to the toolbar at the top and select Format and then choose the format for the data in the cell or cells.

So here's an example of a correctly formatted spreadsheet.

In the first column, in the date, the data format is the date.

The second column for time is formatted in hours, minutes, and seconds.

The meals are selected as plaintext, and the cost is formatted as currency.

So yours hopefully will look similar to this.

We've come to the end of today's lesson so let's have a summary of what we've learned.

Remember, an individual item of data is recorded in a spreadsheet cell.

And cell references combine the column letter and the row number to identify each cell in a spreadsheet.

And remember, we always say the column letter first and then the row number.

So in this example, we're looking at cell A1.

And then to make it clear what is in a cell, you can choose different data formats for each cell.

And we looked at some that are formatted to do with date, time, currency, or even plaintext.

I hope you've enjoyed today's lesson.

See you soon.