Excel Gauge Chart

We all know that a Gauge is a device used to measure the quantity or size of something, for example, a fuel gauge, speed gauge or temperature gauge.

There are various situations where the concept of Gauge is applied -

To Gauge the temperature of an individual, we use a thermometer.
To gauge a vehicle's speed, we incorporate a speedometer in the automobile.
To Gauge the performance of a student, we prepare a mark sheet.

Based on the same concept Microsoft Excel introduced the Gauge charts to help the end-users visualize the performance against a set goal. The Gauge charts in Excel are developed based on the idea of the speedometer of automobiles. They are very helpful and repeatedly are used by business executives to explain whether values fall within a permissible value (represented by green) or the acceptable outside value (represented by red).

In this tutorial, we will learn in detail about the Excel Gauge Chart, including its definition, advantages, disadvantages and step-by-step implementations of this chart in your worksheet.

What is Gauge Chart?

"Gauge charts, also known as Dial charts or Speedometer charts, are developed and designed on the concept of the speedometer. Unlike the speedometer, these charts also incorporate a pointer or a needle to display information as a reading on a dial. The needle moves automatically whenever the user makes any changes in the data. It's a single-point chart which allows the user to chase a single data value against the specified target value."

A Gauge Chart combines an Excel Doughnut chart and a Pie chart. A Gauge Chart is commonly used to show the minimum, the maximum and the current value representing how much distance is between the current value and the total value. It also allows the users to add two or three ranges between the minimum and maximum values and easily visualize the current value dropping in which range.

Advantages of Gauge Charts

Gauge charts can display a value relative to one-to-many data ranges. They are commonly used to cater for the below-given services -

Gauge Charts depict the work completed as against the total work.
Gauge Charts measure the received sales compared to a target amount.
Gauge Charts can display the number of service tickets closed against the total service tickets received.
They help to compare Profit concerning the set goal.

Disadvantages of Gauge Charts

Although Gauge charts are one of the most popular charts and preferably used on the regular basis by most of the executives, but still there are various drawbacks with them. They are as follows-

Gauge Charts are simple in nature and therefore cannot show the exact context.
Gauge Charts often mislead the users by neglecting the key information, that is the required nowadays for the current Big Data visualization.
If you are using multiple charts, they waste unnecessary space. For example, to present the data regarding multiple cars on a single dashboard.
They are not friendly for color-blind people.

To overcome the above fit falls, the bullet chart was introduced by Stephen Few Bullet charts are known to be the best solution for data analysis. Therefore nowadays, it is the first preference of business analysts.

Creating a Gauge Chart

Microsoft Excel offers two ways to create Gauge charts in your worksheets:

Creating a Gauge chart with single value - This is a simple type of Gauge chart created with the help of a Pie chart.
Creating a Gauge chart with multiple Ranges - This is an advanced type of Gauge chart created by combining two different Excel charts i.e., Doughnut chart and a Pie chart.

Steps to create a Simple Gauge chart

Below given is the data for which you have to create a simple Gauge chart with single value.

The above data is not yet ready. Therefore, we have to re arrange the data for Gauge chart:

You can observe the following arrangement in the data-

Cell C7 covers the value corresponding to C3.
The C5 has 100% value to represent the half value of the Pie chart.
Cell C6 contains the difference between cells C5 and C3. The cells C6 and C7 together are equal to 100%, which is used to make the second half of the Pie chart.

Following are the steps to create a simple Gauge chart with single value -

Step 1 - Select the data range for which you want to create the Gauge chart. In our case, it will be C5:C7.

Step 2 - Go to Insert-> Charts. From the charts section, click on the Pie chart.

Step 3 - As a result you will notice, Excel will create a Pie Chart in your worksheet. Right click on the chart.

Step 4 - The following drop-down menu will appear. Select the Format Data Series option from the list.

Step 5 - You will observe the Format Data Series option will be shown on the right side of the worksheet panel. From the listed options, click on the SERIES OPTIONS. In the first box, i.e., Angle of first slice type 90 degrees.

As you can see, the upper half of the Pie chart is what we need to create the Gauge chart. All we have to do is to remove the lower half and our chart is ready.

Step 7 - Select the lower half of the pie and right-click on it. Click on Fill. From the listed option select the No Fill option.

You can notice that the Pie slice on the hovering on right side represents the Profit %.

