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:
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.
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.
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.
Overlapping bars show surplus and shortfall so well. (What a tongue twister – good thing this is just a blog post.)
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.