Using Excel to Analyze Data

Using Excel to Analyze Data

2003 Version

1. Creating Pivot Tables and Graphs

A.  Pivot tables and graphs are one of the most useful tools in Excel. They allow you to analyze and quickly combine multiple standardized tests, grade levels, and special education categories.

B.  Within pivot tables it is very easy for you to move these variables around.

1.  Let’s say you were looking at the MCA-II Reading Test across grade levels. After taking the time to type all grade levels into Excel, you realized that 10th grade was the only grade you were interested in.

a.  With a regular table or graph you would have had to retype the data for 10th grade and create a new graph.

b.  What’s nice about pivot tables is it allows you to either view the MCA-II Reading Test across all grades or just across 10th grade with the click of a button.

C.  Example: we want to compare special education and regular education on the MCA-II Reading Comprehension Sub-strand across all grade levels. To do this:

1.  Open the “Sample file for Excel training”

2.  Click somewhere in the data (one cell should be outlined in black).

3.  Go to DATA and select “Pivot Table and Pivot Chart Report”

4.  Select “Next” and then “Next” again.

5.  Then click “Layout”

6.  To view the MCA-II Reading Comprehension Sub-Strand drag the 7th Grade Reading Comprehension Sub-Strand category on the right hand side into the area labeled “DATA” in the table to the left.

a.  Dragging a category into the “DATA” area tells the computer that this is what you want to measure.

7.  Next, double click on the MCA-II Comprehension Sub-Strand in the “DATA” area on the table. Select “Average.”

a.  Before it was a count of the MCA-II Comprehension Sub-Strand. This is not what you want to view. What we want to see is the average.

8.  Now, we have to decide how to look at this data. What do we want to compare and across what variables?

a.  Earlier we decided to compare regular ed. and special ed. To do this, drag special ed. into either the “ROW” or “COLUMN” area of the layout (special ed. is all the way to the right, you will have to arrow over to find it).

i.  Also, if you place special ed. in the row area and find it would be easier to analyze if it were in a column, you can change that very easily at any time, even when it is in the graph format.

b.  We want to view this comparison across grade levels. To do so drag “Grade” into the “PAGE” area in the table. This allows you to initially view the comparison across all grade levels, but you also have the ability to show only one grade level at a time.

9.  Our table is now complete. Click “OK,” and then “Finish.”

a.  Right now, the table shows you how special ed. compares to regular education, across all grade levels. If you would like to show only one grade level, select the drop down arrow to the right of the grade column and select a grade (for this example, there is only one grade level (8) so this does not apply. If you had more than one grade level this is how you would view only one grade).

10.  To graph this table: RIGHT click in a single cell in the “Total” column and select “PivotChart.”

a.  Within the graph you are able to rearrange the variables by dragging them into different areas of the graph.

i.  Try it out-drag Grade (left hand corner) into the box labeled “Drop Series Fields Here” above “Total” on the right. This will give you a column graph with 2 columns, one special ed. and the other regular ed. for every year (in this case there is only one).

ii.  You can also drag “Special Ed.” (on the bottom) to that same spot above “Total.” This shows you another way of viewing the comparison between special ed. and regular ed. students. To view the columns side by side (not on top of each other): Right click in the graph and select the first column graph.

D.  Additional features:

1.  To change the y-axis so that the minimum value is zero: double click on the y-axis, select the tab labeled “Scale” and place a zero in the blank labeled “Minimum.” This gives you a more accurate representation of the data.

2.  To change the type of graph: right click in the graph itself, click “Chart Type” and select whichever graph you think is suitable for your comparison. A column graph is probably best for this comparison, so a graph change isn’t necessary.

*I also recommend checking out www.schooldatatutorials.org, a website created by Dr. Scott McLeod previously at the University of Minnesota and now at Iowa State University. The website has short 1-2 minute Excel tutorials designed specifically for educators.