Someone at my church wanted some advice about exploring relationships in a survey that he had taken. He asked a bunch of demographic questions (age, sex, income, etc.) and then some yes/no questions. He had the data in an Excel spreadsheet and didn’t quite know what to do with it.
I’m not a big fan of Excel for statistical analysis, but a few simple pivot tables would be a nice start.
Select DATA | PIVOT TABLE OR PIVOT CHART from the menu. The office assistant (for me it’s the cat because I hate that little paperclip), then offers a chance to look at some help files. Take a look at this to get comfortable with how a Pivot Table works.
The default choices in step 1 (Microsoft Excel database; PivotTable) both work well, so don’t change anything here. Click on the NEXT button.
If you are lucky in step 2, Microsoft Excel will select your data range for your. It helps if you place the cursor in the upper left hand corner of the data set before you started this. I had forgotten to do this, so I clicked on the little colorful button to the right of the RANGE field and then highlight the two corners of the data set you are trying to summarize.
Then click on the NEXT button.
In step 3, you tell Excel to where to place the Pivot Table. I usually like to place this in a new worksheet. Click on the FINISH button. You might think you are done, but Microsoft just lied to you. You’re not really finished. You now have a floating window.
and the skeleton of your Pivot Table.
Now you layout the variables in the table. Here I want the age group to be in the rows, so I drag and drop AgeGp from the PivotTable Field List to the Drop Row Fields Here.
Then I drag and drop survey question 1 into the Drop Data Items Here area.
Microsoft assumes that I am interested in a count, but what I actually want is a percentage. So I right click on the gray Count of 1 cell and select FIELD SETTINGS from the popup menu. What I ask for is an average.
This person was nice to code his YES/NO variables as 1 and 0, so an average of these values is exactly the proportion of YES responses.
Click on the PERCENT button to tidy up the format a bit.
This actual data shows that the proportion of YES responses was high is just about every age group.
This is just a start. Pivot Tables allow you to interact with several related variables. I would not use Excel for a really in depth statistical analysis, but it does a reasonable job with simple data summaries.
You can find an earlier version of this page on my original website.