For business users of Microsoft Excel Free guides and templates

Data Visualization

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

Gauges say little, and take up a lot of space. Use Excel charts instead.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, 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. An Excel chart showing a possible turnaround in progress.
   
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!
An Excel chart showing a decline in business performance.
   
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. An Excel chart showing static business performance.

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:

  1. 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
  2. Format the entire Plot Area green.
  3. 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.

 

Dashboard Gauges Are Still a Terrible Idea!

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.