Lesson video

Hello, I'm Mrs. Lashley, and I'm looking forward to guiding you through your learning today.

This lesson we're gonna look at constructing bar charts by utilising technology.

Here are some key words that hopefully you're familiar with from previous learning.

I would suggest that you pause the video and reread them to familiarise yourself with them again.

This lesson's got two parts to it.

The first part, we're going to create bar charts on spreadsheet software.

And the second part, we're gonna look at how we can make use of template files.

We're gonna make a start on the first part, which is to do with the spreadsheet software.

There are many different spreadsheet softwares available.

The main two are Microsoft Excel and Google Sheets.

Their functionality and appearance are fairly similar.

I'd like you to find out which software you have available and open up a new spreadsheet.

If you have access to Google Sheets, then it will look like this.

It will open up in your browser.

If you have access to Excel, then it will open up as a programme, although there is a browser version as well.

And you can see that they are very similar in terms of how they look.

Spreadsheet softwares has many functions and capabilities.

Many jobs, many careers, many business make use of the functionalities.

This lesson is focusing on chart creation and namely, bar charts.

One part of the data cycle is collecting and representing the data.

So you've got an inquiry, you've got a question that you want to investigate, and you collect the data and organise it and represent it.

So this idea of collection and representation can be done by hand, but often, especially in industry, the amount of data that is collected is huge.

And so, working by hand would just not be an efficient method.

And so, the use of software allows it to be sorted and displayed very efficiently.

So most careers that collect, present and analyse data will use technology.

We're gonna make start using the technology.

And so, data as a list can be copied and pasted into a spreadsheet.

So that data might have been sent to you from a colleague or maybe in a Word document or a Google Docs, and you need to then import it into the spreadsheet.

Laura just wants to remind you that you can use keyboard shortcuts.

So if you press the Control button and the C, it will copy.

And if you press Control and V, it will paste.

So they're shortcuts.

This saves typing each word separately, so if you've got a long list of data and remember that we are gonna use technology because the amount of data we have is vast, typing each piece of data would take a long time.

So copying and pasting it from somewhere else, an email or wherever, saves time.

But what other benefit is there than typing each word? The other benefit of this is data entry error is avoided.

So if you were typing many pieces of data, there is a possibility that you will mistype at some point.

And so, by copying and pasting it from its original source, then that is avoided.

So your first task is to copy this list into your spreadsheet file.

So it's a list of colours.

Once you've copied the list of data, you're gonna click on the cell where you want to paste it into.

I've selected A1 and right-clicking and pressing paste.

You could do that using Control+V on the keyboard shortcut instead, but there I've just right-clicked and pressed Paste.

Now, we have all of the data as the list all in the cell, A1.

The next step is to split that into a piece of data per cell.

We do this by going to the Data on the top ribbon and down there is a command called Split Text Column.

Having done this, it's now got one piece of data per cell.

So in A1, there's a piece of data, in B1, there's another piece of data.

Next up, we need to take this row of data and turn it into a column of data.

So to do this, you're going to highlight all of it.

So click and select it all and then right-click Copy or you could do Control and C.

Head back to a new cell, right-click and this time it's a paste special transposed.

Your data should now be running vertically.

So it's the same list, red, blue, silver, but it's now running vertically.

It's already highlighted, so we can just go insert chart.

A pie chart has come up as a suggested, but we can change that in the setup to column.

Under the chart editor, there is a Customise tab and we're gonna head there to add frequency as an access title.

So on the customise, we've got chart and access titles.

We can change the chart title, we can change the vertical axis title, type in frequency.

So now we've got frequency has appeared on the vertical axis title.

And that is your bar chart.

If you wanna get back to the editor, go to the three dots, press Edit Chart and then the Customise tab.

The horizontal axis is gone down as count.

You can change that to colours because our data is about colours.

Just to check, what did the function split text to columns do? It puts each word into its own cell.

It uses the fact that there was a comma after each word as a break in the list where it knows where to split it.

So another check, which direction does the list need to be for the spreadsheet to create a chart? Pause the video whilst you think about that and then when you're ready, press play.

So it needs to run vertically and that's why we did the pace transposed.

We went from the horizontal list to a vertical list.

So check on what we've just done.

Put these steps for inserting a chart from a list of data in order.

Pause the video whilst you sort those out and then come back to check.

So the first stage is to select all the data.

