Normally, I wouldn't expect to be reading an "atlas" for work. But, when Evelina Judeikyte…
Do you ever have a conundrum and think:
Surely someone else has already found a solution for this?
And then you search and realize that no one has? (Or, at least, no one’s posted a solution where Google can find it?)
Well, friends, I wrote this post in case someone, somewhere, at some future moment, finds themselves facing the same challenge I recently had.*
One of my clients needed help developing bar graphs of their study results. On a surface level, the ask seemed pretty straightforward…he needed bar graphs, with error bars representing 95% confidence intervals** and each bar labeled with its value.
Then I actually sat down with Excel. And, like (for me) anything athletic, it wasn’t as easy as it sounded.
When you use Excel to add data labels and error bars to the same graph using default settings, they look terrible:
The biggest problem is that the data labels often overlap the error bars, which interferes with readability and just looks messy.
The bottom left option, with data labels inside the base of each bar, generally avoids the overlap issue. However, this is an uncoventional location for bar chart data labels. Typically, the data label position corresponds to the magnitude of the data (e.g., tall bars have labels placed higher; short bars have labels placed lower).
Surely, I thought, someone has already worked out an elegant solution to this design problem. I did a Google image search for “bar chart error bars labels” and learned:
- Few bar graphs have both error bars AND value labels.
- Those that have both are not in the ballpark of “elegant.” Many use the defaults I showed above. A few other designs involve moving the value labels, to either above or next to the error bars:
These are *better* than the defaults, but they’re still not ideal. It becomes less clear what’s being labeled (bar vs. error bar). Also, in the left example, the difference between data values gets distorted…notice the big gap between the “6” and “8” labels. And, design-wise, the right example just looks a little off, with the lack of symmetry and the lack of alignment between the data labels and the x-axis labels.
So, what’s a girl to do?
Well, the solution THIS girl came up with was to fill the value label text boxes with color to, functionally, cover the error bars. Specifically:
- Use the “inside end” data label placement, and fill the data label text boxes with the same color as the graph’s bars, or
- Use the “outside end” data label placement, and fill the data label text boxes with the same color as the graph’s background.
Here’s a screenshot of me selecting and filling the data label text boxes:
And here are the final versions of the graphs, with a little additional work to improve contrast and readability:
Both of these solutions may still require some manual tinkering. For example, I had to move the “2” data label so that it didn’t cover one of the ends of its (relatively small) error bar.
So, there you go. Not a complicated solution, but one I hadn’t seen elsewhere, so I wanted to put it out into the world.*
What do you think of the revised graphs? Comment below!
Looking for a solution to YOUR tricky dataviz design issues? Contact us!
* You’re welcome.
** Another potentially useful nugget for you: Excel doesn’t automate error bars for confidence intervals. Thankfully, Jason Abercrombie has a nice explanation of how to add the bars manually, starting around 8:30 in the video.