Does your organization collect data through online surveys or paper surveys? Do you need an easy, effective way to visualize survey results in Microsoft Excel? Surveys are one of the most common ways to collect information. In this blog post, we’ll depict how participants’ knowledge changed after participating in an educational program at a museum. Bonus: You can even download the Microsoft Excel spreadsheet and the Microsoft Word document that I used to create these data visualization makeovers.
Before: Just the Survey Questions; No Graphs Yet!
A couple weeks ago I spoke to Harvard University graduate students about visualizing survey results. This not at all to very survey scale is quite common in my research circles so I’m sharing our ideas with all of you, too.
Here are some of the survey questions that were asked before and after program participation. Don’t worry, the online survey was formatted much more beautifully than this screenshot from the Microsoft Word version of the survey!
After: Stacked Columns for the Ordinal Survey Scales
I’m going to show you two makeovers. In both makeovers, I used stacked column charts to display these ordinal survey scales.
Why did I choose vertical column charts instead of horizontal bar charts? I displayed the patterns over time from left to right across the page. Before results go on the left and after results go on the right, so they get vertical columns (not horizontal rows).
Why did I display percentages instead of numbers? There were more than 100 survey responses, so I converted the raw numbers into percentages. When there are fewer than 100 responses, I display the raw numbers. It’s super confusing to talk about 16.56% of 14 people.
Why did I use three colors? I suggest that you color-code by category because it’s a great strategy for breaking up oceans of information into manageable chunks.
Why did I use different shades of each color? The very knowledgeable to not at all knowledgeable scale is ordinal so I used darker and lighter versions of each hue to correspond to the amounts of knowledge.
Why did I write titles and subtitles? I’m a visual person and prefer reading graphs over paragraphs but some viewers will prefer reading paragraphs over graphs. One of the most common mistakes I see among novices is that they focus so much on creating the graph that they forget about the paragraphs. Your viewers will benefit from having both.
Makeover 1: The Traditional Data Visualization Approach
Before you create any data visualizations, I suggest doing some upfront planning with your colleagues. You’ll want to discuss a few thought-starter questions in advance. For example, who’s your audience? Are you designing your visuals for a technical or a non-technical data audience? Are you making a slidedeck, a handout, a technical report, or some other dissemination format altogether?
Here’s the most important planning consideration in data visualization projects: Are your viewers expecting a story?
Sometimes your viewers will expect a traditional data visualization approach, in which they’ll (hopefully) read between the lines and uncover a takeaway message for themselves.
Other times, your viewers will expect a storytelling data visualization approach, in which you use a dark/light color contrast and explicit text to uncover the takeaway message for them.
Here’s the first makeover, which uses a traditional data visualization approach:
Makeover 2: The Storytelling Approach to Data Visualization
Stacked bar charts are one of the most common ways to display survey results because surveys often include scales like this one.
But, we have to be careful because one page with two points in time, three survey questions, and five options per survey question can get cluttered, fast!
In this version of the handout, I used saturation to guide the viewer’s eyes towards the very and moderate categories. In other words, those categories are dark while the other categories are light. This dark/light color contrast is called a preattentive attribute, and it’s one of the easiest strategies for telling a story with data. Your viewers don’t have to think about it or waste any of their precious mental energy. Their eyeballs are instantly drawn to the darker parts of the page.
In your project, you may choose to draw attention to the not at all knowledgeable category. There are several correct ways to guide eyes with saturation. Your job is to anticipate what your viewers will find most useful. Just use your best professional judgment.
Here’s the second makeover, which uses a storytelling data visualization approach:
Learn More about Visualizing Survey Results
In this blog post, we looked at visualizing how participants’ knowledge about historical events increased after participating in an educational program at a museum.
Here are additional resources that show you how to visualize survey results in Microsoft Excel:
- Agree/disagree scales
- Check-all-that-apply questions
- Demographic data
- Workshop satisfaction survey questions
Bonus: Download the Materials
Want to explore these graphs in more detail? Download the Microsoft Excel spreadsheet and the Microsoft Word document that I used to create these handouts.
Purchase the templates