So you’ve completed your survey and now you wonder what you can learn from your data.

That’s easy, isn’t it? Three months ago, your survey began with a set of questions. Now you’ve got your data. Look up the answers!! Oh – and don’t forget the PowerPoint slides. Make them pretty.

But there must be more to it than that. After all, you received 419 responses to 26 survey items. That’s 10,894 responses to survey items. Surely we can get more than 18 PowerPoint slides and an internal memo out of them!

How well you explore your data is key to effectively delivering value to your audience. To do this, eyeball your data.

What does this mean? Well, it doesn’t mean export to Excel and then stare at the computer screen (although I’ve tried that).

We’re going to look at using SurveyGizmo reports in combination with a couple of uses of Excel to discover more of what our data are telling us. Within SurveyGizmo we will use summary reporting with bar charts; we will use crosstabs; and we will use the data export function. Then, in Excel, we’ll look at using some very simple functions, a sorting method and a couple of charts.

Your first step in eyeballing (rather than analyzing) your data is to run a SurveyGizmo summary report. I suggest a bar-chart format. Bar charts make differences and patterns easier to find. Scroll through it. You’re not looking for answers to questions. You’re looking for charts that don’t look like the other charts – for bars that are longer than others or for charts with bars of similar length. Identify those charts that represent survey items of particular interest.

Print the report. Put *’s next to items of particular interest to your audience. Look for charts that don’t make sense or, if you have a baseline, that are different from the baseline. Are there other charts that might explain the charts that surprise you? For example, if you’re surveying customer satisfaction and the overall value ratings are down, are the ratings lower on product quality or responsiveness also? If they are, don’t assume that one causes the other. Do a SurveyGizmo crosstab with “value” in your rows and “product quality” and “responsiveness” in your columns? Do the percentages show a pattern? If not, that may be a finding also and you might want to report that there is no apparent relationship between “value” and either “product quality” or “responsiveness.”

Any hypotheses confirmed? Any surprises? Make note.

Now let’s use Excel to explore further. Do a SurveyGizmo export to an Excel spreadsheet. Each row is a respondent and each column is a survey item.

Before building new charts, we’ll use some of the simplest functions and sort our data to see what we can find.

Go to the blank row at the bottom of the data and use the =AVERAGE function to enter each item’s mean score. Go to the next blank row and use the =MEDIAN function. Now, sort the file HORIZONTALLY on the means. [Note: Many people are unaware that data can be sorted horizontally. To find this option, click on DATA-SORT-OPTIONS-SORT LEFT TO RIGHT.] Now, your means are sorted from left-to-right. Most of your medians probably are also. But which medians are out of order? But take another look at that row of medians. Are some out of sequence? Wherever a median is out of sequence, you have found a distribution that is unusual compared to the dataset as a whole.

In and of itself, an unusual distribution may not mean anything – but once you’ve found one, look more closely at it to see if it offers anything you want to report or if it prompts questions you had not thought of before. Can you explain to yourself why respondents would be different for this item?

Now let’s make two charts.

The first chart is one you can use to compare several items to each other in one picture and is useful with scaled items (for example, “on a scale to 1-to-5″). Highlight the appropriate data and open the chart wizard. Click BAR and select the “100% stacked bar” as the chart sub-type.

[Note: If you're chart does not look like this, you may have your series set up in rows when you want columns (or vice versa). No need to rearrange your data. In the chart wizard there is a Rows/Columns button that will change the orientation of the chart for you. Also, this chart is not in the default colors but has been enhanced for your viewing pleasure.]

One of the nice features of this chart is that you can compare items with different response rates to each other. The chart will standardize the distributions to make them comparable.

What can we see in this chart? Well, product quality is the attribute that satisfies the most customers. One-in-ten being very dissatisfied seems like a lot for an attribute that customers are generally so satisfied with. You might want to isolate those ten percent and see if they all have the same product or share some demographic. Also, if you’re combining “Very satisfied” and “satisfied” as one category, you are missing the huge difference in customer responses between “customer service” and “value.”

You may have quantitative items in your data that are not scaled. Average ratings from a teacher evaluation survey, for instance, may show that average ratings in one department range from 15 to 85 while in another department they range from 40 to 70. To explore the ratings across departments at your university, create a SurveyGizmo form asking each department to enter the average overall rating for each professor in the department. Your SurveyGizmo Excel download will look like:

Now go to the first blank row, as we did above, and enter the averages, then to the next blank row and capture the maximum rating (=MAXIMUM). In the next blank row capture the lowest ratings (=MINIMUM) and finally, in the next row enter the median functions again.

Excel offers chart types that are designed for showing stock prices – but they have other uses as well. Highlight the mean, maximum, minimum and median ratings. [Note: They must be in this order.] Open the chart wizard again and click STOCK. Select “Open-High-Low-Close” as your chart type.

From the average teacher ratings alone, you may have thought that the English and Math departments were similar in teacher evaluations but, as you can see from the chart, they are really quite different. Also, the height of the boxes shows how far your mean and median are from each other. Over half of the teachers are above the median and half are below in each department. The height of that box in biology indicates that, while over half of the teachers are above the top line of the box, those that are low are so low they are dragging the average score way down. You have found some true outliers.

As you go through these steps, expect to find many, many more questions than answers. That’s the whole point of data exploration – to be able to help your audience know all of the questions which their data can begin to answer. These steps are a way to explore data; not THE way to explore data. Starting here may lead you to idiosyncratic strategies that work for you and your audience. The steps we have reviewed here are:

• Run a SurveyGizmo report with bar charts. Look for items of interest to your audience or that are unique.
• Run SurveyGizmo crosstabs whenever you think items may be related to each other.
• Export your data to Excel.
• Use =AVERAGE and =MEDIAN functions to find items with unusual distributions.
• Chart your data. Compared scaled items to each other with 100%-stacked-bars. Compare non-scaled distributions using variations of Excel stock charting tools.

In doing this, you’ve learned a lot about your data and never had to sit through a class in statistics. No statistical analysis and you did it all with your eyeball. Good work!

What’s that you say? Your survey also had 4 open-ended questions and you don’t know what to do about that? That’s an article for December . . . or maybe January.

Tags: bill johnston, Excel export, quantitative data, Survey Expert