How to Make a Diverging Stacked Bar Chart in Excel
Last week my friend Ann Emery posted a dataviz challenge on something I’d been wanting to figure out for a long time: how to make a diverging stacked bar chart in Excel (I’d also heard of them as sliding bar charts, but getting our dataviz terminology on the same page is another blog post). Diverging stacked bar charts are great for showing the spread of negative and positive values, such as Strongly Disagree to Strongly Agree (without a Neutral category) and because they align to each other around the midpoint, they handle some of the criticism of regular stacked bar charts, which is that it is difficult to compare the values of the categories in the middle of the stack.
Ann will post answers to her challenge, but here is how I figured it out. It was approximately 8 billion times easier than I expected.
The secret is hidden values at either end of the bars.
Here is what my data table looks like:
Let’s walk through it a bit. In the middle, in the black text, are my actual data values, what I ultimately want to show the audience. On either side I have purple Buffer columns. THESE ARE THE SECRET COLUMNS!!! In gray you can see my notes. So. Yo. Listen up. From “Disagree” over to the right, those values need to add up to 100%. Just mentally sum your Disagree and Strongly Disagree values and put the remainder in the Buffer column. Same thing for Agree over to the left.
Then select all the headings and values, from Buffer to Buffer, and create a 100% stacked bar chart. Mine looked like this:
Look at that! Now the only bars with color are the ones that encode our values. Still, it looks like crap, right? So delete the legend (we’ll add it back in later), delete the gridlines, and delete the y axis. Delete the x axis too BUT FIRST! Adjust the axis values so the max stops at like 70%. We want more of the chart area taken up by our actual values.
I also added data labels and changed the colors so that the positive were blue-er and the negative were red-er:
POW! That’s right! It takes just a little forethought with some basic math to better represent diverging survey data and circumvent some of the issues with a basic stacked bar. Rock on, people!
Tutorials just like this are in my latest book, Effective Data Visualization.
Learn in the Academy!
You can find step-by-step instructions on how to make 60+ awesome visuals in my Evergreen Data Visualization Academy.
Video tutorials, worksheets, templates, fun, and a big-hearted super-supportive community. Learn Excel, Tableau, R or all three. Come join us.
Enrollment opens to a limited number of students only twice a year. Our next enrollment window opens April 1. Get on the wait list for access a week earlier than everyone else!
Master Dataviz with Graph Guides!
Our newest program, Graph Guides, is a custom-built, year-long sprint through 50 Academy tutorials.
When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.
We open enrollment to 12 students at a time and only twice a year. Get on the waitlist for early access to our next enrollment window.