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
Enter your email address below to download the sample workbook.
Better Excel Charts for Surveys
Let’s look at some better charts for survey data. The first one uses in-cell charts, or you could use Conditional Formatting Data Bars if you prefer.
In-cell Charts or Conditional Formatting Data Bars
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.
Summary
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.
More Charts
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.
Excel Heat Maps
Erdem Urasoglu
Hi Mynda,
I was just trying to see the running macros behind those three buttons for sorting the columns and realized that there is no “edit macro” setting in assign macro menu.
How can I see the codes? I only see the names of the macros as “OptionButton13_Click” for example..
Thank you.
Mynda Treacy
Hi Erdem,
There aren’t any macros in the file. It’s a .xlsx file. The sorting is done with formulas.
Mynda
Mike Hyne
On the Sorted Converging Stacked Bar Chart I see the data it’s using is under the chart and how it uses functions to grab the data located to the right. I see the GroupBox with the 3 radio buttons and how the selected radio button number is put in cell W110 and how X110 converts that to the number of the column to be sorted by. What I don’t see is what is using the value in X110 to do the sorting. I’m probably missing the obvious but need help to see it. Mike
Mynda Treacy
Hi Mike,
The value in X110 is used by CHOOSE and OFFSET in the formulas in column W to find the column containing the values to be ranked:
Mynda
Ivars
Great example of ranking. Thank you for sharing.
Mynda Treacy
Thanks, Ivars. Glad you liked it 🙂
juanito
Good article, thanks!
On the dataviz (not Excel) side of this, there is a good blog article with expert commentary on Stephen Few’s site here.
https://www.perceptualedge.com/blog/?p=2239
About halfway down he discusses specifically the Likert scale issues your solutions variously address.
Mynda Treacy
Thanks for sharing the link, Juanito. It makes an interesting read.
Jacques Raubenheimer
Hi Mynda
it is hard to tell without seeing the actual items, but these items use what is called a Likert scale rating. However, statistically speaking, there is a lot of misunderstanding about how Likert scales should be analysed, and without going into the complexities, it is worth pointing out that, as John Uebersax points out: “Common Error 1
A Likert scale is never an individual item; it is always a set of several items, with specific format features, the responses to which are added or averaged to produce an overall score or measurement.”
Thus, plotting individual scale counts for items like these may (may!) negate the actual statistical theory underlying how the scale as a whole should be treated. That is something that should be kept in mind and dealt with appropriately.
Mynda Treacy
Thanks for sharing the link, Jacques.
Glenn Case
Mynda:
I enjoyed this tutorial, not so much for the discussion on charting as for the interesting way in which you generated the sort order for the charted values. Very neat way to do that!
Mynda Treacy
Thanks, Glenn. Great to see you took time to download the workbook and look under the cover 🙂
Muhammad Fathy
Awesome Chart! : )
Mynda Treacy
Thanks, Muhammad! Glad you liked it.
Mynda
SunnyKow
My preference is to use either data bars or bar charts with each question having a single chart. I will be able to print the entire dashboard (12 questions and their results) in a single sheet of paper. For example:
QUESTION 1
Strongly Agree ||||||||| 30%
Agree |||||||||||| 35%
Neutral ||||| 20%
Disagree ||| 15%
Strongly Disagree || 5%
It allows me to see the results more clearly.
Mynda Treacy
Good point, Sunny. I guess it comes down to whether you want to look at each question individually or whether you want to compare them.
Anand Kumar
Cool Mynda and Thanks for Share.
But I am confused with two Bar Chart Formulae,
First One – REPT(“|”,Cell/MAX(Array)*100) AND REPT(“|”,Cell*100)
Could you please help which one is better to use and where.
Mynda Treacy
Hi Anand,
The first formula normalises the values in the column; where the max value is 100 and everything else is a proportion of the max value. The second formual simply multiplies the cell value by 100. The first is the better formula as it ensures the longest bar is 100 pipe symbols long. The second formula is fine if the values in your cells aren’t that big, or as in the case above, the values add up to 100 or 100% anyway, so no need to normalise.
Mynda
Anand Kumar
Thanks for your clarification ☺.