NOTE: These videos were prepared when the Census at School Project was managed by Statistics Canada. Most of the information is still relevant.
Duration: 8:04 min.
Description:
This episode will cover creating basic graphs like bar graphs and circle graphs in Microsoft Excel using a frequency table.
To access the Rich-Text Format (RTF) version, use the document conversion features available in most word processing software, or use a file viewer capable of reading RTF.
Download Transcript:
Video Transcript
Hi, I’m Angela McCanny. This episode will cover creating bar graphs and circle graphs in Microsoft Excel using a frequency table. There are a number of other spreadsheet programs similar to Excel such as Open Office Calc, Star Office Calc, QuattroPro and Appleworks Spreadsheet. The steps shown here for using Excel will be very similar for these other programs. Similarly, the various versions of Microsoft Excel are largely the same but you may find slight differences from the version that you are using to the version I’m using here, which is Excel 2002. Experiment a little and I’m sure you will be able to find the right location and sequence of commands for your program. It’s not too difficult.
You can find some Census at School lessons that ask students to create bar and circle graphs in the Learning activities section of the Census at School website. Under Grades 4 to 8, you can try You are What You Eat that has students create bar graphs from the breakfast data and compare whether different groups of students eat different types of breakfasts. Or, try the lesson Circle and Bar Graphs to have students compare the effectiveness of each type of graph to display the same data. The example in the lesson uses the pet data and the data about which method students use to travel to school, but we are going to do the lesson using the eye colour data.
Open the class dataset which you have previously imported and saved in Excel. To create a graph, we will first need to create a frequency table on a fresh worksheet.
So, click Sheet 2 at the bottom of the spreadsheet, and a blank worksheet will open. Or, if there are no extra tabs at the bottom, go to the Insert menu and select Worksheet. Since this first graph is going to be about the eye colour data, let’s rename this tab Eye Colour.
To start creating the frequency table, type Eye Colour in cell A1. Underneath that, type the four colour choices from the Census at School survey: blue, brown, green and other.
Now, in column B, we are going to fill in the number of students who have each eye colour. Type Number of students in cell B1. You will notice that the words are too wide for column B and are overlapping into column C. To make column B wider, bring the cursor arrow over to the line between the B and C in the grey row of letters, and when the arrow turns into a double-sided arrow, double click on the line.
To find the number of students who have each eye colour, we need to return to the class dataset. Click on the Class dataset tab at the bottom of the page, and find the column containing the eye colour information. Ah, there it is: it’s column I. The students can either count up the totals by going down the column and first counting all the blues (1, 2, 3 and so on), and then the browns, and so on for green and other.
Or, they can use the Sort command so that all the rows containing each colour will be grouped together, making it easier to count. To use the Sort command, first click in the blank box in the upper left corner of the spreadsheet to select the entire dataset. This is very important. If you try to sort without selecting the entire dataset, it will sort just the eye colour column, leaving all the other columns the same and so each student will have the wrong eye colour. So, to sort, click the blank box, then go to the Data menu and click Sort. In the Sort by box, click on the arrow and select Eyes. Then click OK. Now all the blues are together, the browns are together, and so forth, and it’s easier to count. Count up the number of each colour and enter those numbers in column B on Sheet 2.
We have now completed the frequency table and are ready to make a graph.
To make a graph, we first have to tell the program which data we want the graph to be about. So, select the entire frequency table using the mouse. Click in cell A1 and keep the mouse button pressed down while you drag all the way to the bottom right corner, cell B5. Unclick the mouse. Please avoid selecting an entire row or an entire column, as the program will not graph the data properly this way. Make sure just the cells containing data are highlighted. You will also notice that the cell you first click in will remain un-highlighted. Don’t worry: that’s normal.
Now Click on the Insert menu at the top of the page and select Chart. The Chart Wizard dialog box will appear.
Step 1 is choosing a graph type. We are going to do a vertical bar graph, which Excel calls a “column” graph. The eye colour data would also work well as a pie chart or circle graph. Step 1 also gives us the option of choosing a graph sub-type, like a stacked bar graph. However, for this graph I will stick with the standard vertical bar graph. Click Next.
Step 2: no change is required here; click Next.
Step 3 is for inserting the title and axes labels. Type the title: let’s try Eye Colour in Ms. McCanny’s Grade 6 Class. Then type the label for the x-axis (that’s the horizontal axis) and we can see that this is where the eye colour labels are. So we’ll call this Eye Colour. The y-axis (or the vertical axis), counts the number of students who have each eye colour, so we’ll type Number of students. Click Next.
Step 4 requires no change, so click Finish.
And now we have a bar graph about eye colour. It’s a good graph; it has titles, labels, and it’s easy to see the most and least common eye colours. If you wish, there are options to vary the appearance of the graph, such as changing the font or type size, or in this case, you may want to add colours to the bars to make the graph a little clearer, particularly since this graph is about colour.
We can leave the first bar blue since it’s for blue eyes, but let’s work on changing the second bar brown to match its eye colour. First click on the second bar. Notice that all the bars have a little square in them: they have all been selected. If you change the colour now, all the bars will change colour. So, to select just this bar, click one more time. Now, right click and select Format Data Point.
Select the colour—in this case brown— or, if you are printing in black and white and would still like to make a distinction between the various bars, click Fill Effects, then Patterns, and choose a pattern that will stand out distinctly. Your students will want to experiment.
Now go ahead and change the colours of the other bars. Notice that when you have changed the colours, a legend will appear to match.
We have now completed the bar graph. Take a moment to save the graph, using the File menu and the Save command.
To create the circle graph from the same data, again select the entire frequency table and go to the Insert menu, click Chart and this time select the circle graph. Choose a sub-type if desired, then fill in the title and click Finish. Again, you can change the colours of the circle segments or change the font or type size. Move the graph to the correct spot on the worksheet.
Complete this lesson by asking the students if both graphs give a good picture of the data and what kind of information is easier to read from each graph. Ideas for reflection questions are listed at the bottom of the Circle and Bar Graphs lesson.
Once the students have these skills, they can create double bar graphs for comparing data for more than one group. You can print these graphs if you wish for easier marking.
Enjoy your data exploration.
