Improving data visualization: No more excel data tables!

Simon Hiltebeitel

Improvement Advisor, USAID ASSIST Project/URC

Excel data tables are awful. Still, we often use them when presenting time-series charts because they are a convenient way to display the denominator for percentage or rate indicators. Why do we need the denominator? Two reasons. First, the denominator communicates the scale either of the intervention or the sample. Second, variation in the denominator might explain some of the variation seen in the displayed indicator.

For example, a time series may start with very high value, drop suddenly, and then climb back up to 100%. It looks at first like things were fine, then worsened, then slowly recovered, but closer examination of the denominator may reveal that the initial high value was based on data from only five patients, whereas the subsequent months were for thousands of patients. Including the denominator helps tell a different, more complete story.

So we include the denominator (and also the numerator). But Excel data tables aren’t a great solution:

Time Series Chart Before

  • The formatting takes up a lot of space. Look at that huge blank area between the axis label “Percentage” and the chart itself.
  • When you have a lot of data points, the numbers in the data table overlap or are too small to read.
  • Even if the audience can read the data table, it’s actually quite time consuming for human beings to read and interpret a long list of numbers—that’s why we use charts in the first place!

Any time that your audience has to spend trying to work through understanding a confusing, hard-to-read chart is time they’re not thinking about your message—the story you’re trying to communicate. Ultimately, if the chart is too confusing or hard to read, they’ll give up and your message is lost.

What can we do instead? We want to communicate the denominator value, but do we need the numerator? Not really. It’s possible to approximate the numerator knowing the denominator and the percentage. How can we represent the denominator so it’s easy to understand its variation quickly?

Times Series Chart After

I think this approach is much easier and faster for audiences to process (although presenters may have to initially explain how the two charts relate and that they line up vertically). We sacrifice a little precision to allow our audience to quickly understand the big picture, which seems like a good trade off to me. What do you think?

If you’d like to try out these charts for yourself, you can use this template.


Related Countries: 


Speaking of data viz approaches, I wonder about the possibilities of presenting improvement beyond run charts. I wonder about the other ways we can tell the story of improvement through ways like participatory data analysis with improvement teams or wordle to capture salient terms in patient-provider interaction that could be key toward retention of patients.

Check this blog out:

I think this is a very useful improvement to our runcharts! Especially when you start to have a large number of data points in the runchart, we may find ourselves spending too much time trying to make everything fit in, only to realize very few people actually read those small numbers. The denominator bars give a good enough sense of their magnitude, at first sight, which is really what is needed in a graph. Thank you for the template!

Thanks for the kind words Jorge!

And thanks for sharing those resources Sid. There's some great information there, in particular the fantastic work of Ann K. Emery. Her blog is a great resource for anyone interested in data visualization.

Thanks very much for presenting an alternative to presenting excel run charts, Simon. For presentation purposes, this definitely seems better. So, for QI teams collecting data to report, would they also use this format instead of the run chart format? Doesn't that mean needing to reteach the teams since they have already been taught how to plot run charts? Annie

Thank you so much Annie for bringing up the distinction between different contexts in which data is presented. The intention here is to make it easier for stakeholders who are presented many run charts in a PowerPoint presentation to understand and process data quickly. Individual QI teams are best off using a format that makes the most sense to them and helps them best tell their story. If this format, using a column chart to replace the data table under a run chart helps them make their story more clear, that's great and they can use it. If they find it confusing, using a data table works. The primary focus for individual teams should be to plot data, highlight what changes they made and when, and explain whether they believe those changes led to an improvement. Either way of presenting denominators is fine, as long as they are included.

Facebook icon
Twitter icon
LinkedIn icon
e-mail icon