SubscribeHack together a Gauge Chart in Excel without sweat
Gauges are a familiar metaphor, everyone can understand them, you can see them everywhere - near your stove, ac, car, gaming console, pc - you name it. So, when you are preparing a chart to tell a point, gauge chart like the one above can be effective. (I know charting pros like Jon Peltier wouldn’t agree with this and prefer speedometer charts only in cartoons.)
Unfortunately Excel doesn’t have a gauge chart as a default chart type. They of course have a 3d line chart, but let us save it for your last day at work. Meanwhile we can cook a little gauge chart in excel using a donut and pie (not the eating kind) in 4 steps.
Click here to download the excel speedometer chart template and play around.
1. Have your data ready
For a typical gauge or speedometer chart we need to have these 5 different values
- What the is gauge size?
- What is the range of Red zone?
- What is the range of Yellow / Amber zone?
- What is the range of Green zone?
- What is the value to be shown on Gauge?
In our case we can use typical values like you see on the right.
2. Make a doughnut chart using Red, Yellow and Green Values & Pie Chart
This is a simple step, just select the data for speedometer and click on insert chart and select “Doughnut” as chart type. Make sure you have added a data row in the end with value as 100 to get a gauge with 180° or 50 to get a gauge with 270°
Making the pie chart
This is another simple step, easier than eating pie. Just create a pie chart with 3 values,
- Gauge value
- 1
- 200 minus gauge value + 1
3. Blank out the bottom half of doughnut and pie charts
Just select the blank portions of doughnut and pie charts and set their border & background colors to none.
While you are at it, adjust the colors of donut portions to red, amber and green (or your favorite speedometer colors)
4. Finally, put the pie chart on top of donut chart
Just drag and drop the pie chart on the donut chart. Tweak the colors if needed, adjust the “send to background” / “bring to foreground” settings.
That is all, you will now have a neat looking gauge / speedometer chart to show off. Don’t forget to download the Microsoft excel gauge chart template
Also try: Thermometer chart, Partition chart, Chart around the clock, Min-max charts
| Delicious | Stumble it |
« Prev | Home | Excel KPI Dashboards - Adding Micro Charts [Part 4 of 6] »
Have an Excel Question?
Trackbacks & Pingbacks
- Pingback by Business blog » Blog Archive » Hack together a Gauge Chart in Excel without sweat on September 9, 2008 @ 1:20 pm
- Pingback by Making Pie-charts look Sexy - The CNN’s tax burden analysis chart | Pointy Haired Dilbert - Chandoo.org on October 29, 2008 @ 1:47 pm
- Pingback by Links for September 12, 2008 | PTS Blog on June 20, 2009 @ 3:19 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
Join Our Community
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
@Jon, completely agree with you, dial charts are prone for misuse. I wanted to see how readers would take this.
Despite being a poor presentation alternative, dials have been a well known metaphor in real life and people can relate to them when used in moderation. I am hoping readers realize that this is one of the million ways to sell their story and use it only when needed.
“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
@all : I kind of anticipated a heated debate when I hit the publish button on this one. And sure it followed.
I had this in my mind when I wrote this post:
- Gauges are something that everyone can relate to
- They can make a good chart / single slide to tell one single point of a big story
- They may not be suitable for corporate / board room. But they are acceptable in other settings like schools, informal situations where you use excel to communicate ideas.. which is a thousand different places.
I am very confident that people dont misuse gauges for the very reason that they dont misuse 3d bar charts / 3d lines or several other outrageous excel charting options that are out there but you seldom see in a boardroom.
That said, I should have added a section in the post suggesting possible uses for this chart. I will keep this in mind going forward though
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.
@Somnath: I agree with you. When I wrote this article I barely knew combination charts. I learned them later on though.
A sample xls on the combination chart would b e really helpful. I tired creating one but was not getting the expected output.
@Ram… have you checked our combination charts tutorial ? http://chandoo.org/wp/2009/01/05/excel-combination-charts/
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.
@Alan…You are welcome. You may also want to consider using a bullet chart or some of the budget vs. actual charts to show the same information. I am saying this because guages are prone to misuse often.
http://chandoo.org/wp/2008/07/21/dashboard-bullet-graphs-excel/
http://chandoo.org/wp/2009/04/05/budget-vs-actual-charts/