NOTE: These videos were prepared when the Census at School Project was managed by Statistics Canada. Most of the information is still relevant.
Duration: 9:37 min.
Description:
This episode will demonstrate how to graph data in Excel with just a few quick steps, using the Pivot Chart function.
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 and I am a resource teacher for Statistics Canada. This episode will demonstrate how to graph in Excel with just a few quick steps, using the pivot chart function.
Excel uses the word “chart” to mean “graph”, so pivot charts are graphs where the attributes are “pivoted” or switched in and out of the plot area to create a variety of graphs very quickly.
The steps for creating pivot charts will be very similar in other spreadsheet programs, such as Open Office Calc, Star Office Calc, Corel QuattroPro and AppleWorks. So experiment at little and you should be able to figure out how to do pivot charts in whichever program your school has access.
To get started with learning to use pivot charts, you will need your Census at School class dataset that you have already imported into Excel. So, open that dataset now. I am working with the data for 32 students in a grade 6 class.
We start by informing Excel which dataset we wish to include in the pivot charts. To do this, click on one cell only in the middle of your dataset. Avoid the first and second rows, but any cell below that is fine.
Now, find the pivot chart option: go to the Data menu and select PivotTable and PivotChart Report. Under the question What kind of report do you wish to create?, make sure the option PivotChart report (with PivotTable report) is checked and click the Finish button.
A blank pivot chart will appear on the screen with a PivotTable Field List containing a list of all the attributes in the dataset, in the centre. We will be dragging attributes from the pivot table field list to the grey area with the Drop Data Items Here written across it, to create our graphs.
Firstly, pivot charts needs a count of the number of cases—or in this situation, the number of students—in the dataset. To provide this, drag any attribute that contains categorical—that is, text data—into the grey space. I’m going to use the gender attribute. You will see a single bar appear, showing the frequency of 32. The box in the upper left corner of the graph tells us that this is the count of the gender. In other words, there are 32 students in this class that can be counted up using the gender attribute.
Let’s make this first graph about the breakdown of eye colours in the class. We need to drag the eyes attribute to the category holder at the bottom of the graph. This is our horizontal or x-axis variable. So click on the word eyes, and keeping the mouse button pressed down, drag it to the words Drop Category Fields Here, and let go of the mouse button. We immediately have a bar graph of the distribution of the various eye colours.
Suppose we wanted to make a new graph about whether students are right-handed or left-handed. We start by removing the x-axis attribute eyes: click on eyes and drag it back to anywhere in the field list, and let go of the mouse button.
Now the category holder is open for the hand coordinates attribute, so let’s drag and drop it there. We can see that the majority of the class is right-handed. This is the mode for the dominant hand data.
I have heard that left-handedness is a more common trait in males than in females. And I wonder if that is true for this sample of students. Let’s see how pivot charts can help us answer that question.
We have already graphed the dominant hand data on the horizontal axis, which is displayed as a bar graph. Now we want to separate this information into the results for males and females. You can think of it as splitting the x-axis bars into male and female information. To do this, click on gender in the field list, and drag it to the series holder on the middle right side of the graph, where you see the words Drop More Series Fields Here. This splits the bars into male and female sections, displaying them as a stacked bar graph.
This isn’t bad, but it might be clearer if the data was displayed as a double bar graph. To change this, go to the pivot table menu bar, and click on the Chart or graph icon. The Chart Wizard will open. In the sub-type menu, click the side-by-side double bar graph option, and click Finish. Now, our graph shows quite easily, that in this sample of students, there was one more male left-handed student than female.
A tricky thing about reading this graph is the colours that Excel has automatically assigned to each of the genders. The default for Excel is to paint the first bar of the graph blue (which in this case is female) and to paint the second bar burgundy (which this time is male). If that’s confusing, we can change those colour options using the paint can. Click once on one of the female bars, go to the “paint” icon in the upper menu bar, and select a colour; I am choosing red. Then do the same thing for males: click once on a burgundy bar, go to the paint icon and choose a colour, this time blue. There, that’s easier to read. And notice that the colours have changed in the legend as well.
So far, we have graphed only categorical data—that is, data whose options come in categories—like gender comes in the categories male and female.
Let’s see how we can use pivot charts to graph numeric data.
Start by clearing the attributes from the category and series holders. Since we are going to graph numeric data, we also need to clear the categorical data that we put into the count box. So, drag Count of gender back into the field list. Now we are back at square one.
To make a graph of the height data, start by dragging the height attribute into the grey area on the graph. A single bar graph appears, showing a frequency of close to 5000. What does this mean? The box in the upper left corner reads Sum of height, meaning that when all of the student heights are added together, they total about 5000. This is not very helpful for us.
Let’s see if we can adjust this calculation to give us the mean—or average—height. Right-click on the Sum of height button and select Format PivotChart Field from the context menu. Now, scroll down the Summarize by list and select Average, then OK. Now we can see that the average height for the students in this class is about 150 cm tall.
To find out the exact number for the average height, we need to look at the pivot table that goes with this pivot chart. Notice that at the bottom of the page, there are three tabs: this pivot chart is on the tab marked Chart1 and the class dataset is on the tab marked Class. If we click on the Sheet1 tab, we can see the table that shows the information for our height graph: it shows that the average height is 149.48 cm.
This tells us the average height for the whole class. How can we find the average height for each of the genders?
Go back to the pivot chart on the Chart1 tab. To divide this data into the male and female categories, we will drag the gender attribute to the category holder. Immediately, the data is separated into genders, and we can see that the average height for the girls in this grade 6 class is several centimetres taller than the average height for the boys. How typical is that for 11 and 12 year olds?
To see the exact numbers for the average heights, return again to Sheet1 and see that the average height for the girls rounds to 152 cm. and for the boys, 147 cm.
How do these numbers compare with grade 6 students across Canada? Your students can find out by comparing their pivot chart answers with the Census at School Canadian summary results for last year. Send them to the Data and results section of the website, click on Canadian summary results and select the most recent year. Scroll down to Average height by age and let’s look at the 11 and 12 year olds. Well, at age 12, the girls are taller—but only by one tenth of a centimetre—so it looks like the girls in the class I analysed are a particularly tall bunch.
There are some great lessons that could use pivot charts for the data analysis in the Learning activities section of the Census at School website. In the Grade 4 to 8 lessons, take a look at How Weird is Our Class?, You are the Researcher or Are You a Modal Student?.
In the Grade 9 to 12 section, pivot charts would be great for analysing data in the Bullying—studying it to curb it lesson and the Worksheets for analysing class data, activities 1 and 2.
Give pivot charts a try in your classroom. They are used worldwide and you can be sure that you are giving your students a skill that they can use throughout their learning and earning careers.
