Blog

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:

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!

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

  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: http://bl.ocks.org/CodeXmonk/6262477 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!

    http://dx.doi.org/10.6084/m9.figshare.873604

  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.

Leave a Reply

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

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

Lovely discussion w/ @VivianT88 abt work at @wtgrantfdn to connect research to policy & practice. http://t.co/aBV8q6dw6Z let's include viz!

RT @ravikarkara: @evergreendata absolutely lovely connecting - lots to do Data Visualization - World We want http://t.co/lQ1JoaYpHS

RT @zsuzsiness: Excited to hear convo w/ @evergreendata at @RockefellerFdn on data dashboards!

@ravikarkara you could maybe use this? http://t.co/cfsVB9l9wL

Humbled by the honor. https://t.co/Fq1d9l2jIo

RT @jorgeluis500: I transformed a paired bar chart I had in #Tableau into this. Via @acotgreave http://t.co/lUtbLmawPg http://t.co/FEelZk8l…

Loving the interactivity with such a simple graph http://t.co/cfsVB9l9wL Try it!

@PassengerShame how am I going to reach down and get my laptop out of my bag without making contact? http://t.co/qlvwo9SGF1

@MariuszKanicki @AllisonGister I'd call that a document and not a slidedeck - even if it's made in PowerPoint.