Hello and welcome to this Oak Academy lesson, Visualising Data Using Charts in a Spreadsheet, and it's taken from the unit, Spreadsheets.

I'm delighted you've joined me for this lesson and look forward to learning with you today.

So let's get started.

The outcome of today's lesson is, I can create and customise charts to present data effectively.

We've got two keywords in this lesson.

The first keyword is chart.

Chart is a visual way to show data.

And the second keyword is data.

And data is facts and figures in their raw form.

So there's our two keywords, chart and data.

This lesson has two learning cycles.

So the first learning cycle that we will start shortly is to identify data and use different types of chart.

And then the second learning cycle is to create the most suitable chart to visualise data.

Okay, let's get started with that first learning cycle.

So first of all, a question for you to consider.

What is a chart? So Lucas has an answer, "A chart is a visual way to show data." So we've got an example of a chart on this slide.

We'll find out a little bit more about that as we go through.

There's no title on this chart at the moment so we're not entirely clear about what the data is about, but we can see that there's some data for friends, gaming, et cetera, on the bottom and some numbers on the left-hand side.

So we'll find out a little bit more about what that's about as we go through the lesson.

So some examples of where you see charts in everyday places.

So Sophia identifies, "The weather app that I like uses charts, like showing the temperature through the week." And Sam says, "I see charts when I check the football scores.

There's one tracking goals per player." And then Andeep adds, "My school uses charts to show attendance, and on my reports too." So three examples of where you might see charts in everyday places.

I'm sure you can think of more yourself.

So instead of looking at numbers in a table, a chart helps you to do a few things.

Let's have a look.

So first of all, a chart can help you see patterns.

So from this you can really easily pick up that the largest bar is the friends and the smallest is for reading.

Again, we don't quite know what this means yet 'cause there's no title or context for this chart, but we can start to pick up a few things already.

We can compare values.

So we can see most and least very easily from this chart.

And we can spot trends.

And the way they do this is charts turn data into shapes and colours that are easier to understand than just a bunch of numbers on a spreadsheet or a bunch of numbers in a table.

This is why charts are referred to sometimes as data visualisation.

So it visualises the data and makes it easier for you to understand.

So the first chart we've looked at is in fact a bar chart.

Bar charts are really useful when comparing values.

So an example could be like the number of sales per product.

And again, I'm sure you can think of examples yourself where bar charts could be useful.

This chart is a line chart.

So this time we do have a title on this chart.

So we have Number of Home Viewers, thousands, versus Time in minutes, and we can see that the values are changing over time.

So after no minutes, we've got a value of 550 and then 580, 610, et cetera.

So line charts are best used to show changes or trends over time, like sales each year.

So we can see how the trend is going over the period of time for this line chart.

The next one we'll look at is a pie chart.

Pie charts are used to show how data is split into parts of a whole, like a percentage of total points scored.

So let's do a quick knowledge check.

Match the chart type to what it's best for.

So we've got three chart types, pie, line, and bar, and then three descriptions.

So the first description, showing change over time, then comparing amounts in categories, and then showing parts of a whole.

Okay, let's match those up.

So a pie chart is useful for showing parts of a whole.

A line chart or line graph is for showing changes over time.

And a bar chart is for comparing amounts in categories.

Sometimes though, do you think charts can be misleading? So in this example, imagine a sportswear company wants to show that their new trainers are much more popular than their old ones.

And we've got a bar chart here, and we've got a short column for the old model and a much taller column for the new model.

So looking at it quickly, looks like the new model doing much better than the old model.

So this is the chart they made.

But, is there a problem with this chart? Well done if you spotted that the Y-axis, so that Sales in Thousands, actually starts at 47.

So as I've said, the Y-axis starts at 47 and not zero.

So this makes the bars look very different, even though in reality the change is actually quite small.

The chart makes it look like sales have increased dramatically, even though the difference is actually 2,000 units and at that volume of sales that's not a huge difference.

By starting the Y-axis at zero, this chart has a misleading baseline.

So let's have a look how it could be more realistically presented.

So this time fixing the axis so the chart starts at zero, you can see a more honest view of the sales data.

So now we can see actually that the difference between 48,000 unit sales and 50,000 unit sales actually isn't that significant.

Yes, the new model is still outselling the old model, but nothing like the increase that the first chart indicated.

