When it comes to present data in an understandable way in Excel, charts standout there. And there are few charts which are specific and can be used to present a specific kind of data.
A SPEEDOMETER [Gauge] is one of those charts.
It’s a kind of thing which you can find in your day-to-day life [Just look at the SPEEDOMETER in your car].
And in today’s post, I’m going to show you exactly how to create a SPEEDOMETER in Excel. But here’s the kicker: It’s one of the most controversial charts as well.
So today in this post, we will be exploring it in all the ways so that you can use it in your Excel dashboards when they actually required.
What is an Excel SPEEDOMETER Chart?
An Excel SPEEDOMETER Chart is just like a speedometer with a needle which tells you a number by pointing it out on the gauge and that needle moves when there is a change in the data. It’s a single-point chart which helps you to track a single data point against its target.
Steps to Create a SPEEDOMETER in Excel
Here are the steps to create a SPEEDOMETER [Gauge] in Excel which you need to follow.
As I said, we need to insert two doughnut charts and a pie chart but before you start to create a SPEEDOMETER, you need to arrange data for it.
In the below worksheet, we have three different data tables (two for doughnut charts and one for the pie chart). You can DOWNLOAD it from here to follow along.
- The first data table is to create the category range for the final SPEEDOMETER which will help you to understand the performance level.
- The second data table is for creating labels ranging from 0 to 100. You can change it if you want to have a different range.
- And in the third data table, we have three values which we will use create the pie chart for the needle. The pointer value is the real value which you want to track.
To create a SPEEDOMETER in Excel, you can use the below steps:
To create a SPEEDOMETER in Excel, you can use the below steps:
- First of all, go to Insert Tab ➜ Charts ➜ Doughnut Chart (with this you’ll get a blank chart).
- Now, right-click on the chart and then click on “Select Data”.
- In the “Select Data” window, click on “Legend Entries” and enter “Category” in the name input bar. After that, select the “Value” column from the first data table.
- Once you click OK, you’ll have a doughnut chart just like below.
- From here the next thing is to change the angle of the chart and for this right click on the chart and then click on “Format Data Series”.
- In “Format Data Series”, enter 270° in “Angle of first slice” and hit enter.
- After this, you need to hide below half of the chart. For this, click on only that part of the chart and open “Format Data Point” and select “No Fill”.
- For the rest of the fours data points, I’ve used fours different colors (Red, Yellow, Blue, and Green). At this point, you’ll have a chart like below and the next thing is to create the second doughnut chart to add labels.
- Now, right-click on the chart and then click on “Select Data”.
- In “Select Data Source” window click on “Add” to enter a new “Legend Entries” and select “Values” column from the second data table.
- Once you click OK, you’ll have a doughnut chart just like below.
- Again you need to hide below half of the chart by using “No Fill” for color and I’ve also added a color scheme for the labels. After this, you’ll have a chart like below. Now, the next thing is to create a pie chart with a third data table to add the needle.
- For this, right-click on the chart and then click on “Select data”.
- In the “Select Data Source” window click on “Add” to enter a new “Legend Entries” and select the “Values” column from the third data table.
- After that, select the chart and go to Chart Tools ➜ Design Tabs ➜ Change Chart Type.
- In “Change Chart Type” window, select pie chart for “Pointer” and click OK.
- At this point, you have a chart like below. Note: If after selecting a pie chart if the angle is not correct (there is a chance) make sure to change it to 270.
- Now, select both of the large data parts of the chart and apply no fill color to them to hide them.
- After this, you’ll only have the small part left in the pie chart which will be our needle for the SPEEDOMETER.
- Next, you need to make this needle bit out from the chart so that it can be identified easily.
- For this, select the needle and right click on it, and then click on “Format Data Point”.
- In “Format Data Point”, go to “Series Options” and add 5% in “Point Exploration”. At his point, you have a ready-to-use SPEEDOMETER (like below), just a final touch is required and that final thing is adding data labels and we need to do this one by one for all three charts.
- First of all, select the category chart and add data labels by Right Click ➜ Add Data Labels ➜ Add Data Labels.
- Now, select the data labels and open “Format Data Label” and after that click on “Values from Cells”.
- From here, select the performance label from the first data table and then untick “Values”.
- After that, select the label chart and do the same with it by adding labels from the second data table.
- And at last, you need to add a custom data label for the needle (That’s the most important part).
- For this, insert a text box and select it, and then in the formula bar enter “=” and select the pointer values cell, hitting ENTER.
- In the end, you need to move all data labels to end corners, like below:
Hurry! your First Gauge/SPEEDOMETER chart is ready to rock.
SPEEDOMETER – Why and Why Not
As I said it’s one of the most controversial charts. You can find a lot of people saying not to use a SPEEDOMETER or a GAUGE chart in your dashboards. I have listed some of the points which can help you to decide when you can this chart and when you need to avoid it.
1. Single Data Point Tracking
As we know, using a SPEEDOMETER can only be relevant (like Customer Satisfaction Rate) when you need to track a single data point. So, if you need to track data (like Sales, Production) where you have more than one point then there is no way for it.
2. Only Current Period Data
This is another important point which you need to take care while choosing a SPEEDOMETER for your dashboard or KPI reports that you can only present current data in it. For example, if you are using it to present customer satisfaction rate then you can only show the current rate.
3. Easy to Understand but Time-Consuming While Creating
As I said, a SPEEDOMETER is a single data point chart so it’s pretty much focused and can be easily understandable by the user. But you need to spend a couple of minutes to create as it’s not there in Excel by default.
download this sample file from here
Conclusion
A SPEEDOMETER or a GAUGE chart is one of the most used charts in KPIs and dashboards. Even then you can find a lot of people who don’t like to use it at all.
But, it’s one of those charts (Advanced Excel Charts) which can help you make your dashboards look cool.
And that’s why I’ve not only shared the steps to create a dashboard but also mentioned those which you need to consider while it in your dashboards. I hope you found it useful, but now, you need to tell me one thing.
Have you ever used a SPEEDOMETER or a GAUGE chart for your dashboard?
Share your views with me in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends, I’m sure they will appreciate it.
Fantastic! I’ve tried a few video tutorials and couldn’t make it work. This is great, I appreciate you sharing this.
I’m glad to hear that, Miruna.
Hi Puneet,
I was having trouble with the pointer. It isn’t displaying the value correctly at any number apart from 0/50. 100 is coming around 80. Wanted to know if you knew the reason behind that, or else I could share the file with you to get some assistance.
Thanks
i find it difficult to combine both donut for step 11. hope you can share some tip, on how to combine 3 different chart. the overlapping box make it difficult to edit. or i need more training?
Hi Puneet,
I found this really interesting and have been able to readily use it. One mod I made because a) I was curious about the ‘rest’ value and b) my pointer didn’t land accurately on the value it represented.
Instead of charting the pointer value I convert the value to degrees, then chart that, dynamically changing the Rest value to be 360-Pointer(Degrees)-1(Thickness).
Result:
H2 (Pointer) = User input
H3 = H2/(100/180) <as we want the number of degrees where 0=0 and 100(%) = 180
H4 (Thickness) = 1
H5 (Rest) = 360-(H3+H4)
Finally, the DataSet is H3:H5 and the Textbox value is H2 (so it still shows the original value)
share that file with me, if possible.
puneet(@)excelchamps(.)com
This post is excellent in every way. I truly appreciate your speedometer-related suggestions.
When moving the pointer values, the data is not on top. Is there any fix for this?
I found. Thanks.
Correction B2-200-(B3) saw I hit 100 after I hit the enter button.
Thank you for the tutorial. Very helpful.
At first I was reluctant to engage, but your writing got me hungry enough to eat it; before I knew it, the speedo was made. Thank you.
Tweaking the needle Rest to be an equation helped with the needle placement during the entire sweep.
labels Values
Pointer 75
Thickness 1
Rest =B2-100-(B3)
(Where B2=Pointer and B3=Thickness)
correction B2-200-(B3) saw I hit 100 after I hit the enter button.
Saved my life. Thanks for help!
Very helpful. Still trying to convert the values to large dollars and get the needle to calculate correctly. I know I am overlooking something simple.
The “Rest” value should be twice the maximum value less the value you want to show for the needle. So if you have potential values of between $0 and $5 million, with the pointer at $2 million, then you want the total pie chart value to be $10 million, with the pointer being the first $2 million so “Rest” being $8 million.
Thank you for this excellent tutorial – It helped me a lot!!
Hi I want live data to be connected to my speedometer eg.I want the dat from 0% to 100% if the live data change
0%percentle to 100%
Thanks Puneet
You shift me from Google Sheet to Excel.
Thanks
Thank you for this tutorial, I followed it word for word and it works perfectly
hi Puneet,
This is fantastic! And also great you had attached a working sample so that some of us Excel newbies can use it. 🙂 Only one question though. If I want my range to start from, let’s say 92 and go in the sequence of 92, 93, 94 (in the red zone), 94.5, 95, 95.5, 96, 96.5 (in the yellow zone), 97, 97.5 (in the blue zone) and 100 (in the green zone), how do we adjust the values or formulas in the third table to make sure the pointer points correctly on the gauge? is there a way to do this?
thanks mate! Appreciate you help greatly!
cheers,
Chan
Many Many Thanks for your Excel.
This was great. Def a lot of work to get setup for the first time but once setup I think it can be duplicated easily within the same workbook. Thanks so much for putting this together! Took me a while to figure out that the pie chart cells summed needed equal the sum of the other two tables’ totals. After that, the pointer worked perfectly.
Hello, the needle does not align with the value that is placed in the cell to direct it. How do you resolve this?
Make sure values always adds upp to 200
This is amazing! I was able to pull together my data into a meaningful format and display it in a crisp speedometer that my management likes a lot. Thanks for sharing your knowledge and tricks with others!
OUTSTANDING article. Just followed along and it came out perfectly. Can’t thank you enough for taking the time to draft this. Excellent work.
Thanks Puneet. Your explanation was awesome and made the process easy. I appreciate you sharing you knowledge. I’m going to check out your other trainings and tips as well. Great work.
Thanks for your words.
i wanna use range from 0-900. so what will be the value of my rest in table 3?
This was really helpful. Thank you!
One idea: Swapping primary and secondary axis on the settings of the chart type dialog ensures that the pointer shows in front of the others areas (pie chart areas need to be 100% transparent). Looks better, I think.
Your genuine effort appreciated but I could not reach Pointer Creation and hence gave up. Btw I was very good at Lotus 1-2-3 including Macros etc.. Excel, beyond a point is almost unusable (for me).
But why so.
This is one of the best explanations of doing a speedometer and believe me – I’ve watched a lot on YouTube. My question is (I’m really not good at Excel), I want the pointer to reflect the “grand total” of my conditional formula that tallies all the “yes’s” the client checked in a box. I got the check box to work but, at the end, I made the donuts. I have categories of Low, Middle, High but want the pointer when they have 26 yes’s answered to reflect that tally and adjust each time. Is that possible?
Could you be able to share a snapshot on puneet-at-excelchamps-dot-com?
Hi Punteet,
This help text is absolutely fantastic thank you so much for taking the time to write it.
I hope its ok but i have a final question on this…
Once I have created the speedometer how can I change the labels at the bottom of the chart back to the performance labels?
Thank you so much for all your work on this!
Best wishe
Emily
Do You mean that “VALUE” label?
Thanks a million Punteet you are awesome.. sharing your efforts for free…
Thank you soo much
You are welcome, Mathew.
Thanks a lot bhai… learned something which i was looking for from long time.
why my needle in at the back of doughnut 1 & 2? did I missed any step?
Thanks so much for this, really helpful. One problem im having is not being able to have seperate data labels for the category & performance graphs, im using Office Proffesional plus 2010 and it doesnt seem to have the option “value from cells”.
Thank you sooo much, Puneet! You made my day… now I can ‘wow’ my colleagues! 🙂
One issue with the sample data I realised: for the Pointer data, the 3 figures need to add up to the same total as the other 2 for the pointer to work properly. In this case 45 + 2 +154 = 201, so when you change 45, it doesn’t always point accurately. All 3 data sets should add up to the same total for the pointer to work well. e.g. 45+2+153 = 200. A difference of 1, makes ALL the difference.
God bless you abundantly Puneet!
thanks a lot Maria. I was not able to understand why my graph was not showing result accurate. after reading your comment i was able to clear the issue.
Even in your example the “45” is located at “55” this is very confusing.
Corrected
I appears that the speedo is not calibrated correctly. I just built one following your instructions. It looks OK with value 45, but at other values it is not correct.
I also when built there is the bottom half of the chart (the white area). How do you eliminate this white space from your Excel dashboard?
Thanks, Peter
Could you be able to share that with me?
Thanks so much, a good piece explained very well. Really appreciate
Your welcome, Priyanshu.
amazing
very useful
I’m glad you found this useful, Ary.
Yes, I have used them. One set of three with their individual goals also shown helped the team to more focused and better assess the tasks needed to make the ‘needles’ for the different tasks move in the right direction. After 6 months of weekly reports and discussions, the goals were reached.
is there a way to add a second ponter? I’ve tried but I can’t seem to get it to show.
The best way is to use VBA and shapes. Search YouTube for some really cool ideas. It doesn’t take a lot of VBA skill to get it done!
Hello:
Great tutorial, thanks.
My issue is that the pointer is pointing at 50 but the Pointer value is 45. My customers will be very confused by this. Do you know of a fix for this?
Thanks,
Colin
when I Use double doughnut , I am facing issue to control the doughnut hole size or Explosion for each doughnut separately . when change one the second affecting and change as per the new changed.
please help me who can I solve this issue.
share a screenshot.
I adjusted my labels to read 5; 15; 25 through 95. That way the pointer appeared to be in a more realistic position within the scale.
To get the needle on top, set only the pie on secondary axies. It will then cover the two whees. Then choose “no fill” on the pie-parts that is not the needle, and the wheels will appear.
Label chart is not truning into grey colour
All the chart color side got on right hand side
?
Hi Mate,
Great work thanks a lot for sharing such a good information on making speedometer chart.
You’re Welcome. ?
Hi Mate
Thanks for sharing your knowledge, i loved it
Thanks a lot! i was searching for something like this for my work ,if you could explain me more on the third table and how does it work would be great.
Once again Thank you!
Great tutorial, very accurate and useful, thanks a lot for sharing!
That is really helpful.
I have a question about the speedometer labels though.
I’m not sure if I am reading this correctly, but it looks like everything up to 30% is considered poor, above 30% and below 70% is considered average, etc
Is my interpretation correct?
This seems to be different to the values in table 1
Thanks
Nice tutorial there but the value 45 is pointed at label 50. Is there a way to solve it?
Hey Brianc, for labels I have used the highest value from the range, 50 means 41-50 and it’s in the mid because of 45.
Cell H4 should be =(200-H2)+H3
This is by far, one of the best looking gauge charts in Excel. Thank you!
One tweak: how can I make the needle appear on top? In my rendering, the indicator is the bottom layer and the other two donut charts cover it.
Thanks again for your help!
Hey Bacon, I’m also trying to figure this out. As we are adding three different charts here and the pie chart is the last one. Even I have tried rearranging source data but didn’t worked.
Awesome! Finally a step by step instruction that is understandable and actually works! THANKS!
With some 3D formatting you can actually give the needle a 3D look.
I’m so glad you liked it. 🙂
Very clear, thank you.
You could explain that the Rest data in the third table is a formula depening on the other two values in that table. I know it’s clear from the sample file, but not all readers will download the sample.
Let me get into it.
Great Its very Helpful ………………..
Thanks for your words. 🙂
Hi Puneet,
Thanks for explaining & step by step instructions for creating a speedometer chart. Very helpful.
Thanks
I’m so glad you liked it. 🙂
EXCELLENT!
Thanks for your words. 🙂
Hi,thanks for your great step learning,but i have a guestion.what is rest data in third table ??
You can refer to the sample file.
Very nice… thanks for sharing this approach
I’m so glad you liked it. 🙂