How to Create a Gauge Chart in Excel (With Easy Steps)

Sometimes we need to create a Gauge Chart in Excel to save our time and make our life more efficient. It is an eye-catching visualization of the minimum, maximum & current values measured to compare with the desired goal. It is also known as Speedometer Chart or Dial Chart. This article will teach us to create a Gauge Chart in Excel with step-by-step procedures and explanations.

How to Create a Gauge Chart in Excel: Step by Step Procedures

We cannot find any built-in Excel Gauge Chart. For this reason, we create it when it’s needed for work. The necessary steps to create a gauge chart are given below.

Step 1: Create Dataset

First, we need to create a dataset for building a gauge chart.

• The first data table contains the category of performance level with the corresponding value limit.
• The second data table is the needle data. It contains the real pointer value which we need to track.
• Here, it also contains the width of the pointer (1) and the end value (174). We can get the end value with the sum of the performance data including the total (20 + 40 + 20 + 20 + 100 = 200) and divide the output by the sum of the real pointer value (F6) and needle width (F7). The formula is given below.

• Finally, the third data table is the chart data. It contains meter labels ranging from 0 to 100. The value difference of each range is 10. We can change it according to our needs.

Step 2: Create Doughnut Chart (Performance Data)

Now, we will create a doughnut chart with the help of performance data.

• First, go to the Insert tab.
• Next, from the Charts group, select Insert Pie or Doughnut Chart drop-down.
• Now select the Doughnut chart.

• We can see a blank chart (Chart 1) here.
• Secondly, right-click on the chart and choose Select Data from the Context Menu.

• This will take us to the Select Data Source window.
• Click on Add from the Legend Entries (Series) portion.

• After clicking, we can see the Edit Series dialogue box.
• Here choose the Series name and the Series values from the performance data table.
• Then click on OK.

• Again click OK.

• Finally, we can see a doughnut chart like the one below. Remove the unnecessary parts like chart title & legend to make it more visible and simple.

Step 3: Rotate Doughnut Chart

Thirdly, we need to rotate the doughnut chart. To do that:

• Right-click on the chart (Chart 1).
• Here, from the Context Menu, select Format Data Series.

• Now, we can see a Format Data Series window on the right side of the sheet.
• Then, from the Series Options tab, write down the Angle of first slice as 270 and hit Enter.
• In the end, we can see that the chart is rotated as per our requirement.

Step 4: Format Doughnut Chart

Here, we are going to format the doughnut chart.

• First, right-click on the bottom part of the chart.
• After that, select Format Data Point from the Context Menu.
• Next, go to the Fill & Line tab of the Series Options.
• Now, select Fill as No fill. We can see that the bottom part became invisible.

• Then we are going to change the color of every portion of the chart to make it more attractive as well as visible.
• To do that, double-click on any portion of the chart and it will open the Format Data Point window.
• Select the Fill as Solid fill.
• Furthermore, change the color from the Color drop-down and select the Border as No line.
• Consequently, we can see that the colors are changed and there is no border around the portion of the chart.

Step 5: Insert Second Doughnut Chart (Chart Data)

We will create the second doughnut chart with the help of chart data.

• Likewise the previous steps,

Right-click on the existing chart > Choose Select Data > Select Add from the Select Data Source window > Insert Series name and Series values from the chart data > OK.

• Again click OK.
• After clicking OK, the chart will look like the below screenshot.

• Now, we will format this second doughnut chart like in the previous steps.

Right-click on the chart’s bottom part > Format Data Point > Fill & Line > Fill as No fill > Color choosing from the Color drop-down > Border as No line.

• Our final chart will look like the one below.

Step 6: Add Pie Chart (Needle Data)

In the end, we are going to add a pie chart from the needle data.

• Again right-click on the existing chart.
• After that, Select Data > Choose Add from Select Data Source.
• Further, input the Series name and Series values from the chart data.
• In the end, click OK.

• Then, go to the Chart Design tab.
• Select Change Chart Type from the Type section.

• The Change Chart Type window will open up.
• Go to the All Charts tab and select the Combo section.
• Now click on the Custom Combination option.
• Next, in the Choose the chart type and axis for your data series section, select the Needle Data Chart Type as Pie from the drop-down and make it a Secondary Axis by giving a tick mark.
• Finally, click on OK (See Screenshot).