Then you're gonna go to the Insert tab on that top ribbon, click the option of chart, change the chart type if necessary.

So there's the order from one to four.

Parts two and three can be grouped together by using the shortcut chart button instead.

If data is not a list but instead has already been organised into a frequency table, then we can still make use of the technology.

So you would highlight and copy.

So once you've copied the frequency table, the data that you wish to make a chart for, you're gonna click on the cell where you wish to paste it.

You can do that using right-click and then paste or you could use Control+V, the shortcut on the keyboard.

Now the frequency table is into the spreadsheet.

We need to highlight all of that table because that's what we wish to make the chart about.

Once you've got it highlighted, go to Insert on the top ribbon, find the charts area and click the chart that you want to use.

So in this case, we want a bar chart.

You can see that there are many options that Excel give you.

There are stacked bar charts, there are 3D stacked bar charts.

We are gonna be using column despite the bar one being horizontal.

So we tend to call bar charts what Excel and Google sheets called a column bar chart.

So click onto the icon and it will auto-generate it for you.

Excel automatically puts a title to the chart.

If you want this, it's a text box, so you can then type on top of it like a normal text box.

Otherwise, you can click it and delete it.

If you changed your mind after deleting it, you can go on the plus and add it back in.

However, we want a primary vertical axis title.

That's a text box, rename it to frequency.

So now you've got your bar chart and it might be that you want to make this move to a different place and that's often useful to make it larger to then when you're doing the styling or the formatting, you can see it more clearly.

And you're gonna do this by selecting the chart and then move chart button will appear.

If you go to new sheet, call the sheet whichever you like, press OK and the chart will end up in a new sheet at the bottom of the spreadsheet software.

So another gym class is offered called step, step class, and 16 members attend it.

So we had data about gym classes and how many members were attending them.

So it does depend now on which software.

So we said that the two softwares, the two common softwares of Excel and Sheets are very similar and this is one of their differences.

So depending on the software, it depends on where you add a new row for the table and the chart to update.

So Excel will not update your previously-made chart if you add a new row at the bottom of your table.

So you can see that here, I've just typed step and my bar chart has stayed the same.

Whereas if you are working on Google Sheets, when you add a new row at the bottom, it automatically assumes you would like that as part of your bar chart.

And so, an extra bar just gets created.

The bars will appear in the order of the rows.

So if you wanted the bars in a specific order or they should be in a specific order, then you would need to have your frequency table set up to that order.

Going back to Excel, it will not automatically adjust the data and therefore the bar chart if you add it to the bottom, but if you insert it within the data, so within the frequency table, then it will automatically update.

Aisha said that she added a row to the bottom of her frequency table and the bar chart updated automatically.

So just a quick check, which software does this suggest that Aisha's using? Pause the video whilst you're thinking about that and then when you're ready to check your answer, press play.

So this would suggest that she's working on Google Sheets because Google Sheets will automatically update the bar chart if you add the row at the bottom of your frequency table.

Spreadsheet software does lots of things automatically and one of the things it does automatically, it detects what data you have and will choose an appropriate vertical scale.

If you were doing it by hand, you would have to go through those considerations yourself about what the vertical scale should be.

So although it does do it automatically, there is settings that you can change.

So here we've got four books and the number of pages per book as a bar chart.

We've got frequency as our vertical axis.

We put that label, put that title on.

Izzy suggests that this scale makes a lot of empty space and she's talking about at the top.

There is quite a lot of space above the tallest bar and maybe that can be changed.

So this is the data that created that bar chart.

And the auto scale was increments of 50.

So what scale could you use or would you use if you was doing this by hand? It might be that you decide that 20 or 25 would be a sensible scale.

I'm gonna show you on Google Sheets how we can change this auto scale.

So here we've got an auto-generated bar chart for the data table that you can see on the spreadsheet.

And what we're gonna look at here is the vertical axis scale.

The computer will decide on what they think is a sensible increment, sensible steps for it to go up in.

On this bar chart, we've got book three that has a frequency of 203.

Because of that 203 only just being above 200, there is a quite a large space at the top of the bar chart and it might be that you wish to remove that space by changing the vertical axes.

And so, we're gonna talk through how to do that now.

So you need to click onto the bar chart and get onto the editor by using the three dots.

We're gonna head to the Customise tab.

On the Customise tab, you go into the grid line and ticks.

Click on that and it'll drop down.

