A gauge chart (also known as dial or speedometer) is a type of visualization that typically displays one data field on a maximum-minimum scale. The fact that a single value is shown sets gauges aside from other visualizations.

Although gauges are commonly used in dashboard reports to show various metrics like Key Performance Indicators (KPI), Excel doesn't have built-in support for this visualization. In this guide, we are going to show you how to create a gauge in Excel using some workarounds. You can download our sample workbook below.

Gauge Chart Excel Prep

Let’s begin by preparing our dataset. We’re going to need data points for plotting them on the gauge, as well as additional helper numbers to draw a needle (pointer).

• Data to show: Basically, the value the dial (pointer) will be displaying.
• Labels and value for Levels: The value ranges that represent the levels. You can think of this as speed values on a speedometer. Remember to add totals to last row as this total will be the lowest part of the half-circle gauge.
• Helper for Pointer: The helper values which will replace our pointer. These values help us determine where to place the needle of the speedometer.
  • Width: The width of the needle.
  • Start: The point at which needle will start.
  • End: The value of the remaining arc of the pie. The needle is a part of a pie chart where the needle is only partially visible. To complete the pie chart and place the needle, the End value should be calculated by subtracting the width and start points from the sum of values of levels. In this example, we can find this as 1210 – (10 + 529) = 861.

Combining Charts

To create a gauge chart, you need to combine a doughnut chart and a pie chart. While the doughnut chart will show the levels, the pie chart will be the needle.

Begin by selecting the levels table and insert a doughnut chart. To insert a doughnut chart go to INSERT > Charts > Pie > Doughnut

After removing the title and the legend, you will get a chart that is a half-circle on one side, and the remaining parts on the other.

The solid half will be the bottom section of the gauge, so we need to rotate the chart. Right-click on a colored part and click on Format Data Series. This will make the right-side panel visible for the chart. Set Angle of first slice to 270°.

The next step is hiding the bottom part. Right-click on the bottom half and click on Fill to see the color options. Select No Fill to make the half-circle transparent.

Now you have the first part of your gauge. You can also add data labels to make the gauge easier to read. Remember to remove the label for the Total in the half-circle part.

Let's continue with the needle. We have already mentioned that the pointer would be a part of a pie chart. The trick is to create the pie chart inside the doughnut chart. This can be done using a Combo Chart.

First, add the pointer values into the existing chart. Right-click on a chart area, and click on Select Data. In Select Data Source dialog, click on Add button and select the range that contains Width, Start, End for the Series values input. Also remember to enter a name for Series name.

This is our selection:

Now, we need to convert the pointer doughnut chart to a pie chart. First, open the change Chart Type dialog by right-clicking on the new doughnut chart, click Change Series Data Type…, and then select Pie option for the pointer values.

We also need to set the Angle of first slice to 270° for the Pie chart. You will see that the transparent part becomes visible again with the pie chart. Hide the doughnut chart's bottom half with the pie chart's start and end parts to leave the needle part only.

It is done! Here are some tips to enhance your visualization:

• Add additional levels following the steps we used when adding the pie chart.
• Add a data label for the pointer (pie chart) and move it to the center.
• Increase the Pie Explosion value for the pie chart to make pointer stick out from the doughnut chart like below.

A tip for the width of the pointer: You can adjust the width of the pointer by changing the value for the Width cell. Try to use lowest value possible. Since we used 10 in our example, we chose to start the pointer from a bit before (529) than the actual value of 534. We reached the number 529 by subtracting the half value of the width (10 / 2 = 5) from the actual value of 534.