Leave a Reply

    Your email address will not be published.

    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!

    Ann K. Emery's tips on visualizing knowledge gains after a program: Here are some of the questions from 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:

    Made over report using 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:

    Report 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:

    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

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.


      Leave a Reply

      Your email address will not be published.

      You Might Like

      Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.


      Visualizing 24 School Divisions’ Submissions with a Dashboard in Microsoft Excel

      Amadu Sidi Bah encountered a forgotten folder on the shared drive: “Stakeholder Submissions.” These text-heavy reports had been left to gather dust. He wanted to make sure the stakeholders’ recommendations were used in policy decisions, and he decided to do something about it. In this blog post, you’ll see the Excel-based dashboard that Amadu created from those 24 reports.

      More »

      Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.



      Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.