How to Create a Speedometer/Gauge Chart in Excel?

Last Updated : 11 Mar, 2022

Speedometer Chart(Gauge Chart) is the chart that has a needle and points to the desired area in that chart. It depicts a needle in a half donut chart. The needle points to the position, according to your input values. It’s the same as that of a speedometer in a bike or a car. In this article, we will learn how to create a dynamic speedometer chart(Gauge chart) in excel.

Advantage of Gauge Chart:

Gives a clearer picture of the range of the data. The required position is achieved with ease with a thin needle.
It is very useful for target-set visualizations. For example, a company sets a target of achieving a turnover of x million dollars at the end of the year. We can keep the track of the current check of our position and also how far we are to achieving this goal.

Disadvantages of Gauge Chart:

We cannot determine the edge cases. For example, a needle lies upon the border of two ranges we cannot confirm the exact range value.
They are not much user friendly and also not color-blind friendly.

Creating a dynamic Gauge Chart in Excel

Given a data set of Grade of students. The data written in cell C6:C10 need to be understood carefully. For example, cell C6 has a cell value of 20, which means that if a student’s score is between 0 to 20, then its grade will be D. See, Cell C7 has a cell value of 25, which means that if a student score is between 20 to 45, then its grade will be C. Similarly, Cell C10 has cell value as 10, which means that if a student score is between 90 to 100, then its grade will be A+. We can see from the data set, a student name Arushi has scored 90 marks. So, her grade will be A+. The needle of the speedometer will point in between 90 to 100.

Grade-of-student

Now to create a dynamic Gauge Chart follow the following steps:

Step 1: Add a new value in Cell B11 name Sum. Write the sum formula in cell C11. The selected range to sum the elements is C6:C10. The formula is =SUM(C6:C10). Press Enter.

Adding-a-new-sum-row Sum-cell-contains-the-sum-of-all-the marks

Step 2: Add a new value in Cell B12 name Total. Write the sum formula in cell C12. The selected range to sum the elements is C6:C11. The formula is =SUM(C6:C11). Press Enter.

Add-a-new-value-in-Cell-B12-name-Total Total-cell-contains-the-total-marks

Step 3: Add a new value in Cell E7 name width of the needle. As the name suggests, this specifies the width of the needle you want to have in your speedometer. For example, the width of the needle taken for this data set is 1.2.

Add-a-new-value-in-Cell E7-name-width-of-the-needle

Step 4: Add a new value in Cell E8 name End Sum Formula. The formula used here is =SUM(C12, -SUM(F6:F7)) i.e. subtract the sum of Arushi’s marks plus the width of the needle from the Total, cell value C12. Press Enter.

Add-a-new-value-in-Cell-E8-name-End-Sum-Formula

Step 5: Go to the Insert tab, and in the charts section, select a donut chart.

Select-a-donut-chart

Step 6: An empty chart is created.

An-empty-chart-is-created

Step 7: Right-click inside the chart, and click on Select Data.

Click-on-Select-Data

Step 8: Select Data Source dialogue box appears. Click on Add.

Click-on-Add

Step 9: Edit Series dialogue box appears. Select the Series Name i.e. cell C5.

Select-the-Series-Name

Step 10: Select range C6:C11 in the Series Values option. Click Ok.

Click-Ok

Step 11: Service Data Source reappears. Click OK.

Service-Data-Source-reappears-Click-OK

Step 12: A donut chart is created.

Donut-chart-is-created

Step 13: Double click on the donut of the chart. A Format Data Point dialogue box appears.

Format-Data-Point-dialogue-box-appears

Step 14: Go to Series Options, and inside Angle of the first Slice, set the angle to 270 degrees.

Set-the-angle-to-270-degrees

Step 15: You do not require legends in your chart. Single click inside the legends. Press Delete.

Click-inside-the-legends

Step 16: You also do not require a Chart title in the speedometer. Single click inside the chart title. Press Delete.

Press-Delete-option

Step 17: Double click inside the lower green portion of the donut chart.

Double-click-inside-the-lower-green-portion

Step 18: Format Data point dialogue box appears. Go to series options, inside the Fill section, select No fill. The lower green portion will hide.

Lower-green-portion-will-hide

Step 19: The work of char1 is over. Now, you have to create a second chart in the same chart. Right-click inside chart1 and click on Select Data.

Click-on-Select-Data

Step 20: Select Data Source dialogue box appears. Click on Add.

Click-on-Add

Step 21: Edit Series dialogue box appears. Select Cell F5 in the Series name.

Select-Cell-F5-in-the-Series-name

Step 22: In the Series Values option, select range F6:F8. Click Ok.

Select-range-F6:F8-and-click-ok

Step 23: Select Data Source dialogue box appears. Click Ok.

Select-Data-Source

Step 24: Second donut chart appears around the first donut chart.

Second-donut-chart-appears

Step 25: Single click on the outer donut chart. Go to Insert Tab, and in the charts section, select 2-D pie chart. The outer donut chart will convert to a pie chart.

Outer-donut-chart-will-convert-to-a-pie-chart

Step 26: Double click on the pie chart. Format Data Series dialogue box appears. Under Series Options, in Angle of the first slice, set angle as 270 degrees which were the same as that of the first donut chart.

Double-click-on-the-pie-chart

Step2 7: Now, you need to remove the grey and blue region from your pie chart and your speedometer will be ready. Double click on the grey line.

Double-click-on-the-grey-line

Step 28: Format Data Point dialogue box appears. In Series Options, under Fill, select No fill, and under Border, select No Line. The grey region and its border hide.

Hiding-the-grey-region-and-its-border

Step 29: Now, double-click on the blue region of the pie chart.

Double-click-on-the-blue-region

Step 30: Format Data Point dialogue box appears. In Series Options, under Fill, select No fill, and under Border, select No Line. The blue region and its border hide.

The-blue-region-and-its-border-hide

Step 31: Now, double-click on the red region of the pie chart i.e. is the needle. Format Data Point dialogue box appears. In Series Options, under Fill, select Solid fill, change the color of the needle to black, and under Border, select No Line. A black needle is created.

Black-needle-is-created.