How to Make a Diverging Stacked Bar Chart in Excel

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:

Diverging1Not cool. I should see 6 segments in the stacked bar, not six bars. So right-click, choose Select Data, and click on Switch Row/Column. Now it looks like this:

Diverging2Bingo! So now it’s time to make the two Buffer categories white. Just right-click directly on the Buffer bars and select the white fill color. When you’re done it should look like this:

Diverging3Look 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:

Diverging4To finish it off, I just need to add back my legend (across the top, using textboxes), give it a descriptive chart title, and add in my survey questions as data labels:


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.Β 

50 thoughts on “How to Make a Diverging Stacked Bar Chart in Excel
  1. Jamie says:

    Can I be you when I grow up? You rock! (Let’s just pretend I’m not already grown up…)

    I wonder if the question labels would be better underneath or above the bars… Or even in the bars!!! This would allow the 50% mark that is already lined up for each of the bars to be in the middle of the page. I think this would help emphasize the agree vs. disagree even more. Just a thought!

    • Stephanie Evergreen says:

      That could work! Words above each bar might interrupt the flow – I don’t know! Try it and let me know how it looks. I hear what you’re saying about wanting the midpoint of the bars to be in the middle of the page – or at least, the middle of the graph. But we’re also used to seeing y axis that don’t start all the way over to the left of the graph, too. So yeah, I encourage you to play with it and tell me what you think!

  2. Veronica Smith says:

    One more comment: I prefer the terms “data geek” and “data diva” to “data nerd.” The guys in our office, we refer to as “data dudes.” πŸ™‚

  3. Robin says:

    Thanks for unveiling the mystery! Just wondering why you did not have each bar line up flush right. Wouldn’t that have been easier on the eye and facilitate comparisons better?

    • Stephanie Evergreen says:

      Hey Robin! The whole idea with diverging stacked bars is that the align in the middle, so that the negative go in one direction and the positive go in another. Its easier to see at a glance how the respondents’ agreement stacks up.

  4. Sian says:

    Hi, I am new to the technicalities of DataViz so certainly no expert at all but I have to agree with Robin’s comments.

    My eye went straight to the fact that the bars had not been aligned rather than the focusing on the middle alignment/data presented.

    Perhaps the addition of an indicator/arrow/visual to further highlight the middle negative/positive alignment point might assist?

    Thanks for the opportunity to comment.

    • Stephanie Evergreen says:

      And if that’s how you interpret the graph, just don’t use it. Without a doubt, unfamiliar graph formats are harder to interpret. Stick with what’s familiar for now. When you find a time that the diverging stacked bar makes sense for you and your audience, you’ll have another tool in your dataviz ninja rockstar toolbox to whip out!

  5. Kate Tinworth says:

    Super cool. Loving it.

    To Robin’s question, I’ve found that using the exact midpoint on the paper (or slide, or whatever communication mechanism you’re working in) for the aligned midpoint in the data Stephanie’s talking about can be more aesthetically easy on the eye. You may want to play around with that. In the above example you’d get a lot of white space on the right– but that white space would help with meaning-making (e.g., “Holy shit! These people who don’t wanna be called data nerds sure do have mad skills! Look how much the data leans to the left/agree side!”) Stephanie’s choice of colors really helps too, right?!

    Thanks for the cool post, S!

  6. Meredith says:

    Can I still use the diverging stacked bar chart if I have a neutral category (i.e. a five point scale instead of a four point scale). I could just omit the neutral cateogry and get the same effect, right?

    Or would it be confusing to the viewer if the percentages didn’t add up to 100%?

    • Stephanie Evergreen says:

      I think it would be confusing to just delete the neutral category. I’ve seen some cases where people just present neutral + one side (either positive or negative). It still doesn’t add to 100% and would be aligned to the left but would make more sense if you have to use a neutral category.

      • Ian says:

        You could manipulate the buffer columns to line up the middle of your neutral category…

        • Stephanie Evergreen says:

          It’s certainly possible to make the diverging stacked bar line up in the middle of neutral but it doesn’t make as much visual sense. The whole point of this graph type is that there is a baseline in the middle from which to interpret both sides of a diverging data set. With a neutral category, there is no baseline.

  7. Hornyik JΓ³zsef says:

    Hi, I very much like what I’ve seen here. Only one comment: I would put the negative to the left and the positive to the right. Going into details with an example: Thanks!

  8. Stephanie Evergreen says:

    I just returned from giving a day long workshop to the Idaho Legislature Office of Performance Evaluation and Tony Grange commented that he would include a neutral category in here too. To do so, just divide the neutral value by two and add that number to each buffer. So if the neutral category was 8%, I’d add 4% to the left and right buffer (they’d sum to 104%). This keeps the neutral category aligned to the middle.

  9. Jon Peltier says:

    Other comments have covered two of my three points:
    1. Positive right, negative left.
    2. Split the neutral category so it straddles the center line.
    3. Use of a vertical line at the split helps people understand the chart.

  10. Amanda Whitmire says:

    I’ve been analyzing survey results for the first time, and am playing around with how to visualize the data. I used your figure for inspiration – thanks very much for the tutorial. I started in Excel and added everything else in Adobe InDesign. It was pretty fun!

  11. Elizabeth Fortin says:

    Thank you so much Stephanie for explaining this so simply and clearly. Also thanks for including an explanation for the ‘neutral’ category. I have honestly struggled for months trying to achieve this for a Likert type series of questions I included in some survey research I have undertaken. The result looks great.

  12. Jill Lipski Cain says:

    Thanks to you and Ann Emery for the inspiration and clear instructions! I re-created a blocky chunk of 100% stacked bars to a beautiful diverging one that really pops out the differences in the data. The chart I made also only had Yes, Maybe and No; and it made sense with the data to have the center between Maybe and No. Looks awesome.

  13. Jenna Boyer says:

    I just used this technique yesterday for the first time! So helpful, thank you for the step-by-step directions.

  14. Nico says:

    Well, I found an alternative method, which I find more elegant, and which worked very well for me!
    The trick is to (1) invert the sign of the data that will be on the left of the graph and (2) format these cell so that the negative sign doesn’t appear.

    In the example above:
    1. all data for “agree”, “strongly agree”, etc, should be changed e.g. to -40%, -30%
    2. format cells with negative data using Custom number format: for percentage use 0%;0%;0% (or 0.0%;0.0%;0.0% if you want one decimal point)
    3. create chart, chose Stacked Bar graph
    4. Edit vertical axis, go to Ticks, and check Axis Label to Low

    Et voilΓ . Does that work for you?

    • Sean Cameron says:

      Thank you, Nico! I found this method to be perfect. I couldn’t get the other method to align on the split between disagree/agree and this solved my frustrations!

    • jared says:

      I think this solves my problem. Thanks, Nico. But then I guess I don’t know how to do your solution..

  15. Katharine Tjasink says:

    Hi Stephanie. My colleague and I have been using this method for 5-point Likert Scale questions. We found that if we divide the neutral category by two and then ADD it, it shortens the bars of those questions where a neutral category was selected versus those where the neutral category was not selected by any respondent. We found that if you divide the neutral category by two and then SUBTRACT the number from each buffer, the bars remain the same length.

  16. Katharine Tjasink says:

    Your method, that is!

  17. Pam says:

    Great info! Now how do I do it with a neutral category (ie 5 point likert scale)??
    cheers pam

    • Stephanie Evergreen says:

      Hi Pam, Read through the comments here. Basically, you would split neutral in half and put each half on either side of the divergence. I don’t think it visually works quite as well, but give it a try!

  18. jared says:

    Thanks, Steph, for such a great help.I got one problem though. When I calculated the buffer, it turns out a negative value and the midpoint (0%) of the graph is no longer between the agree and disagree mark. I am anticipating for a prompt resolution to my problem. Thanks!

    • Stephanie Evergreen says:

      If your buffer is coming up as a negative value, I’m guessing your formula to calculate your buffer is incorrect. I’ll take a quick peek if you want to email it to me.

  19. Lia Kelinsky says:

    How would you deal with a Likert scale that includes uneven numbers? Let me expand…I had a scale 1-5 (with 5 being a lot, and 1 being not at all — the remainder of the numbers were not given a descriptive word). Given this, 3 is right in the middle of positive and negative. Thoughts? Thank you!

    • Stephanie Evergreen says:

      Check out the previous comments on how to set this up with a neutral category. It will work but I don’t think it looks quite the same.

  20. profray says:

    I like the method, it works fine for me. I have questions that have two parts, (a) and (b) and want the pair of bars to be grouped so they are closer together and then 2(a) and 2 (b) are paired together. I could throw in a dummy data line and make it all white, but is there another way with spacing and separation that would make this work? Thanks

    • Stephanie Evergreen says:

      Not sure I follow your description here. Draw out what you want on paper and email it to me at stephanie at StephanieEvergreen dot com and I’ll take a peek. Chances are high that if you can draw it on paper, you can make it in Excel.

  21. Mark says:

    In the last stage you say that you added in your survey questions as data labels. Could you please explain how you did this. Do you mean that you inserted a separate text box for each item (which i find very hard to position), or is there a simpler way to do it? Many thanks for all the knowledge that you are sharing. I have struggled with Excel chart defaults for years and really appreciate all your tips on how to get it to produce much better visualizations.

    • Stephanie Evergreen says:

      Hi Mark, I think I did use text boxes here because I wanted to be able to justify the text. Add them last and make sure you use the Align buttons to get them perfect.

  22. Mark says:

    Many thanks!

  23. Sara Wood says:

    I just had to share this visualization from IDV Solutions that takes the diverging vertical for a great effect!

    • Stephanie Evergreen says:

      Interesting! But the vertical and diagonal text make me think it would be better horizontal. Cool, though. Thanks for sharing!

  24. Jeff says:

    What font have you used in this chart?

  25. Jaci says:

    Hi Stephanie
    I noticed that some of your graph examples order likert scales strongly disagree on the left to strongly agree on the right; while others are presented with data ordered strongly agree on the left to strongly disagree on the right. Is there a generally accepted ‘best practice’ in terms of data order?

    • Stephanie Evergreen says:

      I think different schools feel pretty strongly about this but in this particular case, whatever sticks out on the right gets the most attention so I order with that in mind.

    • Jon Peltier says:

      I didn’t notice the inconsistency in this article. Contrary to what Stephanie says, I think I have almost always seen negative (strongly disagree etc) to the left and positive (strongly agree etc) to the right. I would need a very good reason to reverse this intuitive direction of the axis.

      • Stephanie Evergreen says:

        Case in point, Jaci.

        Research shows that there are order effects, where people prefer the left side of the scale. So some survey designers think it is perfectly okay to order with that effect in mind. Others are more strict, like Jon. A quick Google search on “Likert scale direction” will give you a sense of the discussion. I have seen both in my oversight of research as part of an institutional review board. But my point is that the order in which the questions are asked does not have to be the same order in which they are reported. Do what’s useful for your audience, in support of them understanding your main point.

  26. Fiona says:

    Thank you Stephanie. This was really useful.
    I have one quick question – how did you get the “white spaces” in between the categories?
    Thank you.

  27. Wouter says:

    Stephanie, thank you for this. It proved very helpful for the analysis I’m doing for my dissertation.

  28. Andrew says:

    Hey Stephanie, thanks for the tutorial. I used this in combination with your post on 508 compliance to make a hybrid (blue/red one that is just below “Use color plus something else to communicate”). I’ve pretty much been able to replicate it, but my only issue is the bars are showing up in the reverse order vertically from how they are listed in the cells and I cant figure out how to make it switch.
    For example, “I could eat a whole block of cheese” on the 508 post appears on top, but for me it appears on the bottom of the chart, and vice versa. In my sheet A2 is cheese, A3 is nap and A4 is coconut, so I don’t understand why they show in reverse on the chart.

    Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *

RT @LizTweets: Use the free StateFace font from @ProPublica to incorporate tiny US state icons into your text. How cool is that?! https://t…

RT @EvanSinar: Using The Gauge Diagram for Qualitative Data Visualization @evergreendata #dataviz…

@cisey you're welcome! So fun!

5 hours of detention by immigration cannot keep the #dataviz from #Canada.

RT @net2van: #Qualitative Data Visualization: The Gauge Diagram When numbers aren't a fit for your #dataviz @evergr…

Audible groans when this slide came up at #evalYOW17

RT @katiedrumm: Excuse me, GMG is hiring a graphics and data viz editor. Please RT and tell your talented friends:

So much fun keynoting & workshopping #dataviz at #evalYOW17