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 they need.
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, our company’s difficult 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 necessary.
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 dashboard reporting.
That is, by avoiding gauges, managers can improve their dashboard reports and probably avoid the cost of buying 3rd-party dashboard reporting programs.
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.