Step 7: Alignment of Pie Chart

Alignment of the pie chart is necessary now.

• In the beginning, right-click on the chart.
• Format Data Series > Series Options > Click Secondary Axis from Plot Series On option > Insert Angle of first slice as 270 > Enter.

Read More: How to Create Meter Chart in Excel

Step 8: Pie Chart Formatting

To format a pie chart

• First, select Format Data Point by right-clicking on the big part of the chart.

• Next, Fill it as No fill so the big part becomes invisible.

• After that, select another big part of the chart and Fill it as No fill.
• Again, select the small portion of the part.
• Here, Fill it as Solid fill.
• Now, choose any color from the Color box.
• Also, make the Border as No line.
• Finally, we can see the pointer part.

Step 9: Insert Data Labels on Doughnut Charts

Here, we will insert data labels on doughnut charts. To do that:

• Go to the Format tab first.
• Then click on the Current Selection drop-down.
• From here, choose the desired doughnut chart (Series “Performance Data”).

• We can see that the performance data chart is selected.
• Now click on the Chart Elements.
• Go to the Data Labels > More Options.

• We can see the Format Data Labels window here.
• In the Label Options group, uncheck the Value option.
• Also, check the mark on the Value From Cells option.

• After that, click on the Select Range button to select the value from the cells.
• We can see a Data Label Range window.
• Here, input the cells and click OK (See Screenshot).

• As a result, the labels are shown in the chart.

• Follow the same procedure to add labels on another doughnut chart.

Step 10: Data Labels Adding to Pie Chart

Now, we are going to add data labels to the pie chart.

• Go to Format tab > Current Selection > Series “Needle Data” > Chart Element > Data Labels > More Options.

• Again, Uncheck the Value option > Check the mark on the Value From Cells option > Click on the Select Range button.
• In the Data Label Range window, select the Pointer value and click OK.

• Then, make the Label Position as Center.

• Finally, we can see the pointer label is added.

Step 11: Format Data Labels

We can see that the labels are inside the chart. We will format this.

• Select the labels of the desired chart.
• After that, drag the labels to the desired position.
• Also, the Format Data Labels window is showing.
• From the Label Options section, uncheck the Show Leader Lines option.

Final Output

• Finally, our gauge chart looks like the screenshot below.

• Further, if we change the pointer value to 55 in cell F6, we can see that the gauge chart is also changing automatically.

How to Create Animated Gauge Chart in Excel

Sometimes, we need to create an animated gauge chart in excel to make it more alive. Follow the steps below to understand how to do that.

Step 1: Make Dataset

We need to make a dataset. For that:

• First, take a new workbook.
• Now, in cell C5, enter the Pointer value (65%).
• Again, in cell E5, enter the same Pointer value (65%).
• Secondly, we will take some label values. For Full, enter the below formula in cell H5. It will be the same as the Pointer value (65%).

• For Half, in cell H6, enter the below formula:

• In the end, take the Empty value as Default 50%.

• Thirdly, we will enter some data values to get the needle chart.
• In cell K5, enter the below formula similar to the pointer value.

• Next, take the Needle width of .50%.
• To get the Empty value, we will use the below formula:

Here, 150 is the total of label values (65 + 35 + 50).

Step 2: Insert Doughnut Chart

• Now, select the cell range G5:H7.
• Then go to Insert tab > Insert Pie or Doughnut Chart > Doughnut chart.

Step 3: Doughnut Chart Rotation

• Remove the chart title and chart labels.
• Next, select the chart and right-click > Format Data Series.

• After that, set the Angle of first slice as 240 and hit the Enter button.
• Input 40% in the Doughnut Hole Size box.
• The output will look like the below screenshot.

Step 4: Format Doughnut Chart

• Here to remove the outline of each portion of the chart, go to the Format tab first after selecting the chart.
• Further, Shape Outline drop-down > No Outline.

• Then double-click on the Gray portion of the chart. It will open the Format Data Point window.
• Set the Fill as No fill from the Series Options. It’ll make that part invisible.

• Furthermore, choose the Orange part of the chart.
• Go to Fill > Solid fill.
• Choose the color from the Color box.