We want to change the vertical axis, so that's okay as it is and it's the step value that we are going to be inputting and changing.

The step value is what it is increasing in.

This is the major spacing, the major step.

So we've only got major grid lines showing up currently, and so it's those lines that you can see.

So 50, 100 and 150 is what the computer auto-generated.

If I change this to 25, you'll see that it now goes 25, 50, 75.

And we have reduced the size at the top of the bar chart because 203 to 225 has a smaller difference than 203 to 250.

If we change it to 20, then again it will scale or change again, 203 to 220 is even less.

So maybe that's a better scale in terms of reducing wasted space at the top of your bar chart.

I can change this to one and here you can see that now, because of the size of the bar chart, it's not actually able to show all of those numbers up to 203.

But it has got rid of all space above it because the top number is 203.

So one is not a sensible scale, but you can see that you can change it to anything that you wish to.

If we change it back to 50, the auto-generated, then that's what we get.

25 is probably a sensible one here and that's where I'm gonna leave it.

So just to check your understanding on that, if this was the setup for the bar chart's vertical axis, what would the numbers be? Pause the video whilst you're figuring that out and then press play to check them.

So we'd set it up to go up in 30, so it would be zero for the first number, then 30, then 60.

You are now gonna do a bit more practise using the spreadsheet software that you have available, so either Google sheets, Microsoft Excel, or any other that you've got available.

So task one needs you to create a bar chart from the list of data.

So you're gonna need to copy and paste it into your spreadsheet.

Make sure the data is usable for the software by having it vertical.

And then creating the chart, ensure those titles on both axes.

And then for part B, a bit of interpretation of the chart that you make, would you advise the garden centre to order an equal amount of each flower next time or different amounts? Explain your answer.

Pause the video and when you've done that, come back for question two.

Question two, I'd like you to create a bar chart for this table of data about the visitors to a museum.

Again, make sure you've worked with settings to have titles on both axes.

Pause the video and when you're ready, come back for question three.

Question three is the same data, but this time you've got an additional frequency column.

So there's data for week one and data for week two.

And there's a question of, what is different about this bar chart? Pause the video, use your software to create it and when you're ready, come back to check the answers.

You needed to copy and paste the list of data into your spreadsheet software.

It was really important that you would organise the data or split the data, split text to columns, and then made the data vertical by pasting transposed.

Once you had it set up, highlight it all, select it all and insert your chart.

You needed to have frequency on the vertical axis and flower or type of flower on the horizontal, the context of your data.

If you had played with any of the other features, any of the other settings and changed the scales or changed the colours, that doesn't matter, but your data should be represented as a bar chart with the two axes, frequency and flower.

For part B when you were trying to interpret, the question was, would you advise the garden centre to order an equal amount of each flower next time they do their ordering or different amounts? Your response may have included that the total amount of sales is not very high.

So although daffodil sales were the lowest on this particular day, it may not be the case on another day.

The garden centre should collect further data to get a better understanding of the sales.

So here, our data set was fairly small and by being fairly small, it might not actually be representative of the data or of the situation.

Question two, copy and paste in the full table, frequency table into your spreadsheet software and create a chart.

And question three, this one should have automatically recognised that it was a comparison bar chart because there was two sets of data that you was inputting.

So there was week one's frequency and week two's.

So the second part is creating bar charts using template files and we're gonna look at why they are useful.

Let's make a start.

So hopefully you'll agree that creating bar charts is a fairly simple task on spreadsheet software.

The formatting and design can take a little bit longer, so going through the Customise tab and deciding on colours and location, titles, the increments on the scale, whether you want major and minor grid lines, all those design choices can take you a little bit longer and some individuals might need to make multiple charts.

Can you think of any individuals that might need to do that? So maybe you've come up with a scientific researcher or somebody that's gonna present, make a presentation or a data analyst.

So there are many jobs that need to represent data.

And if your company has got a specific brand, colour, they might have specific fonts that they use, then when you're creating multiple charts, those formatting decisions, fonts, font sizes, colours, may just need to stay the same every single time.

So to be more efficient, you can create a template file to use over and over again.

And this template file can be set up with the colours that you need, the fonts that you need, the scale that you need, the size of the diagram, the location of the diagram, all of those things can be pre-made, preset, and then it's just a case of input in the data.

So I have made a template file, I'd like you to click the hyperlink and save a copy.

You can rename it if you wish or it will just save itself as a copy of template file.

