My New Favorite Graph Type: Overlapping Bars

Why have I fallen in love with this graph type? I think its because its such a great way to visualize the comparison between two things, when one is inherently a part of the other. In recent client projects, I’ve used these to show actual v. budgeted amounts. Or individual v. aggregate scores. Or those we’ve surveyed v. the entire population. Progress toward a goal or against a benchmark. How funny my boyfriend thinks I am v. how funny I really am. You get the idea. AND, best of all, making this graph is a piece of cake.

Your data table will look pretty standard:

OverlappingTable
You’ll highlight all of this data and insert a very standard, typical, clustered bar chart. One of these:

Overlapping1
The trick is to get one set of bars on top of the other, kind of nested inside. To do that, you’ll click on the set of bars you want in front. Then right-click and select Format Data Series. In the box that pops open, click the radio button next to secondary axis.

Overlapping2
Moving a series to the secondary axis essentially means it will layer in front of the original series (which is still on the primary axis). The secondary series pretty much occludes your other set of bars, doesn’t it? We want the bars in the back to be wider, so the ones in front look sort of nested inside. Getting the bars wider involves a super geeky technical-sounding Excel move – reduce the gap width. Excel means the width of the gap between two of those blue bars. Why not just call it Make the Bars Wider? I don’t know. Maybe Excel secretly hates you. Regardless, to reduce the gap width, click on the little bit of blue bar you see in the background with the Format Data Series window open. In that same window, you’ll see a slider next to Gap Width. Crank it to the left.

Overlapping3

You’ll just have to eyeball the exact gap width until it looks good to you. See how it made the bars much thicker, kind of making them look like the own the orange bars? That’s the idea! We want the visual representation to reflect the relationship between the variables in the dataset.

Moving those original bars to the secondary axis means we also see a second y axis scale at the top of the graph. Make sure the minimum and the maximum of both y axes match perfectly. Then delete the one at the top. It’s ruling the orange bars but its redundant.

Finally, add a better font, some action color, and a succinct title.

Overlapping4

Overlapping bars show surplus and shortfall so well. (What a tongue twister – good thing this is just a blog post.) 


Read!

Check out even more useful graph types in my latest book, Effective Data Visualization.

You’ll find at least 4 other ways to visualize comparisons against benchmarks, plus my sought-after Chart Chooser handout.

This second edition even includes the largest collection of qualitative visualization options ever published.

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.

25 Comments

  1. These are great, they are also a snap to make it Tableau using a couple different methods. Dual Axis makes it a snap. Thanks for Excel details.

  2. I love this for a single site compared to overall. The one thing I don’t like is that I want the single site to show up first in the legend, but since it is the second axis it shows up second in the legend. There are ways around it, but it’s tedious, and not worth the effort. The other caution is that when you use two axis things can get messed up if you need categories in reverse order. Be sure to reverse order categories on both axes. https://dl.dropboxusercontent.com/u/2808007/Overlap%20Barchart.docx

  3. I love this so much Stephanie! Perfect timing too, as I volunteer for a local community group that’s trying to get some improvements to one of our streets, and I think I’m going to re-do a frankly horrible visualization done by the city with this method to explain something soooo much better. 🙂

  4. I have been using the overlapping bar model for a few years. I think I learned it from chandoo’s web site. It is HIGHLY effective for showing planned vs actual on just about anything!!! I used it to show how far we where over budget, which was hidden in some tabular spreadsheet data. When I showed the chart ( very similar to your example) the project executives went silent. It was funny and scary all at the same time , as they realized that this information had been there ( for a long time) and they did not realize it. – there was immediate action on the “issues” the next day….this chart is a powerful tool!!

  5. Very nice idea – works well for comparison of two numeric quantities (last year vs this year …) thanks for sharing

  6. Stephanie – you say you need to get the two axes lined up carefullly. My understanding is that if you delete the secondary one from the display it will automatically be aligned to the primary, that is to say it will have the same minimum and maximum values. This takes account of future changes to the data, especially if the primary axis has the maximum set to “Auto”. So you should not need to do any fiddling about, just go straight to deleting it.

  7. Yeah, I finally found a great opportunity to try this graph type! I’m working on a remake of an existing clustered bar chart (so I don’t have the Excel file, just a chart in a PPT slide). The problem is that I wanted to show the y-axis categories in reverse order. When I checked the box for this (in the formatting dialogue box for the y-axis), one of the datasets didn’t change. So, my bars were then mismatched! In the context of your example, my “budgeted” and “actual” values were not aligned with the correct “bucket.” Bucket A now shows the correct “budgeted” value, but with the “actual” value for Bucket 6! While I can go and recreate this on a spreadsheet and work it out, I was wondering if there might be an easier way working right from the chart in PPT.

    1. Stephanie Evergreen

      Yes! So when one set of bars goes on the secondary axis, it gets a new horizontal and vertical scale. Make those visible in Chart Tools>Add Chart Element. Just make all axes visible, then adjust them so they match. In your case, you’ll want to make both categories in reverse order.

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.