Create speedometer or gauge chart
A gauge chart, also named as dial chart or speedometer chart which looks like a speedometer in cars using a needle to show information as a reading on a dial, when the data changes, the needle moves dynamically as well as below screenshot shown.
In Excel, a gauge chart is composed of two Doughnut charts and a Pie chart, it shows the minimum, maximum and current values in the dial. It can be used for presenting sales performance of representatives or work completed as against total work or other situations with a visualization way. This article, I will talk about how to create a gauge or speedometer chart in Excel step by step.
- Create a gauge or speedometer chart in Excel
- Create a gauge or speedometer chart in Excel with an amazing feature
- Download Gauge or Speedmometer Chart sample file
- Video: Create a gauge or speedmometer chart in Excel
Create a gauge or speedometer chart in Excel
To create a gauge chart, you should prepare some data ranges firstly as below screenshot shown:
Table 1: This table of data represents the levels, the number and value of intervals depends on what you need. In this example, I will set up 3 intervals which add up to 100. Remember to calculate the total values because this total will be the lowest part of the half-circle gauge.
Table 2: This table is used to create scales ranging from 0 to 100. You can change it if you want to have a different scale range.
Table 3: This table will be used to create a pie chart for the needle:
- Current Position: The position of the needle is located;
- Width: The width of the needle;
- End: The value of the remaining arc of the pie, it should be calculated by subtracting the width and current points from the sum of values of levels. In this example, we can get it with this formula: =sum(B3:B6)-SUM(H3:H4)=200-82=118.
After preparing the above data, please do with the following step by step:
1. Select the Table 1, and then click Insert > Insert Pie or Doughnut Chart > Doughnut, and a doughnut chart has been inserted into the sheet, see screenshots:
 |
 |
 |
2. Then, you can delete the chart title and legend as you need, and then, right click the doughnut chart, choose Format Data Series from the context menu, see screenshot:
3. In the Format Data Series pane, under the Series Options icon, set the Angle of the first slice to 270°, at same time, you can set the Doughnut Hole Size to 50% as you need, and you will get the following chart:
4. In this step, you should hide the bottom part of the doughnut chart, please double click to select the bottom half part, and right click to choose Format Data Point option, see screenshot:
5. In the Format Data Point pane, under the Fill & Line icon, select No fill option to make the half-circle invisible, see screenshot:
6. Now, your first part of the gauge has been created, you can add data labels to make the gauge easier to read. Click to select the chart, and then click Chart Elements > Data Labels > More Options, see screenshot:
7. In the Format Data Labels pane, under the Label Options icon, check Value From Cells option, and in the popped out Data Label Range dialog box, select the performance labels from the first table, and then click OK, see screenshots:
 |
|
 |
8. The data labels have been added into the chart, and then, you should uncheck Value option in the Label Options section to remove the value from the labels, and you can format the data point with the specific colors to your need, see screenshot:
9. Now, in this step, you should create the second doughnut chart for showing the scales, please click to select the chart, and then, click Select Data from the Design tab, see screenshot:
10. Then, in the Select Data Source dialog box, click Add button, see screenshot:
11. In the Edit Series box, enter a name into the Series name text box, and then in the Series values box, select the value data from table 2, see screenshot:
12. Click OK button, and you will get a doughnut chart as below screenshot shown:
13. And now, you can format a grey color for this doughnut chart to make it more professional, click to select the outside doughnut chart, and then click Shape Fill under the Format tab, then choose a grey color as you need, see screenshot:
14. Again, you need to hide the bottom half of this doughnut chart, double click to select the bottom half circle, see screenshot:
15. And then, choose No Fill from the Shape Fill under the Format tab, now, you will get the chart as below screenshot shown:
16. In this step, you can also add the data labels for the data series, click the chart, and then click Chart Elements > Data Labels > More Options, see screenshot:
17. In the Format Data Labels pane, under the Label Options icon, check Value From Cells option, in the popped out Data Label Range dialog box, select the scale labels from the second table, and then click OK, see screenshots:
 |
|
 |
18. After adding the scale labels, uncheck the Value option in the Label Options section, and the data label has been added into the chart, see screenshot:
19. After finishing the two doughnut charts, in the following steps, you should create a pie chart for the needle, right click the chart, and then click Select Data from the Design tab, in the Select Data Source dialog box, click Add button, see screenshot:
20. In the Edit Series box, enter a name into the Series name text box, and then in the Series values box, select the value data from table 3, see screenshot:
21. Then, click OK button, you will get a chart as below screenshot shown:
22. Now, you should change this outside doughnut to a pie chart, click to select the outside doughnut, and then click Change Chart Type under the Design tab, see screenshot:
23. Then, in the Change Chart Type dialog box, select the series name pie from the Series Name, and choose Pie Chart from the drop down list, see screenshot:
24. And then, click OK button, the doughnut chart has been changed to a pie chart as below screenshot shown:
25. Then, you should format no fill for the two large slices to make them invisible, double click the grey slice to select it, and then choose No Fill from the Shape Fill option under the Format tab, do with the same operation for hiding the blue slice, and you will get the below screenshot:
26. After hiding the two slices, then, double click to select the tiny slice, and then, right click it, and choose Format Data Point from the context menu, see screenshot:
27. In the Format Data Point pane, under the Series Options icon, please do the following operations:
- Enter 270° into the Angle of first slice box;
- Change the Pie Explosion value for the pie chart to make pointer stick out from the doughnut chart as you like.
28. Now, the gauge chart has been created, at last, you should to add a custom data label for the needle, for this, please click Insert > Text Box > Draw Horizontal Text Box, see screenshot:
29. Then, draw a text box in the middle of the half doughnut chart, select it and then in the formula bar enter “=” and select the current position values cell, then press Enter key. See screenshot:
30. Now, the gauge chart has been created successfully, at last, you can move all data labels to the end corners as you need, see screenshots:
 |
|
 |
Create a gauge or speedometer chart in Excel with an amazing feature
If you are painful with above complicated method, here, I will recommend a handy tool - Speedometer Chart of Kutools for Excel, with this feature, you can insert a 180° or 270°speedometer chart by only several clicks as below demo shown. Click to download Kutools for Excel for free trial!
Download Gauge or Speedmometer Chart sample file
Video: Create a gauge or speedmometer chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
