Improving data visualization: Using overlapping bar charts to present actual vs target data
In 2016, the USAID ASSIST Uganda team reported some impressive results that showed 13 of 15 ASSIST-supported health facilities exceeding their six-month HIV counseling and testing targets. If you put their data into Excel and create a clustered column chart using the default settings, here’s what you get:
The blue lines represent the target value and the orange lines the actual number of people tested. This chart works, but we can do better. One problem with the chart is that, while it’s not hard to compare the orange bar with the blue next to it, the overall pattern that most of the orange bars are higher than the blue bars (i.e., the testing targets were exceeded) doesn’t really stand out.
Rather than using clustered bar charts, we can use overlapping bar charts to make the message of this chart more clear:
By making the bars overlap, it’s intuitively more clear that the two numbers (target and actual) are related. It also allows us to quickly see that all but two sites have exceeded their 6-month testing target. Adding the data labels highlights the actual number of people tested, and putting the legend to the side allows it to be in the same orientation as the data.
Lastly, we can make a few more improvements to highlight the story we want to tell. We can give the chart a more interesting title than “number of people counseled and tested who have received their test results.”
It seems to me that what we’d want to say when presenting this data is that 13 of the 15 ASSIST-supported health facilities are surpassing their 6-month testing target, so why not have a title that matches that message?
We can still include the old title that defines the data as a sub-heading, adding a main title in a larger font that highlights our message.
Lastly, six of the 15 facilities have not only surpassed their 6-month testing target, but they’ve already exceeded their annual target. That’s something we really want to highlight, so I re-ordered the data by the percentage of their six-month target achieved, ranging from Otuke having tested 278% of their six-month target to Oyam having tested 35% of their 6-month target. By putting them in order from highest percentage to lowest, I could add the green and red text below the chart that highlights where the facilities are relative to their targets. Compared to the first version of the chart above, I think this final version does a better job of highlighting key results and helping tell a clear story.
In the files attached are two Excel files. The “example target chart” contains the charts above so that you can see how they were constructed. The “target chart template” is set up to make creating this type of chart relatively painless and will hopefully work for most needs. If you’d like to learn how to construct this type of chart from scratch, Stephanie Evergreen has a great blog post about it here: My New Favorite Graph Type: Overlapping Bars.