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:

DivergingStackedData

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:

DivergingFinal

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.

55 Comments

  1. 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!

    1. Stephanie Evergreen

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

    1. Stephanie Evergreen

      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.

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

    1. Stephanie Evergreen

      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!

      1. I am having some trouble, I have likert data, but I have one section of data that is neither one or the other. I cant seem to figure out how to have the , strongly aggree, somewhat agree, neither agree or disagree, somewhat disagree, strongly disagree…. I fear I might be loosing my mind but just cant get this sorted in Exel, and help would be appreciated!!!

        1. Stephanie Evergreen

          Read through the comments, Emma, because some other folks have asked and answered that question. Problem solved! Good luck!

  4. 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!

  5. 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%?

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

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

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

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

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

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

  10. 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?

    1. 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!

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

    1. 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!

  12. 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!

    1. Stephanie Evergreen

      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.

  13. 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!

    1. Stephanie Evergreen

      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.

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

    1. Stephanie Evergreen

      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.

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

    1. Stephanie Evergreen

      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.

    1. Stephanie Evergreen

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

  16. 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?

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

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

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

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

  18. 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?

    1. Stephanie Evergreen

      Yes, Andrew, Excel likes to do the opposite of what you want 🙂 Right-click on the axis with the labels and select Format Axis. In that menu, you should see a box to check for categories in reverse order. That should do the trick.

  19. Hi Stephanie, would you have any advice for how to display ‘treatment’ and ‘comparison’ values using a similar stacked bar method? I think the stacked bar method would be a great way to visually show responses to questions (EX: “I feel positive at school.” using a 5-pt scale from Strongly Disagree to Strongly Agree), however, I’m not sure what would be the best method to compare responses between 2 distinct groups. I’m thinking of pulling out some response highlights and doing two separate charts for each group but I’m worried that it will be difficult to visually compare. If you have any other ideas I’d love to hear them! Thanks for the post

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.