Survey data can be tricky to display in a chart so in this post I’m going to give you some options.
Let’s start by looking at what you shouldn’t do, and that is a 100% stacked bar chart like this:
The above chart plots the 15 questions on the vertical axis and the responses colour coded in the stacked bars.
I’ll give it one thing, it looks nice and it’s eye catching, but the problem with all stacked charts is it’s very difficult to compare the series in the middle since the starting point is staggered for all but the series on either end.
And that means it’s going to be difficult to interpret for the reader.
Download the Workbook
Better Excel Charts for Surveys
This chart is better than the first because the bars for each answer group start at the same point. It makes it quick and easy to compare the responses within a column. We also have the values for each question as opposed to an axis for scale.
We could improve this chart by sorting the answers in descending order for either ‘strongly agree’, or ‘strongly disagree’. This would prioritise or focus the reader’s attention, or even better, give the reader the ability to choose which column to sort on.Converging Stacked Bar Chart
In the chart above I’ve omitted the N/A responses so each question won’t add up to 100%, but that’s ok because I’m really only interested in the responses that were applicable.
Fellow Excel MVP, Jon Peltier, calls this the ‘converging’ stacked bar chart because the strongest responses are on either side of the vertical axis. This chart enables the reader to see the ‘strong…’ responses and more easily compare them, although we could take it a step further.Sorted Converging Stacked Bar Chart
This chart is sorted in descending order on the ‘Strongly Disagree’ response, as it would be reasonable to assume that this is where you want to focus your attention.
But wouldn’t it be nice to allow the reader to choose what order to sort the chart in….Custom Sorted Converging Stacked Bar Chart
This chart allows the reader to choose whether to sort by question number, 'Strongly Agree' or 'Strongly Disagree'.
Building Converging or Diverging Stacked Bar Charts
A converging or diverging stacked bar chart is really just a stacked bar chart with a few tricks, which you can see in the Excel file available for download above. They are:
1. Change the values you want to display on the left of the vertical axis to negative numbers.
2. Use a custom number format for the horizontal axis labels (0%;0%;0%) so that the labels on both sides of the vertical axis are displayed as positive percentages.
The takeaway should be that changing the way your data is presented and sorted can alter the focus for the reader and more quickly convey a particular point.
If you’re interested in reading more about different charts for survey data then check out this post by Jon Peltier. Warning, get supplies as it’s long!
And if you’d like to learn more cool chart ideas and tricks, including the sort button option above, please consider my Excel Dashboard course.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.