So once you've got that template file open, I'm gonna show you what it looks like and what happens on it.

This template file is made so that you can just add your data underneath the context column and the frequency column and the bar chart will automatically appear.

So if you start to type some data, so here I've got some colours and some frequencies about numbers of favourite colours.

Then as I'm typing it, the bars are appearing.

The more rows, the more bars.

Here I've accidentally left a row, but it's still figured out that I want to include it into my bar chart.

If I write within that row, then a bar will appear between purple and black.

On the template file, the frequency label is already there, but the horizontal axis title says change me because that will depend on the context.

So we'll need to go into the editor and then the chart and access title and change the title text from change me to the appropriate title for the data.

So in this case, it's going to be colours.

Now you can change the colour of the text, whether it's bold or italic, the size.

If you change your mind, you can change it again.

The context column on that template file is where you will type the different options, your different categories, and that will just be dependent on what the data is about.

The frequency is where you're gonna input the number for each of those categories.

Give it a try for yourself on your copy of the template file.

You can use your own data, you can just make some data up for the time being, but hopefully you can see that as you input the data, the bar chart will automatically appear.

So check, fill the blanks, what's missing in this sentence? Pause the video and then when you're ready to check it, just press play and head back.

Template file is a useful tool when creating many charts where the style and formatting is to stay the same.

Up till this point of the lesson, we've been using spreadsheet software, but there is other technology that we can utilise and even if it's not specifically being created as a spreadsheet software, and one of these as an example is Desmos.

So I'd like you to click on the Desmos template file.

It's a hyperlink on the slide.

And once again, I'll run through what to do.

So this template file on Desmos allows us to create bar charts.

There is a specific way that we need to input our data.

So you can see the X1 Y1 table and the Y1 values are the frequency.

So if I change them all to one, hopefully you can see that all of the bars are shrinking to have a height of one.

By changing them to other values, so these would be the frequency of the data, then the bars will grow to their correct heights.

The template file is set up with just five bars, but if you need further bars, you've got more data, then you must put a new X1 value and its frequency and you can do that as many times as you need to.

Those new bars do not have labels on their horizontal axis.

So to do this, we need to go to the settings, which is the cog, and copy or duplicate the last coordinate.

You'll need to duplicate as many times as you have new bars.

Because it's a duplication, you need to now change the value within the square brackets and the label.

And hopefully, you can see that's appeared underneath, that we've now got bar six and bar seven.

So what values of that Y1 column were used to create this bar chart? Pause the video whilst you're reading the bar chart and figuring out the values, then press play.

So because the first bar has a height of eight and the second bar has a height of five, these are the frequencies from a bar chart, remember.

So it's eight, five, nine, seven and three.

So let's just check.

On the Desmos template file, which icon duplicates? Pause the video whilst you are thinking about that and then press play to find the answer.

C, that is the duplication.

The button that A shows is your settings button that opens up to find the duplication and B, we haven't used, but that button adds new items to the Desmos file.

So you're gonna practise using the two template files.

So question one, using the template file which is on Google Sheets, recreate this bar chart showing the number of moons per planet in our solar system.

Just to note though, you do not need to add the numbers to the bars.

They are just there so that you have the data to input.

Pause the video and when you've finished recreating that bar chart, come back.

And question two, click the hyperlink that will take you to the template file and you need to recreate this bar chart showing the number of time zones per country, including overseas territories.

Pause the video and come back to check your answers.

So for question one, the data that you would've needed to add is the names of the planets in order that have been given there and then the frequencies that match each one.

Question two, on the Desmos template file, to recreate this bar chart, you needed to add some more bars, which did need you to copy some coordinates and relabel all of them.

So we didn't want them to say bar one, bar two, bar three, we wanted them to have the countries, so Australia, Brazil, Canada, Denmark, UK, USA.

So hopefully you can check here if you didn't quite manage it what your table should have looked like.

So that had the frequencies in the Y1 and we didn't need additional bars.

And then, in your settings, you needed to copy a coordinate, relabel them.

So to summarise this lesson, we've looked at constructing bar charts by utilising technology.

Many careers have a need to represent data and often multiple times.

Technology is an efficient and useful tool to create the bar charts and that's why they will use them.

Template files can be made and set up to assist with the creation of multiple bar charts that all have the same format and layout.

Again, it just is more efficient.

Well done today and I look forward to working with you again in the future.