Step 9 - Beautify the chart to make it visually appealing.

From the Fill colours option, you can choose an appropriate colour to fill the Pie slices.
Click on the right Pie slice, from the listed options, choose the 3-D FORMAT as Top bevel, and select the Angle option.
Click on the left Pie slice, from the choose the 3-D FORMAT as Top bevel, and select the Divot option.
Click on the right Pie slice and add Data Label.
From the Data Label panel, enter the Size and Position of the chart.
From the Chart Elements, deselect the Legend option.
Give an appropriate chart Title to the Gauge chart and set a well-defined position for it.

You will have the following chart as the output:

Steps to create a Gauge chart with Multiple Ranges

Below is the data for which you must create a Gauge chart with multiple ranges.

The above data is not yet ready. Therefore, we have to re arrange the data for Gauge chart as given below:

You can observe the following arrangement in the data-

The data in cell C8 represent the value we want display in our Gauge chart.
The data in the cell C9 represents the Pointer size. Though you can increase or decrease it this size to format the chart. Initially you can take it as 5 for brevity and later can decrease it to 1, to make it the pointer thinner.
At last, we have mentioned the value 110 in the cell C10. It will complete our Pie chart.

To create advanced Gauge Chart, we will combine two different types of charts. We will incorporate the Doughnut chart depicting different parts corresponding to different Values and a Pie chart representing the Gauge pointer. Following are the steps to create a Gauge chart with multiple ranges:-

Step 1 - Select the data range for which you want to create the Gauge chart. In our case, it will be C3:C6.

Step 2 - Go to Insert-> Charts. From the charts section, click on the Doughnut chart.

Step 3 - As a result you will notice, Excel will create a Pie Chart in your worksheet. Double click on the half portion of the chart.

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

Step 5 - Since we don't want the title and legend to appear in our Gauge chart so we will deselect both Chart elements.

Step 6 - Right-click on the chart and select the Format Data Series from the listed options. You will observe the dialog window displayed on the pane's right side. In the first box of the Series Options type the 271 in the box. It will change the angle of your Chart.

Step 7 - Next, we will change the Doughnut Hole Size from the same window to 65%. It will make the size of the chart bigger.

Step 8 - Next, we will change the Doughnut Hole Size from the same window to 65%. It will make the size of the chart bigger.

As you can see in the above image, our Gauge chart is successfully created with proper values. The next step is to insert a Gauge pointer or needle to display the value status.

Step 9 - To create a Gauge pointer with a Pie chart follow the below steps:

Click on the Doughnut chart.
At the top of the Excel Ribbon, click on the DESIGN tab.
From the Data group section, click on the 'Select Data' option.
The Select Data Source dialog box will be displayed. Click on the Add button.

Step 10 - The above steps will open the Edit Series window pane.

In the first box, select the cell containing the name Pointer for your Gauge Chart.
Select the cells containing data for Value, Pointer and End, i.e. C8:C10 for Series values. Click on the OK button.

Step 11 - It will again redirect you to the Select Data Source dialog box. Click on OK.

Step 12 - Select the chart. From the Excel Ribbon click on the Chart DESIGN tab and follow the below steps:

From the Type Group click on the Change Chart Type option.
You will observe the Change Chart Type dialog box will be displayed. From the All Charts section, click on the Combo option.
Next, we will be selecting both the chart types on the basis of our requirement-
- For the Value series select the Doughnut Chart.
- For the Pointer series select the Pie Chart.
Makesure to Check the box Secondary Axis for the Pointer series.
Once done click on the OK button.

Your chart looks as shown below.

Step 12 - We will Right click on each of the bigger Pie slices and follow the below steps-

Click on the Fill option and select the No Fill option.
Once will observe that the position of your Doughnut Chart has changed, Select the chart and right click on it. From the listed option click on Format Data Series.
From the Series Option, first choose the series option from top and type 270.
Next, choose the Pointer option and again in the first box i.e., Angle of first slice type 270 degrees.

Your chart looks as shown below.

Step 13 - The last step is to format the Pointer Pie Slice look, making the Gauge Chart more appealing.

Select only the Pointer Pie Slice and right click on it. From the listed options click on the Format Data Series.
Click on Fill & Line.
Select 3D Solid Fill and select the color as black.
Add a Data label of 85 to represent the pointer value.

Finally, as shown below, you will have the desired Gauge Chart.