Speedometer Chart – MS Excel Tips & Tricks

In this tutorial we are going to learn how to make a Speedometer Chart. To make learning easy and fun we have given both video tutorial and step-by-step instructions in this article.

Before we learn to design and construct a Speedometer Chart, we will first learn what are Gauge Charts.

A Gauge is a device for measuring the amount or size of something, for example, fuel/rain/temperature gauge.

There are various scenarios where a Gauge is utilized ?

• To Gauge the temperature of a person, a thermometer is used.
• To Gauge the speed of an automotive, a speedometer is used.
• To Gauge the performance of a student, a mark sheet is used.

Gauge charts came into usage to visualize the performance as against a set goal. The Gauge charts are based on the concept of speedometer of the automobiles. These have become the most preferred charts by the executives, to know at a glance whether values are falling within an acceptable value (green) or the outside acceptable value (red).

What is a Gauge Chart?

Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. A Gauge Chart shows the minimum, the maximum and the current value depicting how far from the maximum you are. Alternatively, you can have two or three ranges between the minimum and maximum values and visualize in which range the current value is falling.

A Gauge chart looks as shown below.

Advantages of Gauge Charts

Gauge charts can be used to display a value relative to one to three data ranges. They are commonly used to visualize the following ?

• Work completed as against total work.
• Sales compared to a target.
• Service tickets closed as against total service tickets received.
• Profit compared to the set goal.

Disadvantages of Gauge Charts

Though the Gauge charts are still the preferred ones by most of the executives, there are certain drawbacks with them. They are ?

• Very simple in nature and cannot portray the context.
• Often mislead by omitting key information, which is possible in the current Big Data visualization needs.
• They waste space in case multiple charts are to be used. For example, to display information regarding different cars on a single dashboard.
• They are not color-blind friendly.

For these reasons Bullet charts, introduced by Stephen Few are becoming prominent. The data analysts find Bullet charts to be the means for data analysis.

Creating a Gauge Chart

You can create Gauge charts in two ways ?

• Creating a simple Gauge chart with one value: This simple Gauge chart is based on a Pie chart.
• Creating a Gauge chart with more number of Ranges: This Gauge chart is based on the combination of a Doughnut chart and a Pie chart.

Gauge Chart with Multiple Ranges

In this tutorial we will see how to make a gauge chart with more ranges.

Preparation of Data

Arrange the data for values as given below.

This data will be used for Doughnut chart. Arrange the data for Pointer as given below.

You can observe the following ?

• The value in the cell C8 is the value you want display in the Gauge chart.
• The value in the cell C9 is the Pointer size. You can take it as 5 for brevity in formatting and later change to 1, to make it a thin pointer.
• The value in the cell C10 is calculated as 200 – (C8+C9). This is to complete the Pie chart.

Creating Gauge Chart with Multiple Ranges

You can create the Gauge chart with a Doughnut chart showing different regions corresponding to different Values and a Pie chart denoting the pointer. Such a Gauge chart looks as follows ?

Step 1 Select the values data and create a Doughnut chart.

Step 2 Double click on the half portion of the Doughnut chart (shown in yellow color in the above chart).

Step 3 Right click and under the Fill category, select No Fill.

Step 4 Deselect Chart Title and Legend from Chart Elements.

Step 5 Right click on the chart and select Format Data Series.

Step 6 Type 271 in the box – Angle of first slice in the SERIES OPTIONS in the Format Data Series pane.

Step 7 Change the Doughnut Hole Size to 50% in the SERIES OPTIONS in the Format Data Series pane.

Step 8 Change the colors to make the chart appealing.

As you can observe, the Gauge chart is complete in terms of values. The next step is to have a pointer or needle to show the status.

Step 9 Create the pointer with a Pie chart as follows.

• Click on the Doughnut chart.
• Click the DESIGN tab on the Ribbon.
• Click Select Data in the Data group.
• Select Data Source dialog box appears. Click the Add button.

Step 10 The Edit Series dialog box appears.

• Select the cell containing the name Pointer for Series name.
• Select the cells containing data for Value, Pointer and End, i.e. C8:C10 for Series values. Click OK.

Step 11 Click OK in the Select Data Source dialog box.

• Click the DESIGN tab on the Ribbon.
• Click Change Chart Type in the Type group.
• Change Chart Type dialog box appears. Select Combo under the tab All Charts.
• Select the chart types as following ?
  • Doughnut for Value series.
  • Pie for Pointer series.
• Check the box Secondary Axis for the Pointer series. Click OK.

Your chart looks as shown below.

Step 12 Right click on each of the two bigger Pie slices.

• Click on Fill and then select No Fill. Right click on the Pointer Pie slice and select Format Data Series.
• Type 270 for Angle of first slice in the SERIES OPTIONS. Your chart looks as shown below.

Step 13 Right click on the Pointer Pie slice.

• Click on Format Data Series.
• Click on Fill & Line.
• Select Solid Fill for Fill and select the color as black.
• Select Solid Line for Border and select the color as black.

Step 14 Change the Pointer value from 5 to 1 in the data to make the Pointer Pie slice a thin line.

Step 15 Add a Data Label that depicts % complete.

Your Speedometer Chart is ready.

Hope this was helpful and happy learning.

To checkout more stuff on MS Excel visit https://www.wkvedu.com/blog/

To learn more visit www.wkvedu.com