Down With Gauges!
Using gauges for management dashboards is a mistake. Gauges hide information that managers need and
waste space in reports.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
It's possible to carry an analogy too far.
Dashboards for managers are logically similar to dashboards for cars.
Both types of dashboards provide key information about current performance. But if you
follow the car-dashboard analogy too far, you'll deprive managers of key information
That's the problem with gauges for management reporting.
Gauges Hide Trends
Gauges have several problems that should concern most managers. The
key problem is that gauges hide trends.
In a car, trends don't matter. If you're ten miles over the speed
limit, or if your engine temperature is too high, that's all you need to
know. But in business, trends do matter.
To illustrate, the following three Excel charts
show about the same results for December as the gauge. But each chart shows
twelve-times the information in about half the space.
|Interpretation: After a long struggle, the turnaround might be working.
Are we on the right track? Let’s keep doing what we've been doing
recently, and see what happens in January.
|Interpretation: We've been solidly in the green until
December. Then we crashed. Perhaps we have a problem with the data.
Or perhaps our recent changes were a mistake. Whatever the problem,
we need answers now!
|Interpretation: We’re performing as we always do. But expectations keep rising.
What's driving that trend? Are expectations too high? Eventually, we
need to research this issue.
Again, December performance is identical for each of these three
Excel charts, and about the same as the gauge. But each chart tells a
different story. And each chart suggests that different responses are
As the interpretation for the last chart mentions, gauges
also fail to show the trend in our opinions about the levels of
acceptable performance. To illustrate, a score of 4 was borderline green
performance in January; but by September, that same performance had
become borderline red. Are these opinions appropriate? The gauge doesn't
provide enough information to even suggest this question.
In short, the charts reveal information; the gauge conceals it.
Excel, Gauges, and Charts
Excel doesn't support gauges directly. This gives other companies an
opportunity to sell software to fill this gap.
I suspect, in fact, that one of the reasons that gauges have been
promoted so widely is that other software publishers see an opportunity
to sell their programs to replace Excel for performance reporting. But
because managers are better off without gauges in the first place,
there's little need to buy programs to generate them. And there's no need to replace Excel for
That is, by avoiding gauges, managers can improve their dashboard
reports and probably avoid the cost of buying 3rd-party dashboard
How to Create a Better Chart in Excel
It's easy to create the chart that generated the three
earlier examples. Here are some guidelines:
- Start with a line chart with three lines: actual performance, the
red gauge values, and the incremental yellow gauge values. Make sure that the
lines use this plot order in your three SERIES functions:
1 - actual performance
2 - red values
3 - yellow values
- Format the entire Plot Area green.
- Select the line with red values and change its type to a stacked
area chart. To do so, choose Chart, Chart Type, and complete the
dialog. Then do the same for the line with yellow values.
The chart's colors aren't standard Excel colors. If you use Excel 2007 or after, this isn't a problem. But if you use Excel 2003 or before, you can learn how to
assign your own colors to worksheets, see
Display Any Colors in Excel 97-2003.