Hi, my name's Mr. Hall and welcome to this Oak National Academy lesson.

This lesson is called "Formatting in a spreadsheet model" and it's taken from the unit "Spreadsheets." Thank you for joining me for this lesson and I really look forward to learning with you today.

The outcome of this lesson is that you'll be able to format a spreadsheet model to improve readability and usability.

We've got four keywords in this lesson, so the first keyword is formatting.

Formatting means modifying the appearance of data in a spreadsheet.

We've then got wrap.

So wrap means to automatically break long text within a cell onto multiple lines.

Next, we have merge.

So merge is to combine two or more adjacent cells into one larger cell.

And finally we've got conditional formatting.

So conditional formatting is to apply a style or format of a cell based on whether a cell matches a certain criteria.

So there's our four keywords for this lesson, formatting, wrap, merge, and conditional formatting.

We've got two learning cycles in this lesson today.

The first is that you'll apply formatting to a range of cells, and then later on we'll move on to highlight information using conditional formatting.

So let's get started with that first learning cycle.

So here's two examples of information presented.

Which layout do you think is easier to understand? And can you explain why? So the spreadsheet is formatted to be easier to understand.

The data's organised into rows and columns as a structured layout and is easy to read.

Totals are calculated, which helps you quickly to see overall trends in the data.

And it's easier to compare each location's weather.

Exactly the same information was presented in both formats.

It's just that with the spreadsheet being formatted, it's much easier to understand the data.

In a spreadsheet, you can organise and format data to make it clear what's in each cell, so Andeep says, "Each cell in a spreadsheet can have a data format, which shows the type of data the cell holds." And Lucas adds, "Every cell can be formatted so that it's clear what's in each one.

It often looks neater too." And we'll see some examples of that.

You can change the font style, font colour, or background colour of a cell.

Formatting changes make the data easier to read, so let's have a look at an example.

Now row 1 has a different background colour and font colour so the table heading is clearer.

You can see those icons there, the one on the left is the font colour and the one on the right is the background colour icon.

So time for a question.

Which icon changes a cell's background colour? Is it A, B, or C? That's right, it's C, so it's the kind of paint pot which has a bit of paint spilling out of it, and that's the kind of icon you've probably seen in other applications where you can do fill colour, so the correct answer is C.

One type of formatting is changing the data format of the cell to change the style and format of the data.

Formatting these cells to currency will automatically add the pound sign, add decimals, and make the numbers look neater.

So it's really easy now that they've been formatted to see that those are amounts of money.

Other types of number format include number, date, time, and percentage.

Here, Column B is formatted as time.

And on the right-hand side you can see the format menu from a typical spreadsheet application, so you can see there's a number of different formats you can choose for each cell.

So there you see Column B formatted as time, so that's the time option has been chosen to format that column.

So Lucas has noticed that sometimes his data or text spills over into nearby cells, so we've got an example there of text which is in a cell but it's too long for the cell it's in so it's spilling over to the cells next to it.

Text wrapping is a formatting option.

It changes how your data is laid out within a cell.

So this is the text wrapping tool, that's what the icon looks like.

And there's different ways you can do text wrapping, so overflow is the default setting.

When the data is entered, it will automatically spill over across empty cells.

So if you don't do anything in your cell, that's what is likely to happen to the text, it will spill over into the cells next to it.

So that one's overflow.

Wrap keeps the text inside the cell by making it appear on a new line.

So to do that, it will change the height of the row so that the text can fit in, so you can see that Row 2 in that example is much taller than Row 1 or Row 3, so much more height to that cell.

So that's text wrapping and the icon for text wrapping is the one in the middle there, so it's kind of like the arrow going back on itself.

There, text wrapping icon.

When you wrap text, the row does not always expand to show you the whole content, so sometimes you might see something like this where you can see the first line of text and then just a little bit of the next line below, so it doesn't always adjust the height of the cell.

To fix that, you can drag the border between the two row numbers when you get the dull-headed arrow.

Hover your mouse over the line between the row numbers until the cursor changes to that double arrow, then click and drag the line to adjust the height.

And the last one we're gonna look at here is clip, so clip is for keeping the contents of the cell within the borders of the cell and anything that would've overflowed before will now not be visible, so it's hidden.

It's still in the cell, so it's still there but you can't see it anymore, and you can see in the example that the bar at the top where the data has been typed in has the whole contents of that cell even though you can't see all of it in the cell, so that's clip.

And you can see the icon there for clip on the right-hand side of those three is that one there.

So that one's clip.

So Sofia says, "Sometimes I want a title to go across multiple columns underneath." So there's another way you can do this.

You can change how cells interact by merging them.

If you want to centre a title or a heading across several columns, use merge cells to join them into one big cell, and we'll have a look at how you can do that.

So in this example, the "Rockstar Challenge" heading is in cell G2.

The text is spilling over into cells H2 and I2 and we could use merge to make this look neater.

So this is the icon for merge.

So it's kind of like two square brackets with arrows pointing at each other.

So here's how we do it.

Cells G2, H2, and I2 have been merged in this example.

The formatting for that cell will apply across all of the cells and they will be referred to as a range.

In this case, the range is cells G2 through to I2, and that's been done using the merge cells tool.

So there's our range, G2 through to I2.

Just be careful.

In some spreadsheet software, the merged cell may only be referred to as the first cell, so in this example it would just say cell G2 even though you've merged the other cells as well.

You can also add borders to cells to make the cells stand out or show the table more clearly.

So in this example, we have the "Rockstar Challenge" cell which has been merged across multiple cells in this spreadsheet, and it's surrounded by a black border.

So there we are, the title now has a thick black outer border.

You can change the thickness and colour of the borders by using the tools which are shown in the image on the left, so that is the outline tool.

