Peltier Tech Excel Charts and Programming Blog

In A Gauge Chart That Works?, Clint describes his efforts to come up with a dial-type chart that isn’t as terrible as most. He comments that Stephen Few‘s Bullet Graphs are unfamiliar to most people, which is true, ans they’ve only been around for three or four years. Bullet graphs and most gauge-type charts are tricky to create and maintain, since they rely on a melange of chart types and additional chart elements to produce unusual visual effects. Clint’s boss wanted a gauge, but Clint didn’t want to work with ugly, bulky dial gauge replicas. Long story short, Clint designed the following gauge to show page views in a web site he was watching:

I’m not wild about the new gauge, but it isn’t totally awful. I’m not sure that a bullet-style graph with this color scheme couldn’t have been used (shown below without the text elements), but I’ll leave that discussion alone. I also won’t dwell on the observation that a bullet chart is really a gauge at heart.

On the positive side, Clint’s graph is linear instead of circular. Also, the new Excel 2007 feature is nice that allows arrowheads on series lines and error bars. (One of the few nice things about Excel 2007 charting, and not worth putting up with the problems.)

On the negative side, it’s a gauge. Gauges like this only show one point in time; there’s no historical context, and you can’t tell if your month to date numbers are on target to hit the total per month target. Also, what happens if the value goes to 11? Seriously, there is no provision for an out-of-scale measurement.

I’ve sworn off gauges myself. I’ve removed a speedometer tutorial from my web site, for at least a couple of reasons. First, it is a poor display device, for all the reasons stated above and elsewhere. Second, I would receive too many questions from people who didn’t understand enough math to customize it. It’s algebra and a little trig: find a tenth grader to help, people!

To overcome these issues, I returned to an old standby, the line chart. I use a category value to show days elapsed during the month, and vertical measures of page view targets and measurements. Colored backgrounds are made using stacked columns to provide a bad-medium-good scale across the chart, and dual scales, showing values and percentage of target, decorate the vertical sides of the chart. I also stuck with colors similar to Clint’s. Here is my non-gauge chart:

This chart takes up about as much space as Clint’s, but it adds the missing time component. I can visually extrapolate the line forward to the end of the month and estimate whether we’ll hit our target. Since Clint’s chart has some empty space, it can be shrunk; so can the line chart without too much difference in legibility:

Through the use of (ahem!) clever formulas, these charts can be made to adapt when the value exceeds the target:

When I have a chance to pretty it up, I’ll post a tutorial showing how I created this chart.