How to Create Gauge Chart in Excel

A gauge chart (a.k.a dial chart or speedometer chart) is used to compare or measure performance against a goal that you set. It is called a speedometer chart because it resembles a speedometer of automobiles and it uses a pointer to show data as a reading on a gauge.

Generally, the Gauge chart is used to visualize the achievement or performance of one data field on a maximum-minimum scale. Excel does not provide built-in support for creating gauge charts. But with a few tricks, you can create a gauge chart by combining two different chart types which are the doughnut chart and the pie chart.

Since Excel does not offer you an in-built gauge chart type, you just have to create a gauge chart by using the combo chart option in excel. We’ll see how to do that in this article.

Setup the Data for the Gauge Chart

Let’s start by preparing our datasets. First, we’ll have to set up three different data tables: one for the dial, one for the pointer, one for the chart data (optional).

Prepare your table as shown below:

The Dial

Performance Labels – These determine the chart labels that you want to be shown on the dial. This will have markers such as Poor, Average, Good, and Excellent.
Levels – These values will separate the speedometer into multiple sections.

The Pointer

The pointer is created with the following values, these values determines where you want to put the pointer of the gauge chart.

Pointer – This value specifies how far on the gauge chart you want the needle to be.
Thickness – This specifies the width of the needle (the pointer). You can change the thickness of the needle to your desired size, but it’s best to keep it under five pixels.
Rest Value – The value for the remaining part of the pie chart. This should be calculated by this formula ‘=200-(E3+E4)’. You should this formula in cell E5.

Create Doughnut Chart

Once you setup your datasets, select the values under the ‘Level’ column of the first table (The Dial). Next, go to the ‘Insert’ tab, click the ‘Insert Pie or Doughnut Chart’ icon from the Charts group, and choose ‘Doughnut’ chart from in the drop-down.

Then, delete the default chart title and the legend. Now you have a doughnut chart that is a semi-circle on one side (level: 100), and the rest of the parts on the other side (level: 20, 50, 20, 10).

Rotate the Doughnut Chart and Remove the Chart Border

Now we need to adjust the position of the chart by rotating the chart. To do that, right-click on the colored part of the chart and select the ‘Format Data Series’ option.

This will open up the right-side format pane for the chart. In the pane, set the ‘Angle of first slice’ to 270° using the slider and adjust the ‘Doughnut Hole Size’ too if you want.

Remove the Chart Border

Once you positioned the chart properly, remove the chart border (the white separator between each colored section) to make the chart nice and clean.

In the same right-side ‘Format Data Series’ pane, click the ‘Fill & Line’ icon, go to the ‘Border’ section, and select the ‘No line’ option to remove the chart border.

Turn Full Circle Doghnut Chart to Semi-Circle

As you know, gauges are never full circle, so in order to change that full circle into a half circle, you need to hide the bottom slice of your chart.

To do that, double click on the bottom slice of the chart to open the ‘Format Data Point’ pane. In there, head to the ‘Fill & Line’ tab, and in the Fill section, choose ‘No fill’ to make the bottom slice transparent.

Change the Colors of the Rest of the Slices

Now for the rest of the four data points, let’s change the colors to make the chart more appealing.

First select any slice on the chart by double-clicking on it and in the ‘Format Data Point’ pane, switch to the ‘Fill & Line’ tab, click the ‘Fill Color’ icon in the ‘Fill’ section to open the color palette, and choose a color for the slice.

Then, select each slice one by one and change the color of the respective slices. Once you’re done, you should have something that looks like this:

Add Data Labels to the Chart

Once that’s done, you should add data labels to the charts, because a gauge chart without any labels has no practical value, so let’s fix that. Besides, you can also data labels at the end too, but it’s a bit tricky process. So we’ll just add labels right now to keep it simple.

To add data labels, right-click on any slice, click ‘Add Data Labels’ from the context menu and again select ‘Add Data Labels’.

This will add the values (Level Column) as labels from the first table.

Now, double-click on the data labels on the bottom slice (transparent slice) and delete it. Then, right-click on any data label and select ‘Format Data Labels’.