So there we see, it's a relatively small gap between the old model and the new model.

So there's the real difference and it's a small amount.

Time for another question.

Which of these are good reasons to use a chart? Is it A, to make your data look colourful, B, to spot patterns in the data, C, to compare amounts easily, or D, to make typing easier? So choose all that you think are correct.

Which of these are good reasons to use a chart? Well done, the correct answers are B and C.

So good reasons to use a chart are to spot patterns in the data and to compare amounts easily.

The type of chart you use depends on the type of data you want to visualise.

Using the wrong type of chart can skew the data.

So in this example, a pie chart is being used to compare the difference between groups.

This is inaccurate as pie charts are used to compare the parts of a whole.

Watch out for signs of misleading charts, and here's a few things you should look for.

So first one is cherry-picking.

That's only showing parts of the data.

So that can skew what's being shown by only showing the bit that maybe you want people to see and not the whole picture.

So the second one we've seen already, so a missing baseline.

That's where the Y-axis doesn't start at zero.

And as you've seen, that can skew the difference between two values on a bar chart.

And then third, an exaggerated scale.

So that makes small changes look bigger than they are.

And Andeep says, "A good chart shows the whole picture.

A misleading chart tells a story that isn't fair." So use this chart to answer the questions.

This is the chart we started off with at the beginning of this lesson.

So now we've got a title, which is Favourite After-School Activities.

We know it's a bar graph, so we've got a little bit more context now so hopefully that will help you answer these questions.

So the first question is what type of chart is it? Second question, what kind of data does it show? And what question can it help you answer? So I'll pause for a while while you think about those questions.

So, what type of chart is it? Well done if you identified that it's a bar chart.

So it's like the chart we looked at at the beginning of this lesson.

What kind of data does it show? Well, it shows categories and those are different activities.

So the activities of gaming, sports, reading, watching videos or friends.

And number, and in this case the number represents a number of students.

And this bar chart visually compares quantities of different categories.

And the question it can help you answer is which after-school activity is the most popular? So now we'll have a look at a different type of chart.

So you use this chart to answer the questions, what type of chart is it? What kind of data does it show? And what question can it help you answer? Again, I'll pause for a few seconds while you consider those questions.

So this type of chart is a line chart.

And the data it shows is data that changes over time.

So the day is plotted on the horizontal axis.

So you've got Monday through to Friday on the horizontal axis.

And the number of students is on the vertical axis.

And the question it can help you answer, on which day was gaming participation highest or lowest? And you can really pick out quickly from that line chart that Friday, it was the highest, and Monday, it was the lowest.

Now we'll have a look at another type of chart.

And again, we've got three questions and I'll pause after the questions for you to have a chance to reflect.

So first question, what type of chart is it? Second question, what kind of data does it show? And third, what question can it help you answer? So this is a pie chart and the kind of data it shows is categorical data.

So this is different online activities and the percentage of each category relative to the whole.

So the total online time.

So the whole is all the time that someone spends online, and then each section is the activities they do split into percentages.

So the question it can help you answer is what percentage of online time do students spent on different activities? And you can quickly identify from this chart that the most popular online activity is social media and the least is other.

Now we can move on to the second learning cycle in this lesson, and in the second learning cycle you'll create the most suitable chart to visualise data.

So let's now look at how you can create a chart in a spreadsheet.

First of all, select the data you want to visualise.

So in this example, the data from cells A1 through to B4 have been selected.

You then click on the Insert menu.

And then as you look down the Insert menu, you choose Chart from the list.

The spreadsheet software you use may suggest a chart type for you based on what it interprets from the data and the headers you've selected.

Now Andeep's got some good advice here, which you don't always assume that the suggestions will be correct.

Sometimes it'll suggest types of chart which aren't suitable so you need to use your judgement.

And Lucas says, "Think carefully about the best way to show your data." So sometimes you might wanna try more than one chart until you get the way that you're most happy with.

So now, in the chart editor sidebar, which you'll be in if you follow the steps on the previous slide, click on the Chart Type menu.

So this one has Pie Chart selected here.

And then when you click on the dropdown arrow, you can scroll down to find more options.

So we've got suggested as Pie Chart, but you can see there's also various other types of bar chart in that screenshot.

And then finally, you can explore Customise to change colours, labels, and titles of your chart.

So you can change how it looks.

So you find a look that suits you.

