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.
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.
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.
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.
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.
Step 5: Go to the Insert tab, and in the charts section, select a donut chart.
Step 6: An empty chart is created.
Step 7: Right-click inside the chart, and click on Select Data.
Step 8: Select Data Source dialogue box appears. Click on Add.
Step 9: Edit Series dialogue box appears. Select the Series Name i.e. cell C5.
Step 10: Select range C6:C11 in the Series Values option. Click Ok.
Step 11: Service Data Source reappears. Click OK.
Step 12: A donut chart is created.
Step 13: Double click on the donut of the chart. A 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.
Step 15: You do not require legends in your chart. Single click inside the legends. Press Delete.
Step 16: You also do not require a Chart title in the speedometer. Single click inside the chart title. Press Delete.
Step 17: Double click inside the lower green portion of the donut chart.
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.
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.
Step 20: Select Data Source dialogue box appears. Click on Add.
Step 21: Edit Series dialogue box appears. Select Cell F5 in the Series name.
Step 22: In the Series Values option, select range F6:F8. Click Ok.
Step 23: Select Data Source dialogue box appears. Click Ok.
Step 24: Second donut chart appears around the first donut chart.
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.
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.
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.
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.
Step 29: Now, double-click on the blue region of the pie chart.
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.
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.
Step 32: A dynamic speedometer is created. The needle of the chart changes as per the marks of Arushi.