Charting survey data can be tricky, and when you want a segmented survey data chart it adds another layer of complexity.
Bart sent me the data below and asked how I would go about visualising the data to include the size of the segments (students, lecturers, staff).
Segmented Survey Data Chart – Option 1
You might be tempted to dive straight into a stacked bar chart with percentage labels like this:
Pros: Clearly shows the proportion of each segment’s opinion and the overall consensus.
Cons: It doesn’t give any indication of the segment's sample size.
Segmented Survey Data Chart – Option 2
It’s not really a chart, rather a table with Conditional Formatting. Each segment has the lowest values highlighted in shades of red and the highest values in shades of green.
Pros: Clearly draws your attention to the frequency of the responses. We can quickly see that the majority, irrespective of their segment, are neutral or in agreement. It also shows the sample size of each segment, which is important to Bart.
Cons: Unlike a chart with bars that represent size, the table makes it difficult to compare the segments and opinions at a glance.
Segmented Survey Data Chart – Option 3
100% stacked bar chart with value labels:
As with the first example, the bars in the chart display the percentage of each opinion per segment. The data labels show the segment size.
Pros: Discloses the size of each segment and illustrates the sentiment based on the size of the coloured segments.
Cons: Could be confusing mixing values in the labels with percentages on the horizontal axis.
Segmented Survey Data Chart – Option 4
The chart below consists of 5 separate stacked bar charts aligned closely together so they appear as one chart.
Note: It’s important that the horizontal axis scale on each chart is the same. I’ve hidden it in the charts above because I’ve used labels instead.
Pros: Easy to see the sentiment at a glance. i.e. majority are neutral or in agreement. Labels display the segment size.
Cons: Not easy to compare the total size of each segment or the proportions of opinion within each segment. e.g. it’s not easy to tell that 80 students in agreement represents 30% of the total student opinion.
Segmented Survey Data Chart – Option 5
This chart is also 5 separate bar charts aligned close together. Again, the horizontal axis, which is hidden, has the same scale in each chart.
Pros: Shows both the discrete values and percentage of opinions within each segment. Easy to compare the segments within an opinion. The bars show the size of the segment. The ‘All’ segment shows the overall sentiment.
Cons: Labels make it feel cluttered and could be confusing and time consuming to read. Also, there is a bug in Excel that resizes the label boxes upon closing the file, so they end up wrapped when you open the file again which is annoying.
Segmented Survey Data Chart – Option 6
This is a twist on the more common survey data charts and like chart option 4 it shows both the sentiment and the size of the segments.
Pros: Discloses the size of each segment visually and through the labels. Illustrates the sentiment based on the size of the coloured segments.
Cons: Not easy to see the proportion of opinions within a segment. A lot of manual work to format the colours and insert the labels. Not easy to compare the size of each segment’s opinion.
My preference is chart option 4 because it’s the quickest and easiest to read and clearly shows the overall sentiment while still disclosing the size of the segments. However, if the opinion of one segment is more important than others then chart option 5 would be the best.
This survey only asked one question, however typically surveys ask multiple questions. Check out this post on Excel Charts for Surveys to get some more ideas.
Download Workbook
Enter your email address below to download the sample workbook.
Year on Year Charts
Howard Silcock
One modification that would improve ALL the charts has nothing to do with Excel! Change the label ‘Nor Agree or Disagree’ to ‘Neither Agree nor Disagree’. (Of course, this really needs to be changed in the data.) For some of us, that’s more distracting than any of the shortcomings of the individual charts!
Mynda Treacy
I agree 🙂 however, the data wasn’t mine, so I didn’t want to change it.
Mynda
Greg Taillon
Pie charts are much better at showing a percentage of the whole. A large pie chart showing the sentiment with four smaller pie charts showing the participants’ percentages for each sentiment would clarify the results of the data.
Mynda Treacy
Hi Greg,
Thanks for sharing your ideas. The pie charts won’t visually illustrate the size of the segments, which was the objective for Bart.
Mynda
Philip Wiest
This is a terrific article!
I’m not a fan of pie charts… but with 3D Maps, the comparative size of a bubble could be used to communicate the relative size of the pool of respondents. With 3D Maps, you can place bubbles at separate x y coordinates that would appear as pie charts.
This is a great illustration of how “making things look easy” … takes a lot of work!
Mynda Treacy
🙂 Glad you liked it and thanks for sharing your ideas, Philip.
Daniel
Hi Mynda,
those are all creative ideas, but in the market research industry only option 1 would be “correct” in the sense of a common ground. One has to ask what the goal of the chart is – I would say, one likes to know the opinion of the three groups in comparison to each other and to the Total. Option 1 does the job, all other options not (well option 3 is not bad as it makes the proportions visually comparable). If the sample size is relevant, the simplest solution is to write the numbers of cases below the group names – e.g. like (n=270) below “Students”.
Another solution would be to show the sample size in a second chart near the original chart, in a simple way like a bar chart.
Kind regards,
Daniel
Mynda Treacy
Thanks for your ideas, Daniel. The goal of the chart is to illustrate both the sentiment and the size of the segments, which isn’t typical for most surveys. This requirement to visualise the segment size was the driver behind my chart choices.