In the ‘Format Data Labels’ pane, click on the ‘Value From Cells’ option. A small ‘Data Label Range’ dialog box will appear.

In the Data Label Range dialog, click on the ‘Select Data Label Range’ field and select label names under the ‘Performance Label’ from the first data table, and click ‘OK’. Make sure not to select the ‘Total’ label.

Then, untick ‘Values’ option from Format Data Labels pane and close the panel.

Create the Pointer with a Pie Chart

Now, let’s add the pointer to the gauge. To do that, right-click on the chart and then click on ‘Select Data’.

In the Select Data Source dialog, click on the ‘Add’ button to open the ‘Edit Series’ dialog box.

In the ‘Edit Series’ dialog, type ‘Pointer’ into the Series Name field. Click on the ‘Series values’ field and delete the default value ‘={1}’ and then go to your pie chart table (The Pointer), select the range containing data for Pointer, Thickness, and Rest Value, i.e. E3:E5 for Series values and click ‘OK’.

Click ‘OK’ again to close the dialog box.

Convert the Pointer Doughnut Chart to a Pie Chart

Now, you need to change the newly created doughnut chart to a pie chart. For this, right-click on the outer chart and select ‘Change Series Chart Type’.

In the Change Chart Type dialog box, select ‘Combo’ under the tab All Charts. Click on the Chart Type dropdown menu next to the Series Name ‘Pointer’ and choose ‘Pie’ as the chart type. After that, check the ‘Secondary Axis’ box next to Series ‘Pointer and click ‘OK’.

Once you’re done, you’re chart may look like this:

Convert the Pie Chart into the Pointer (Needle)

Align the Pie Chart with the Doughnut Chart

Now you need to align the pie chart with the doughnut chart. To make both charts work in collaboration, you need to realign the pie chart by 270 degrees as you did before. Right-click on the pie chart and open ‘Format Data Series’ and set the Angle of the first slice to ‘270°’ for the Pie chart.

Remove Pie Chart Borders

Next, you need to remove the borders of the pie chart as you did for the doughnut chart. Switch to the ‘Fill & Line’ tab, and select the ‘No line’ option under the ‘Border’ section to remove the chart border.

Now, the chart has three slices: The grey slice, the blue slice, and the orange sliver at the 12 o’clock position in the screenshot above.

Make the Pointer

To make the needle/pointer, you need to hide the pie chart’s grey section (pointer slice) and blue section (Rest value slice) to leave the needle part only.

You can hide the slices of the pie chart as you did for the doughnut chart. Double click on the grey data point and then right-click on it to select ‘Format Data Point’, and go to the ‘FIll & Line’ tab, check ‘No Fill’ in the Fill section. Follow the same steps to hide the next big slice (Blue) of the pie chart so that only the pointer (Orange slice) remains.

Next, select the pointer slice and go to the ‘Fill & Line’ tab, click the ‘Fill Color’ icon in the ‘Fill’ section, and change needle color using black (Choose your preferred color).

The speedometer is ready:

How Gauge Chart Works in Excel

Now we created the gauge chart, let us show you how it works. It’s easy.

Now, whenever you change the value corresponding to Pointer in the second table, the needle will move.

But before we do that, Let’s add a custom data label (text box) for the needle that automatically updates with the value represented by the speedometer. Doing this will make your chart much more intuitive and comprehensible.

To do that, switch to the ‘Insert’ tab in the Ribbon and click ‘Text Box’ icon from Text group.

Then go to your chart, click and insert the text box to your desired size as shown below. Click on the text box to edit it, go to the formula bar and type a ‘=’ symbol, and select the cell E3 (Pointer Value), and then press the ‘Enter’ key. This will link the text box with cell E3. Next, Format the text box to whatever you feel is appropriate.

The pointer value is the performance that you want to measure or assess. This determines how far on the gauge chart you want the needle/pointer to be.

Now, this where the third table (Chart Data) comes into play. This table contains students’ marks for which we want asses the performance.

From now on, whenever you change the value in cell E3, the needle will automatically move and the text box’s value below the needle will update as well. You can change the pointer value with each student’s mark to assess their performance.