• Again, select the Blue part of the chart.
• Now Fill > Gradient fill.
• Choose any default gradient from the Preset gradients option.
• Also, choose the Direction of the gradient.

Step 5: Insert Pie Chart

• Select the chart > Select Data.

• Choose Add from the Select Data Source window.

• Here, input the Series values from the chart data.
• Leave the Series name blank.
• Click on OK.

• Now in the Select Data Source window, click on the Edit option from the Horizontal (Category) Axis Labels part.

• Next, insert the Axis label range and click on OK. (See Screenshot)

• Again click OK.
• We can see another doughnut chart here.
• So right-click on the chart > select Change Series Chart Type.

• Then All Charts > Combo section > Custom Combination > Choose the chart type and axis for your data series > select the Series2 as Pie >  tick mark on Secondary Axis box > OK.

Step 6: Pie Chart Alignment

• First, Format tab > Shape Outline > No Outline.
• Next, right-click on the chart.
• Then select Format Data Series > Series Options > Click Secondary Axis from Plot Series On option.
• After that, enter the Angle of first slice as 240 > Enter.

Step 7: Format Pie Chart

• First, click on the blue part of the chart and double-click on it to open the Format Data Point window.
• Fill > No fill.
• Again select the gray part of the chart and set the Fill as No fill.
• Here, we can see the small pointer part.
• Double-click by selecting it.
• Further, set the Fill as Solid fill.
• Also, insert the color from the Color section.

• Secondly, select the Border as a Solid line.
• Also, choose the border line color from the Color box.
• In the end, insert the Width as 1pt.

• The final output will look like the below screenshot.

Step 8: Add Illustrations

Further, we will insert a shape and the pointer labels.

• In the beginning, go to the Insert tab.
• Illustrations > Shapes > Basic Shapes > select an oval shape.

• Insert it in the desired place.
• Now, select the Shape Fill drop-down and choose any color from the Theme Colors option.

• Again, select the Preset from the Shape Effects drop-down.
• Choose the Preset 7 option.

• Finally, our gauge chart will look like this (See screenshot).

Step 9: Insert Pointer Label

• Next, we are going to enter the data label.
• To add that, select the chart > Insert tab > Text > WordArt > choose any pattern.

• After that, place it in the desired location.
• Then select the WordArt > go to the Formula bar > Write Equals (=) sign and select the cell E5 > Hit Enter.
• The formula will look like the below:

• Finally, we can see the pointer label is added. We can change its format as per requirement.

Step 10: Launch VBA Window

Here, we can see that If we change the pointer value in cell E5, the gauge chart is also changing automatically. But we want to add the animation here. So we need to apply a VBA code. To do that:

• First, select the worksheet from the sheet bar.
• Next right-click on the sheet.
• Click on the View Code.

Step 11: Type & Run VBA Code

• A VBA Module window is opened here. We can open it by using the keyboard shortcut Alt + F11.
• Now type the below code:

Option Explicit
Private Sub Worksheet_Activate()
Dim k As Integer
For k = 1 To Int(Range("C5").Value * 200)
VBA.DoEvents
Range("E5").Value = k / 200
Next k
Range("E5").Value = Range("C5").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
Dim k As Integer
For k = 1 To Int(Target.Value * 200)
DoEvents
Range("E5").Value = k / 200
Next k
Range("E5").Value = Target.Value
End If
End Sub

• Then click on the Run option or we can simply press the F5 key to run the code.

Step 12: Name & Run Macro

• Insert the Macro Name as VBA in the Macros window and select the Create button.

• A confirmation Macros window pops up.
• After that, select the sheet name and click on Run.

Final Output

• Finally, we can get our animated gauge chart.

Things to Remember

• We can use only the current data period here.
• Easy to understand.
• Applicable for the single data point.
• It takes a lot of time to create a gauge chart which is irritating sometimes.

Download Practice Workbook

Conclusion

By using these steps, we can create a Gauge Chart in Excel. There is a practice workbook added. Go ahead and give it a try. Visit the ExcelDemy website for more articles like this. Feel free to ask anything or suggest any new methods.

Related Articles

• How to Create Speedometer Chart in Excel
• How to Create Speedometer Chart with Two Needles in Excel

<< Go Back to Meter Chart in Excel | Excel Charts | Learn Excel