Hack together a Gauge Chart in Excel without sweat

Chandoo, do you really want to encourage such poor presentation techniques?

I once had a tutorial that showed how to create a dial type chart, complete with colored regions, one or more needle indicators, and a numerical scale around its periphery. But the page received inordinate attention, and I realized the technique was prone to misuse and abuse, so I took it down.

I did recently post an example of a dial gauge, but only in jest:
Dial Gauge

“I am hoping readers realize…”

You have to (a) tell them once, (b) tell them twice, …

Then you have to suppress the urge to make such a protocol available.*

*For me this is the hard part. I posted my own dial gauge to show that it could be done (and to show how clever I was to do so). I’ve actually taken down a few pages that present ineffective visualization techniques, and there are a few articles I’ve written for other outlets besides my own that I wish I could take back.

I agree with Jon. You’re providing a tutorial and an Excel file, do you really people expect to critically reflect for the fraction of a second that it takes to download that file? Gauges and Excel are a bad combination, because they make people use gauges for things they’re really not good at. There are undoubtedly good uses for them, but I have yet to see a gauge in a business graphic that makes sense.

I assume Chandoo just wanted to show a technique for special charts in Excel.

But I agree with Jon and Robert. A lot of readers of Pointy Haired Dilbert are probably looking for ideas and techniques in Excel that are useful for their daily work. That is why showing how to create a gauge chart in Excel was probably not the best choice.

On the other hand: maybe Chandoo just wanted to start a discussion…

Anyway: For everybody who is not that familiar with the discussion about gauges on business dashboards and better alternatives to display single values, please find a couple of links that might be interesting for you:

Don’t use gauges:

http://www.perceptualedge.com/articles/dmreview/dashboard_design.pdf

http://www.exceluser.com/dash/gauges_no.htm

Use bullet graphs instead:

http://www.perceptualedge.com/articles/misc/Bullet_Graph_Design_Spec.pdf

And here is a very good tutorial how to create bullet graphs in Excel:

http://www.exceluser.com/explore/bullet.htm

This blog Is very informative , I am really pleased to post my comment on this blog . It helped me with ocean of knowledge so I really belive you will do much better in the future . Good job web master .

With all due respect to Jon, there is absolutley nothing wrong with using this technique in an Excel dashboard. It is not a poor presentation technique when used properly. I am often asked to create such charts for corporate dashboards. If the argument is that you should not provide the info to inexperienced users because they may misuse it, then I would ask how often are pivot tables misused? Should pivot tables be removed as a standard feature of Excel? No, you cannot control how people use this information, you can only strive to provide the most accurate and detailed info as possible.

Actually, you do not need to create a separate pie chart. You can simply add the second table (Gauge Value,1, 200-(Gauge Value+1)) as a new series on the doughnut chart, and change the chart type to pie chart.

A sample xls on the combination chart would b e really helpful. I tired creating one but was not getting the expected output.

This thing is a life saver! Thank You so much for this excel file. I work for a Major Apparel company and we are developing a dash board. The guages are a good measurement because it shows when an area is moving into the red zone and needs attention.

This will be very helpful.

Thanks again.

There is a better way to do this with one chart instead of two. Make the two data series described above and put them in the same Donut chart. After formatting the donut part, click on the second data series on the graph (should be the outer ring), Right Click and choose “Chart Type” then choose Pie. This makes one overlay on the other perfectly. It also eliminated the hassle of seeing which chart is on top of the other and/or aligning the two.

I agree that these kind of gauges can be easily misunderstood, as they provide purely a snapshot of a single instant in time. However, they do give a very clear visual impact of that time period. What I have done in the past is to use this type of gauge in conjunction with a time period line chart to show the current status and the trend. Just make sure you clearly state that the gauge is for a single time period!

John – how do you get the GV data label to be the GV value not the GV+ value?

Mike: you just need to set the name of the name data series to be the GV value (at least that’s how I did it).

I found this really useful, having spent a few hours looking for something comparable this is certainly, by far and without hesitation the best excel template I’ve found. It’s much easier to use than alternatives and more intuitive.

Are speedometer guages bad presentation? All the points above have merit, but I’m relfecting and thinking that a simple pie chart has the same limitations (i.e. it’s a single snapshot in time, usually a month or a year) and I can’t see the difference.

What’s much more apparent reading the discussion is the point that bosses too readily jump to the wrong conclusions based on the data that’s being presented. I’d respectfully point out that that is not the data, or it’s presentation’s fault.

Excellent work chandoo, your site is bookmarked!

Memamu -
 
Correct, gauges are not effective means to represent information. To display one value, they take up a lot of room. They show a single point in time, without any context or history. The use of an angle to show data is not as effective as the length of a bar or position of a point along an axis. About all you can say for certain in Chandoo’s dial gauge is that the color intersected by the needle is yellow, so it’s hardly better than a categorical measure.
 
Pies are also not particularly effective. They represent a single point in time. Using areas is as ineffective as angles for portraying values, and colors must be included, so nobody viewing the chart better be colorblind. If you really want to make it ineffective, make it a 3D pie chart.
 
You can replace the gauge with a line chart, which enables you to show today’s point at the end of a series of points. Replace a pie chart with a bar chart.
 
The sad thing is that people (like Dilbert’s pointy-haired boss) see a pie or gauge in all their colorful glory, they think, wow, that looks good. People see pie charts everywhere and don’t realize they can’t read them effectively. An effective data display is often boring, and it should be, to avoid distractions.

Hi Chandoo, I read your blog frequently – thanks for creating such an excellent source of information.

I have been working with gauges a lot. In know that to using them, there is a lot of resistance amongst charting gurus, and I guess I have to appreciate what Jon Peltier has to say about them. However, my clients really DIG them, so I will probably continue producing them.

Anyways, I find that it is much more robust way to create these gauges in one chart – starting out with a donut chart with two series, then changing your needle to a pie chart and you’re basically done. I have a small blog I only recently started, where I am giving a short explaination for whoever wants to see in a bit more detail. Hope you don’t mind me sharing the address.

http://www.blackbrick.nl/excel-blog.html

Keep up this excellent blog!

Cheers, Maurik