August
28
2006
Or, boy I am I oblivious.
Eric Peterson lobbed this my way a week ago and I just noticed it a few days ago (I’ve been hard at work on a response).
His basic question, if I may restate it, is “What do I think about using a ‘tachometer‘ to display KPI data?”
The short answer is, I love to do this - when it’s practical.
If you work in Excel, it is often not practical because the level of initial work and maintenence needed to operate a custom chart element like this violates the efficiency rule (remember, that in order for reports to be truly effective, they need to be easily repeatable so that they can be delivered efficiently and on time). In other words, I’m lazy and it is, generally speaking, too much work for not enough return.
That being said, I’ve spent the past couple of days approximating the dashboard that Eric refers to (via Dashboard Spy and courtesy of Subaru) in Excel, to see if it could be done - with the proper wherewithal.
Here’s a screen grab of the finished product:
Ok, so it can be done.
Here’s a list of resources you’ll need to pull it off:
- Andy Pope’s Gauge Work Around
- John Peltier’s Speedometer Work Around
- Apps Pro X/Y Chart Labeler
- Excel Camera Tool
Scared yet?
Ok, here’s my file for the above picture, you’re welcome to try it out (no commercial use, please), but I make no warranty as to the accuracy or stability of it.
Long story short, custom dashboard elements like the above are cool, sexy and can effectively communicate important information such as KPIs, but hopefully you have a better platform than Excel to create them in (this is where something like Xcelsius becomes helpful).
Gack! There are some real problems with using speedometer charts to communicate:
* Doesn’t show historical context well (or at all).
* Dials show a fixed range of values (visitor growth could be well over 100%, what if you get slashdotted!)
* Hard to read at a glance–real speedometers work because you’re glancing at them every few seconds so you maintain a continuous awareness of their value. This won’t be that case with most corporate uses.
On the other hand, the spreadsheet you provide is a really nice clean illustration of how to make these advanced features of Excel work together.
Chris,
I agree, gauges do not typically show historical context (and you can get into trouble because of it). So a report using them is incomplete without some nod to providing that historical context. However, KPIs can be used without historical context if they are matched against a target value (ack! my conversion rate has fallen below 1%). If you extend your ‘every few seconds’ example from driving down the highway to every day or week or whatever period is appropriate for the KPI, it works just as well - the time frame is just different, that’s all.
When I do use these types of charts, I typically make sure to provide historical context in two ways.
1. If it’s a gauge, then it will have two ‘hands’ (pointers) - one for the current period and one for the previous period. This provides the immediate context
2. A time series chart of the data in the gauge provides the broader context.
Finally, thanks for the compliment!
-Clint
Clint,
I was just received a link with a wonderful detailed description of how to do bullet charts in Excel. Here it is: http://www.exceluser.com/explore/bullet.htm. Bullet charts are an alternative to gauges that convey information better.
Like gauges, they also will have people commenting “OMG! I didn’t know you could do that in Excel!”
Whadyathink?, Chris
Certainly an elegant solution. But I have this little voice in my head whispering ‘You’ll be explaining how to read this graph to the uninitiated from now until next christmas!’ Or I’ll have to add too much labeling to explain it and it will be overrun by chart junk. Have you used it effectively within your business? (e.g. minimal explanation & training to your clients on how to use and interpret?
Chris, upon further reflection, I would also say that color choice is critical to the success of the bullet graph (above and beyond the normal importance of color/shade choices in a chart) which can make it difficult to make them sing - especially in excel.
[...] unfairly criticizes himself for missing my reference to his work a few weeks back but responds with a cool tachometer visualization in Excel that you can download and use for your own key performance indicators. One of Clint’s readers [...]
[...] Then I explained all that was wrong with using speedometers in a business context and beyond that how hard they are to create and maintain. [...]