Displaying Likert Scale Data with a Diverging Stacked Bar Chart in Microsoft Excel

We should all be so lucky as to have a ubiquitous data scale named after us. Rensis Likert is one of those proud few, and it’s not a big assumption to assume you are all familiar with his eponymous scale. The ways that Likert scale data is displayed are many, but usually the decision on chart type comes down to either a 100% stacked bar chart (SBC) or a diverging stacked bar chart (DSBC).

Data visualization expert Stephen Few recommends the DSBC over the SBC, and Alana Pirrone does a nice job of showing how the DSBC improves on text tables, pie charts, and bar charts. For a counterpoint, Datawrapper defends the SBC as the better option, yet we still came away convinced that for quick visual communication of the strength of agree versus disagree, the DSBC is the way to go.

Two points for additional context: first, if there are only two alternatives to be compared (yes/no, like/dislike, etc.) a simple two-way bar chart, population pyramid, or tornado chart – all of which have a common axis between the choices – might be the right way to go, depending as always on the nature of the data and the story being told. Second, there is a lively debate over whether and how to include “don’t know/no opinion” responses, a.k.a. DK/NO, on a Likert scale. Some recommend a four-point scale (strongly disagree, disagree, agree, strongly agree) to force a choice, and some believe “nones” to be a mandatory and critical fifth point of data. Again, up to you, although if you are allowing the DK/NO response you should probably include the data. The links above show various visualizations of these issues to help you figure it out, including putting DK/NO responses in the middle or off to the side.

So on to our mission, showing you how to create a DSBC in Microsoft Excel (we are using Microsoft Excel for Microsoft 365 MSO, Version 2202, Build 16.0.14931.20118) 32-bit). The first requirement is data, and here we will use the Spring 2021 Global Attitudes Survey from Pew Research, Spring 2021, question 13c: the percentage of the public who say the political system (1) needs to be completely reformed, (2) needs major changes, (3) needs minor changes, or (4) doesn’t need to be changed. Pew displays the results as an SBC:

In this case Pew also omitted those who did not respond, a.k.a. “item nonresponse,” which is a separate issue from DK/NO responses. We’re going to ignore that for our purpose here. Also, this is not classic four-point Likert scale data, but for our purposes it is essentially the same thing, a four-point strength-of-belief response to a survey question. Reformatting the data as a DSBC gives us this:

You can see the original Pew SBC emphasizes the countries where the public believes complete reformation of the political system is necessary. Our re-imagining of the data as a DSBC transfers the emphasis to a division of countries into those that believe complete/major change is necessary versus those that believe minor/no change is necessary. How did we get there? Read on for the step-by-step.

1. Enter and format the data.

Enter the data into Excel in normal column format and format as shown above. Add two columns, B and G above, labeled “buffer left” and “buffer right.” For “buffer left” we entered in cell A5 the formula =1-(B2+C2) which gives us a value that, when added to columns B+C = 100%. We did the same for “buffer right,” formula = 1-(D2+E2). Drag those formulas down to fill in the rest of the data rows. We will make the two “buffer” columns invisible when we chart the data, which will magically give us a common center axis.

2. Select data, create chart, and hide buffer data.

Select A1:G18, click insert column or bar chart, and select 100% stacked bar chart. This will give you a chart with colors that match whatever your color scheme is. Now just select each of the buffer data bars, dark blue and green respectively in this chart, and change the fill to “no fill.” Notice the common center axis between “needs major changes” and “needs minor changes.”

We also deleted the chart title element and, in the legend, the “buffer left” and “buffer right” elements. What we are left with is a chart that clearly shows the relationship between the cumulative percentages of the public in each country that think complete/major change is necessary versus those who think just minor/no changes are necessary. We could stop at this point, but if you’d like to pretty it up, read on.

3. Additional formatting.

All of this is of course optional, but we think the following formatting changes enhanced the readability of this chart:

1. Change the chart colors to match more closely the original Pew colors.
2. Delete the vertical lines, including next to the country names.
3. Delete the percentage scale at the bottom.
4. Add a white solid line border to each of the four chart colors.
5. Add data labels and change text color to white for all but one series.
6. Increase country name font size from 9 to 11.
7. Decrease gap width of bars to 30%.
8. Sorted data by “buffer left.”
9. Changed values in table from percent to general (required new table…).
10. Moved some values in narrow bars to side (shift+drag) and reformatted color to black, and removed leader lines.
11. Moved legend to top.
12. Added title.
13. Added center line.

The last step is, like all the others, optional, but we feel it gives a stronger visual impression of good values (less need for change) versus bad values (greater need for change):

What we are left with is a chart that tells a slightly different story from the Pew original, in this case which countries see less need for changes to their political system versus those that see more. We hope you found this useful!

And a little red meat for the data visualization junkies out there: just for fun we updated the chart with the “no answer” data. What’s up with Taiwan?