How to Create a SPEEDOMETER Chart [Gauge] in Excel

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 a SPEEDOMETER Chart?

Basically, when it comes to Excel, a SPEEDOMETER is a single-point chart which helps you to track a single data point against its target.

Just like a normal SPEEDOMETER, you’ll have 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…

…here is an example.

Steps to Create a SPEEDOMETER in Excel

data tables to create speedometer in excel

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.

Below are the steps you need to follow to create a SPEEDOMETER in Excel:

  • First of all, go to Insert Tab ➜ Charts ➜ Doughnut Chart (with this you’ll get a blank chart).
    insert-a-blanl-dougnut-chart-to-create-a-speedometer-in-excel
  • Now, right-click on the chart and then click on “Select Data”.
    right-cllick-to-create-a-speedometer-chart-in-excel
  • 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.
    select-values-from-first-data-table-to-create-a-speedometer-in-excel
  • Once you click OK, you’ll have a doughnut chart just like below.
    first-doughnut-chart-to-create-a-speedometer-in-excel
  • 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.
    adjust-slice-angel-to-create-a-speedometer-in-excel
  • 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”.
    select-no-fill-for-half-of-the-chart-to-create-a-speedometer-chart-in-excel
  • 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.
    change-color-scheme-to-create-a-speedometer-chart-in-excel
  • 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.
    select-values-from-second-data-table-to-create-a-speedometer-in-excel
  • Once you click OK, you’ll have a doughnut chart just like below.
    excel-speedometer-chart-after-inserting-second-doughnut-chart
  • 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.
    excel-speedometer-chart-after-second-doughnut-chart
  • For this, right click on the chart and then click on “Select data”.
    right-cllick-to-create-a-speedometer-chart-in-excel
  • In “Select Data Source” window click on “Add” to enter a new “Legend Entries” and select “Values” column from the third data table.

    select-values-from-thrid-table-to-create-a-speedometer-in-excel
  • After that, select the chart and go to Chart Tools ➜ Design Tabs ➜ Change Chart Type.
    change-chart-type-pie-chart-to-create-a-speedometer-in-excel
  • In “Change Chart Type” window, select pie chart for “Pointer” and click OK.
    select-pir-chart-click-ok-to-create-a-speedometer-in-excel
  • At this point, you have a chart like below. Note: If after selecting a pie chart if the angel is not correct (there is a chance) make sure to change it to 270.
    chart-after-adding-a-pie-chart-to-create-a-speedometer-in-excel
  • 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.
    make-no-fill-for-large-part-of-pie-chart-to-create-a-pie-speedometer-in-excel
  • 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 the three charts.
    enter-point-exploration-to-create-a-speedometer-chart-in-excel
  • 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”.
    add-data-lables-from-first-table-to-create-a-speedpmeter-in-excel
  • After that, select the label chart and do the same with it by adding labels from the second data table.
    add-custom-data-label-from-trid-table-to-create-a-speedpmeter-in-excel
  • 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, hit ENTER.
    add-custom-data-label-from-trid-table-to-create-a-speedpmeter-in-excel

Hurry! your First Gauge/SPEEDOMETER chart is ready to rock.

simple steps to create a speedometer in excel

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.

Sample File

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.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

37 thoughts

Leave a Comment

Your email address will not be published.

  1. 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.

    • 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!

  2. Pingback: What are Gauge Charts
  3. 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

  4. 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.

  5. 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.

  6. 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.

  7. 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!

  8. 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

    • 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.

  9. 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.

  10. 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.

  11. 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.

  12. Hi Puneet,
    Thanks for explaining & step by step instructions for creating a speedometer chart. Very helpful.
    Thanks