charts

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!

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

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=""> <strike> <strong>