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.