Create a Gauge Chart in Excel

[nerdslife_team]

Gauge charts, also known as dial or speedometer charts, are fantastic for showing progress towards a specific goal. They work like the speedometer in your car, with a needle pointing to a value on a scale.

While Excel doesn’t have a gauge chart as a standard option, you can build one by combining a doughnut chart and a pie chart. This lets you visualize a single data point’s performance against a predefined scale, from its minimum to maximum.

Setting Up Your Data

Before we get started, you’ll need to set up your data in a way that Excel can understand. You’ll need three small tables: one for the gauge’s dial, another for the needle, and an optional third one for labeling chart sections.

Your data should look like this:

The Dial

• Performance Labels: These are the words you want on the dial, like “Low”, “Medium”, and “High”.
• Levels: These values are what create the different sections of the speedometer.

The Pointer

The pointer data is what controls where the needle points.

• Pointer: This is the actual value that the needle should display.
• Thickness: This is how thick you want the needle to be. Keeping it below 5 usually looks best.
• Rest Value: This is the remaining part of the pie chart and can be calculated using this formula =200-(E3+E4) placed in cell E5.

Method 1: Using Doughnut and Pie Chart Combination

This method is the most effective for creating a dynamic gauge chart in excel.

Create a Doughnut Chart

1. Start by selecting the values under the “Levels” column for the dial data. Go to the Insert tab, find the Insert Pie or Doughnut Chart icon in the Charts group, and choose the Doughnut chart type.
   

   

2. Remove the default chart title and legend to keep things clean. Your doughnut chart will look like a semi-circle and should display a ‘Level: 100’ on one side.
   

   

Rotate the Doughnut Chart and Remove the Border

1. To re-orient the chart, right-click any of the colored segments and choose Format Data Series.
   

   

2. In the Format Data Series pane, set the Angle of first slice to 270 degrees. You can also adjust the Doughnut Hole Size if you want.
   

   

3. Remove the chart’s border by going to the Fill & Line icon, expanding the Border section, and selecting No line.
   

   

Transform the Doughnut Chart into a Semi-Circle

1. To hide the bottom half of the doughnut chart double click on the bottom slice to select it, opening the Format Data Point pane.
   

   

2. In the Fill & Line tab, choose No fill to make the bottom slice transparent and create a semi-circle.
   

   

Customize the Slice Colors

1. To make the chart look better, change the colors of the remaining slices. Select a slice by double-clicking it, then find the Fill Color option under the Fill & Line tab and pick a new color.
   

2. Repeat this for each section of the chart.
   

   

Add Data Labels

1. To display the labels, right-click on any slice, select Add Data Labels, and then Add Data Labels again.
   

   

2. This will show the level values. Delete the label for the bottom (transparent) slice to avoid clutter.

3. Right-click any data label, choose Format Data Labels, then click on Value From Cells
   

   

4. In the Data Label Range dialog, select the cells under ‘Performance Labels’ (except for ‘Total’) and click OK.
   

   

5. Uncheck the Values option in the Format Data Labels to only show your selected labels.
   

   

Create the Pointer Using a Pie Chart

1. To add the pointer, right-click the chart and select Select Data
   

   

2. In the Select Data Source dialog, click Add to open the Edit Series box. Type “Pointer” in the Series Name field. For Series values, delete the default value and select the range containing the ‘Pointer’, ‘Thickness’, and ‘Rest Value’ data from your pointer table (like E3:E5). Confirm by clicking OK on each window.
   

   

Convert the Pointer Doughnut Chart to a Pie Chart

1. Right-click on the chart and select Change Series Chart Type.
   

   

2. In the Change Chart Type window, go to Combo. Change the Chart Type for the ‘Pointer’ series to Pie. Check the Secondary Axis box for the pointer and then click OK to complete.
   

   
   
   Your chart will now have a pie chart on top of the doughnut chart, which is going to be our pointer.

Transform the Pie Chart into a Pointer (Needle)

Align the Pie Chart with the Doughnut Chart

1. Right-click the pie chart, and select Format Data Series. Set the Angle of first slice to 270 degrees.
   
   

Remove Pie Chart Borders

1. Remove borders from the pie chart by going to the Fill & Line tab, expanding the Border section, and selecting No line.
   
   
   
   Now you will have a pie chart with three slices, two large ones and a thin slice at the top.

Configure the Pointer Appearance

1. Hide the two larger slices of the pie chart by double-clicking a large slice, right-clicking, and selecting Format Data Point. Then, under Fill, choose No fill. Do this for the other large slice too.
   

   

2. Select the thin slice, which is now the needle, and in the Format Data Point pane, choose a fill color for it, such as black.
   

   

Your gauge chart now has a working needle.

See How the Gauge Chart Works

The needle’s position is controlled by the ‘Pointer’ value you entered in your pointer data table.

1. To show the value on the chart, go to Insert tab and click Text Box in the Text group.
   

   

2. Draw the text box on your chart, click inside it, type = in the formula bar, then select the cell with the ‘Pointer’ value (e.g., E3), and press Enter. You can then format the text as you want.
   

   

Now, whenever you change the ‘Pointer’ cell, both the needle and the displayed value will update. This helps you track data effectively.

Here’s an example of a gauge chart in action, with updated data.

You can change the needle’s thickness by changing the ‘Thickness’ value in your data table.

By following these steps, you have created a gauge chart in Excel that dynamically represents data against a predefined scale.