Overlapping Column Charts: A Quick Actual v. Goal Comparison

Hello there! I’m writing you as a follow up to a workshop I recently facilitated with Nicole Huggett, MSW, for the Arizona Evaluation Network in Phoenix. A big focus of our time together was spent on covering visualization options for comparing goals and pre-post results.

One of the popular charts we discussed were overlapping column charts and how they can be used to compare actual performance to goals. Since the workshop, I have found overlapping column charts to be very valuable data visualizations for this – so much so that I knew I had to share the steps publicly (OKAY, I also kept getting asked for the steps, so I knew writing it once would save us all some time!).

Although I already shared when you might use this chart, the particular scenario I was to set is related to survey participation. Specifically, one community organization needed a quick way to determine which years they met (or didn’t meet) their survey participation goals. An overlapping column chart served as a great way to for project managers to determine just that in a matter of seconds.

Ready to make one yourself? Awesome – let’s do it!

To get started, select your data insert a 2D Clustered Column Chart.

Excel, we love you so, but you do some weird stuff. To fix the data, right click and choose select data. Go ahead and delete the year series (oh yes, we’re going to delete lots of things!), select Goal and notice the x-axis is empty…go ahead and click this button and highlight the four years. Voila! Your Goal Series is now included, and you should have two columns in your Excel window.

Next, let’s get these columns on top of one another. To do that, we are going to right-click the Actual Column, select Format Data Series (get familiar with this area of Excel – it’s crucial to a lot of your changes!), and change the axis from Primary to Secondary. The column you want on top is the secondary…and the column you want on the bottom is the primary.

Now that we have these on top of one another, let’s adjust the gap of the Goal column. You can play with the settings to make it look right but I’d say at least down to 75%.

To start to clean this up (it’s still confusing right now!), let’s right-click the Actual column (Excel should allow you to select all of them) and Add Data Labels.

From here on, it’s really turning your chart from a Basic to Bomb Chart (check out this example of how to make yours look awesome). You want to pay special attention to fonts (both the type and size), colors, unnecessary noise (yes grid lines, I’m talking about YOU), and, of course the title! It’s here where you want to leverage data visualization best practices to really get your reader’s attention.

After you’ve made some simple changes, your overlapping column chart it should look something like this:

One thing you might notice is we don’t know what the goal was from looking at the chart – and that’s OKAY. This is really intended to give high-level insight. In other words, was the goal achieved or not? Whether this is as much information as your exec team needs, or you want to create a dialogue, I highly suggest this minimalistic chart for easy actual-to-goal comparisons!

Want to know how to do this in Tableau? Tune in next time and don’t forget to check out my posts on how to Getting Started with Tableau.  

I stopped blaming Excel for my basic data visualizations. Here’s when and why.

Early last year I realized my research findings only meant something if I involved the right people. And, to do so required me to be a creative and adaptive evaluator. Around the same time, I realized that I could have all the answers, but without a good way to communicate them, no one would actually notice.

Screen Shot 2017-04-02 at 8.32.46 PM.png
Look how easy it is to be basic!
I started searching for solutions. I found blogs by industry celebs (Ann K. Emery, and Stephanie Evergreen, to name a few) and began to pour over their archives. I found a lot of cool stuff. My world was quickly shaken when I realized that meant APA formatting might not be the best way for my voice to be heard (my condolences to the die hards, there is a time and a place). But, the big takeaway was that I figured out that, “Yes, it sucks because Excel made me do it,” was a crappy excuse for not visualizing my data in a better way. I learned to stop accepting the norm. You see, the purpose of Excel is to be a tool – not to do all my work for me because guess what? I’m the expert! I know those gridlines are garbage (and unnecessary), and that I should really figure out the RGB numbers for my brand.

So when did I stop blaming Excel? When I started valuing my data visualizations as more than entering data, selecting insert, choosing between a column or bar chart, and dragging it into my equally as boring report. When I realized that sweet visualizations in Excel just meant more thought and a few extra steps.

Screen Shot 2017-04-02 at 7.13.36 PM
I stopped accepting default settings!
Need an example? Check out Basic to Bomb Column Charts! Now go…find your inspiration for better visuals!

Screen Shot 2017-04-02 at 8.53.49 PM
Not too bad for a few extra clicks…