Okay, have a go at filling in the blanks in this sentence.

So to insert a chart in a spreadsheet, blank, after selecting your data, you should go to the, blank, menu and choose, blank.

And the three words you've got to insert are chart, insert, and software.

So let's complete that now.

To insert a chart in spreadsheet software, after selecting your data, you should go to the insert menu and choose chart.

So during the live broadcast of the Rockstar Challenge semi-finals, the number of home viewers varied.

And in this table we can see how the viewer count changed over the first 15 minutes.

So we can see that at the beginning, so zero in minutes, there were 550,000 viewers.

And at the end, so at minute 15, there was 615,000 viewers.

And we've got various values in between.

Which kind of chart would show this data effectively? Well done, a line chart would effectively show this data.

So on the X-axis, we've got the time in minutes, so five, 10, 15 starting at zero.

And then the Y-axis, we've got the number of viewers in hundreds or thousands.

So zero, 200, 400, 600, 800.

And the line shows the trend over time.

So we can see broadly over time the number of viewers was increasing.

So starting from its lowest level, which is 550,000, it peaked between 10 and 15 minutes at 630,000 but it fell slightly again.

But we can see that the overall trend was that the number of views was increasing through the 15 minutes.

After the Rockstar Challenge semi-finals, they analysed where the votes came from using three different regions.

Which chart type is the best way to show the proportion of home viewers coming from each region? So we've got the region and the percentage of viewer votes in the table.

So in Metro City it was 55%, Coastal County, 30%, and Inland Isles, 15%.

So which chart type will be best to show the proportion of home viewers coming from each region? Well done, pie charts are a great way for showing how different categories contribute to a total.

So the total viewership was a 100% and this pie chart shows how it was divided between the three regions.

Each slice of the pie represents a region and the size of the slice corresponds to the percentage of the vote from that region.

So very quickly you can see that Metro City had the biggest viewer share by quite a large margin, and Inland Isles had the smallest.

Okay, time for a question.

A pie chart is most useful for showing.

A, trends in data over time, B, comparisons between different categories, C, how different parts contribute to a whole, or D, the relationship between two different sets of numbers.

Well done, the correct answer is C, a pie chart is most useful for showing how different parts contribute to a whole.

So back to the Rockstar Challenge.

In the semi-finals there are four contestants.

Here are the votes that viewers cast for their favourite acts.

So we have the contestant, the song, and the number of votes.

Which type of chart do you think will be most effective to show which contestant received the most votes, and can you explain why? For this example, a bar chart would effectively show the data.

The height of each bar clearly shows the total number of votes, making it easily to compare them visually.

So you can see just by looking at the height of the columns, Jay Riff got most votes and Indigo Reign got the fewest votes.

And you don't need to actually look at the individual numbers to see that.

You can see it really clearly and visually from the height of the bars.

So now time for Task B.

Open the spreadsheet file, RSC charts.

So once you're in the file, open tab Task B1.

And then, how did the viewer count change over the first 15 minutes? Create a chart that will show the change over time.

So here's what your chart should look like.

A line chart will be the most effective way of visualising this data.

And you can see that there's a general increase in viewers over time as each point of the chart is linked by the line, and the general direction of the line is upwards.

Now, open Task B2.

Which proportion of home viewer votes came from each region? And for this one, create a chart that will show each region as part of a whole.

So the clue here is each region as part of a whole.

So that indicates that a pie chart is the best way of showing this data.

So we can see that if we look at the three percentages, they add up overall to a 100%.

So the total number of viewer votes.

Now open Task B3.

So the question for this task is, which contestant received the most total votes? Create a chart that will show comparisons between the different amount of votes each contestant received.

And for this one, a bar chart is the best way of visualising the data.

So you can clearly see, Jay Riff received the most amount of votes.

If you want to see these solutions in the spreadsheet, open the file, RSC charts solution, to see the working solution.

Here's a summary of this lesson.

Charts are used to show data in a visual format, making it easier to compare values, identify patterns, or understand trends.

Different types of charts are suitable for different purposes, such as bar charts for comparisons, pie charts for showing parts of a whole, and line charts for changes over time.

Charts can be created and formatted in a spreadsheet to present information clearly and effectively.

Thank you for joining me for this Oak National Academy lesson.

I hope you enjoyed learning along with me today and I look forward to seeing you again soon.