NOTE: These videos were prepared when the Census at School Project was managed by Statistics Canada. Most of the information is still relevant.
Duration: 5:12 min.
Description:
This episode will cover creating scatter plots in Microsoft Excel.
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 am Angela McCanny. This episode will cover creating scatter plots using Excel.
Scatter plots are for graphing numeric variables, like the height or foot length data from the Census at School survey. Scatter plots allow us to plot two numeric variables against each other to see if there is some relationship or correlation between the data.
One lesson on the Census at School website that is excellent for discovering trends in numeric data is the lesson The Vitruvian theory – does it apply to you? In the Learning activities section, you can find a version of this lesson for both the Grades 4 to 8 students or the Grades 9 to 12.
This lesson is based on Leonardo da Vinci’s theory that in the perfect human, the height measurement and the arm span measurement will be the same. We can use Excel to help us create the graphs to test this theory.
It is very easy to create scatter plots using the Chart command in Excel.
Open the Excel spreadsheet containing the class dataset, and insert a new worksheet. Rename this tab Height and Arm Span.
We need to copy and paste the two columns of data that we need for this graph from the class dataset worksheet to this worksheet. So, click on the Class dataset tab and select the height column, using the mouse. We click on the letter D at the top of the height column and hold down Control C.
Click on the Height and Arm Span tab, click in cell A1 and hold down Control V to paste. There, we have the height data. Now, go back to the Class dataset tab and repeat the process for the arm span column in column E. Copy and paste. All right, we have our data.
Now to create the scatter plot, select the data to be included in the graph. Click in cell A1 and drag to the bottom right cell in column B. Now, go to the Insert menu and select Chart.
This time in Step 1, select the scatter plot graph and for the sub-type, I will stick with the dot points only. Click Next.
Step 2. No changes are required here; click Next.
In Step 3, type the title and axes labels. For the title, we might type Height and Arm Span in Class 9A. The x-axis is the data from column A, so that’s the Height in centimetres. The y-axis is the data from column B, so we’ll type Arm Span in centimetres. Click Next.
Step 4 requires no change, so click Finish.
If need be, click and drag on the corner of the graph to adjust the size and drag the graph to where you want it to appear on the worksheet.
If we look at the graph, we can see that the data is quite clumped in the upper right corner of the graph. The reason for this is that both axes start their numbering at zero, but the data entries don’t start until somewhere in the hundreds. We can adjust the axes so that the data is more evenly distributed throughout the graph.
To adjust the x-axis, click once on the 0 at the left of the horizontal axis. Now, right-click and select Format axis. On the tabs at the top of the window, click on Scale. It’s the minimum that we want to reset, so highlight the 0 in the Minimum box and replace it with an appropriate lowest number for your data; in this case, 130.
Reset the Major unit – this is the unit the axis will go up by – to 10. Reset the Minor unit to 5. Click OK. Repeat these steps for the y-axis, using a good minimum number for the arm span measurements. Click on the 0, then right-click and choose Format axis. Reset the minimum, and the major and minor units. Click OK.
Taking a look at the graph, we can see that the data is more evenly spread along the axes. This legend is not meaningful, so delete it and now the scatter plot is looking quite good.
What is it telling us about our data? We can see that the data points are tending to form a line going upward to the right. We can see that the shorter people have shorter arm spans and the taller people have longer arm spans. There is definitely a positive trend in the data. This is telling us that there is some correlation between students’ heights and their arm spans: the taller they get, the longer their arm span.
It would be fun to repeat the scatter plot using some of the other measurements:
Is there a connection between foot length and wrist circumference perhaps, or maybe our forearm length?
Would it make sense to find a correlation between the number of students in a household and the students’ heights? This might be a good one to graph to see what no correlation looks like.
What correlations can your students find in the data?
Make lots of graphs and enjoy your discoveries!
