Lesson video

Hi.

I'm Cache, your computer science teacher for the spreadsheets unit.

Welcome back to lesson four, where we're going to be using charts to represent data and also using macros to carry out repetitive tasks.

For this lesson, you're going to need a pen, some paper, and you're going to need to remove any distractions that are going to get in the way of you focusing.

Once you've done this, let's begin.

In this lesson, you will implement formatting to design a clear and readable chart.

Implement and test a macro to carry out a repetitive task.

Starter.

So in this age of information overload, the use of charts and infographics makes it a lot easier for users to understand data.

Time is precious and we want to make sure that any charts that we've got are from reputable sources, because any that are from less-than-reputable sources, they can be shared with people and it could spread misinformation.

We're going to start off by analysing some charts and I want you to have a think about what's wrong with each of the charts.

Starter.

What is wrong with this chart? Take a few moments to have a look at the chart, to analyse it and to have some thoughts on what you think might be wrong with it.

Okay.

Let's have a look at the solutions.

So on this chart, the bottom left-hand corner starts at 50, charts usually start at zero.

Now because it starts at 50, the jump between the first bar and the second bar is not as much, but because of the 50 start, it looks like it's quite a lot.

If you look on the right-hand side, when we do start the baseline at zero, the information is much more accurate.

What is wrong with this chart? Once again, take a few moments to have a think.

Okay.

Let's go over the solutions.

So looking at this chart, the Y axis, we're not really using it from 20 to 40, so we don't really need that in there.

So manipulating the Y axis.

Expanding or compressing the scale on the graph can make changes in the data seem more or less significant than they are.

Okay.

One last one.

What is wrong with this chart? Okay.

Let's go through some answers.

So here we've used the wrong chart, or we can see the wrong chart is being used.

Pie charts are usually out of a whole, so the percentages on the left-hand side, we see 64, 38, 50, they don't add up to a 100% percent.

On a pie chart, you know we're expecting it to add up to a 100% percent.

On the right-hand side, a bar chart is what we'd most likely use for that type of data.

Visualise voting data.

Using the RSC voting spreadsheet, create charts to represent the data, column chart of telephone votes and column chart of telephone and text votes.

Pause the video to complete your task.

Task 1 - Visualise Voting Data.

Using the worksheet and the spreadsheet, "RSC voting spreadsheet", complete Task 1.

Resume once you're finished.

Task 1 - Visualise voting data - solution.

So as you can see on the left-hand side, we've got the answers for the first chart.

And on the right-hand side, we've got the chart for the telephone and text votes per week.

Task 1 - Visualise voting data - solution.

So these were the charts that I produced.

As you can see, they've got the headings on there.

I've even coloured the headings in and the charts themselves, they're nicely laid out and they're on separate tabs at the bottom of that sheet.

Make a pie chart.

Using the RSC Live event seating spreadsheet, create a pie chart to represent the total seat sales.

Pause the video to complete your task.

Task 2 - Make a Pie Chart.

Using the worksheet and the spreadsheet, "RSC Live events seating", complete Task 2.

Resume once you're finished.

Make a pie chart - solution.

As you can see there, the pie chart's being created.

We've got it on a separate sheet.

The headings have been enlarged and also the main heading is in the centre and it's got a different colour on it as well.

Reset! All the seats have been sold.

Now we need to reset the spreadsheet, ready for the next show.

When you have tasks that need to be repeated, you can record a macro to automate those tasks.

A macro is an action or a set of actions that can run as many times as you want.

When you create a macro, you're recording your mouse clicks and keystrokes.

On the right-hand side, you can see the steps that are going to be involved in the next task.

So we've clicked on Tools at the top, we've clicked on macros and then we've clicked on record macros.

Once you do that, you that, every single click that you're taking your, your, every single clip that you're making is being recorded.

Pause the video to complete your task.

Make a pie solution.

So the pie chart you can see there is an example.

It's got its own tab at the bottom.

The headings are enlarged.

The main heading is in a different colour and has also been centred.

Reset! All the seats have been sold.

Now we need to reset the spreadsheet, ready for the next show.

When you have tasks that need to be repeated, you can use a macro to automate those tasks.

A macro is an action or a set of actions that you can run as many times as you want.

When you create a macro, you're recording your mouse clicks and keystrokes.

On the right-hand side, you can see the spreadsheet that we're using.

To get onto the macro option, you click on Tools, then macro, and then you click onto record macro.

Every step you take after that is then being logged.

Pause the video to complete your task.

Task 3 - Code a Macro.

Using the worksheet and the spreadsheet, "RSC Live events seating", complete Task 3.

Resume once you're finished.

Task 3 - Code a Macro - solution.

So as you can see here, we've got the spreadsheet loaded.

We're going to assign some seats.

And now as soon as I click onto this reset button, you'll be able to see that all the seats have been reset.

So the actions that I recorded, they've been saved and they've been assigned to the reset button.

Thank you for joining me on this lesson.

I hope you've got a better understanding of using macros and using graphs and charts when representing data.

You can share your work with us at Oak National using Instagram, Facebook, or Twitter, tagging @OakNational and #LearnwithOak.

Make sure you get a parent's or carer's permission before doing so.

Thank you.