Okay, time for a quick check.

So this is a fill in the blanks check, so I'll read out the sentences with the blanks and then you fill them in for me.

So when you want to create a heading that goes across several blank, you can use blank to join them into one large cell.

If your text is too long and spills into the next cell, you can use blank to keep all the text inside one blank.

Okay, let's fill in the gaps.

When you want to create a heading that goes across several columns, you can use merge cells to join them into one large cell.

If your text is too long and spills into the next cell, you can use wrap text to keep all the text inside one cell.

Now open the spreadsheet file "Rockstar Challenge results." When you open it, you'll see that this spreadsheet is unformatted.

Follow the instructions to add formatting to make the data easier to read, and I'm gonna take you through those instructions now.

So first, merge cells A1 to E1 for the title, which is "Rockstar Challenge." Centre the text and make it bold.

Then wrap the text in the header, so that's row 4, so all the headings are fully visible.

And then third, find any cells that should be data formatted and look for numbers, dates, and times.

Four, in the Total Votes column, column F, write a formula to add Studio Votes and Home Votes.

Use the fill handle to copy the formula down for all contestants.

And then five, style the spreadsheet.

So adjust the font size and the style to make the table easy to read.

Add borders around the table to separate the data clearly.

Okay, so you can open the spreadsheet "Rockstar Challenge results solution" to see the finished version, and your spreadsheet should look something like this.

So we've got the title merged across the top row there.

All the titles in row 4 are clearly visible with wrapped text, so they go across two lines now.

And you can see that there's a number of different formats included in this example.

So first one is we've got a date format at the top, and then in column C we've got a time format, and columns D, E, and F are in number format.

Now we can move on to the second learning cycle, which is to highlight information using conditional formatting acting.

So far, you've formatted cells in a spreadsheet by using the menu tools like wrap, background fill, and merge.

But you had to apply the formatting to each cell yourself.

Conditional formatting is a way to change how a cell looks based on its value.

A condition is used for this.

A conditional format can format cells for you as long as something in the cell meets a condition.

A condition is something that can be either true or false.

Instead of applying the formatting yourself, the spreadsheet does it automatically when the contents of the cell meet the condition, so whether they are true or false.

In this Rockstar Challenge results table, you want to highlight the contestants who've got more than 160 studio votes, so let's see how we can do that with conditional formatting.

Conditional formatting can be set up so that once the sheet detects any value greater than 160, it changes the colour of the cell.

So let's do a quick check.

What does conditional formatting do? Does it, A, calculate totals; B, change the cell formatting based on a rule; or C, add up votes automatically? That's correct, it's B, it changes the cell formatting based on a rule, so that's what conditional formatting does.

To set up conditional formatting in a spreadsheet, first you need to select the cells you want to format, so here we've got cells B2 through to B9 selected.

Then you click on the Format menu and choose Conditional Formatting.

Once you're in the Conditional Formatting menu, check the range you have selected here is correct.

So it was B2 to B9, which is what we wanted, so that's correct.

The Format Rules section is how you set the condition.

There are lots of conditions to choose from in this menu.

There are conditions based on number values, text values, and more.

So the one we want to use in the Format Cells If menu is select Greater Than, so we want to know any value greater than 160.

So in the value box we enter 160, so now you can see surrounded in the green boxes we've got Format cells if greater than 160, which is the rule we want.

Then under the Formatting Style you can set how you want the cells to be formatted.

So this time we've selected we want the cells to be filled in blue.

When you've done everything, press Done.

So the conditional formatting rule was if the total studio votes are greater than 160, colour the cell blue.

So let's have a look what that would do to that spreadsheet.

So now we can see the totals in rows 4, 5, 7, and 8, all over 160, are now colour blue.

The rest remain unchanged, including row 9, which is exactly 160 and that's stayed unchanged because it's not greater than 160.

Time for a knowledge check.

Match the word or phrase to the definition.

The three words we've got are condition, format, and rule.

And the three definitions we have are the style of a cell, such as colour or bold text; an instruction that the spreadsheet can base actions on; and something that must be true to trigger an action.

So match the word or phrase to the definition.

Okay, let's have a look at those links.

So a condition is something that must be true to trigger an action.

Format is the style of a cell, such as colour or bold text.

And rule is an instruction that the spreadsheet can base actions on.

So now open the spreadsheet file "RSC seat allocation." Format the seating chart using conditional formatting.

Each category should have a different colour.

So A, an empty seat.

B, a seat booked by an adult.

C, a seat booked by a student.

And D, a seat booked by a person over 60.

The event organisers want to know when they're close to selling out tickets.

Apply conditional formatting to the cell range F13 to F15 to colour the cell in red if there are less than 5 tickets remaining.

Finally, explain how conditional formatting can be used to automatically change the appearance of a cell.

Open the spreadsheet "RSC seat allocation solution" to see a completed version.

So you can see the Seating Discount & Codes are in the table on the left-hand side.

So Adult, the code is A in green; Student, S, with the background colour blue; and Over 60s is O with the background colour yellow.

And in the stage diagram, the stage and seating diagram, you can see the corresponding colours for each type of seat, so there is an adult, a student, or an over 60.

So the fourth question was explain how conditional formatting can be used to automatically change the appearance of a cell, and your answer should be something like this.

So conditional formatting changes how a cell looks based on what's inside it.

For example, it can make a cell turn red if the number is too low.

This helps you notice important information quickly without changing the data.

So here's a summary of today's lesson, "Formatting in a spreadsheet model." Formatting improves clarity, usability, and presentation of data in a spreadsheet.

Merge and wrap can help to present data and organise text in a cell effectively, and conditional formatting highlights key data points automatically based on set conditions.

Thank you for joining me for this Oak National Academy lesson today and I look forward to seeing you again soon.