Learn how to create a gauge Chart in Excel! A doughnut chart displays the zones, and a pie chart indicates the actual value.
The popularity of the gauge chart is indisputable. It’s a combination chart: a doughnut that displays the zones, and a pie chart indicates the actual value. Not only is it used by Excel PowerBI but by leading BI providers too. When you look at the above visualizations, it is clear why it is so widely used.
When we talk about key performance indicators, gauge charts are essential. In today’s tutorial, we show you the making of the gauge chart using Excel. Furthermore, we’ll assess the pros and cons. Finally, we provide you with chart templates free to download.
What is a gauge chart?
The concept of the gauge charts can be taken back to the dashboards of the cars. How easy is it to read the current speed? Probably this is why one other name for the chart is the speedometer. This is how the gauge chart developed. They needed a solution that provided immediate feedback about the actual status of the indicators.
These days a CEO doesn’t have the time to examine reports lengthily. So a solution was needed that gave the feedback ASAP. “A” or “B,” “yes or no” is that simple. We decide and, if necessary, intervene.
After this introduction, let’s see the essential part of the article and create our chart. After that, we’ll discuss every detail in the next chapter. The gauge chart is a key dashboard element; we can even call it the industry standard.
How to create a Gauge chart in Excel?
#1 – Select the data
First, select the data range you want the speedometer to show! In this example, choose the range D3:E6 (Column D for Doughnut Chart) and (Column E for Pie Chart). The Pie chart series is based on 3 data points, and the Doughnut chart series has 4 data points.
#2 – Create a custom combination chart
Select the D3:E6 range. Next, click on the Insert Tab on the ribbon. To create a custom combination chart, select the Combo Charts Group. Then, click on the ‘Create custom combo chart’ icon.
#3 – Select the pie chart and check the secondary axis
Choose Doughnut as the chart type for Series 1 and Pie chart type for Series 2. Make sure you mark the checkbox to Plot the Pie series on the secondary axis.
#4 – Clean up the chart
Click the OK button, then remove the title, border, background fill, and legend.
#5 – Select the Pie chart series
Select the chart area. Select the Format tab on the ribbon. Choose the Pie series using the drop-down list in the top-left corner. You can find this tool in the ‘Current Selection’ group.
#6 – Format Data Series
Select the Format tab. Click the Format Selection type In the Current Selection group. Move the slider right to add 240 degrees to the Angle of the first slice field.
#7 – Change the doughnut hole size
Choose the Doughnut Chart Series. Then, repeat the last step and change the Doughnut Chart Hole size to 80%.
#8 – Apply No fill for Point 1 and Point 3 on the Pie series
Change the data points by changing the Ctrl and the left or right arrows. First, click on the Format tab. Next, check the Shape Styles group. Finally, change the Shape Fill of each point. This example uses fill for Point 2 and no fill for Point 1 and Point 3.
#9 – Apply red, yellow, and green colors on the Doughnut chart
Use the same method as Step 9. Select the Dougnut Chart series and Shape Fill of each point. Use red color for Point 1, yellow for Point 2, and green for point 3. Finally, use no fill for Point 4.
Explanation
Let us see how the actual value changes between 0 and 100. First, edit the value in cell E5. Then, the formula in cell E7 ensures that the red, yellow, and green slices sum up to 150 points.
Modify the upper and lower limits. Only one thing: Red Zone + Yellow Zone + Green Zone must equal 100.
Advantages of Gauge Charts
Let’s see why it is so popular! Here we’ll see a short list containing the advantages of the chart. Gauge Chart provides actionable insight more efficiently and understandably, and it’s a perfect decision to:
- Measure the work completed vs. total work ratio
- Create an alert when KPIs reach a threshold value
- Create sales vs. target comparison
- Check the current rating of a product
- Track project status
Disadvantages of Gauge Charts
There are many advantages, although there are some disadvantages also.
- Its space consumption is more than that of a regular chart
- Only the maximum two values can be displayed on it
Extended usability: Multi-zones and Dual Gauge Chart
In the figures below, we show two unusual solutions. First, we created more zones on the left side chart than the usually used three. Why is this good for us? We can even display ten zones and think about a customer satisfaction survey (1 – not satisfied, 10 – very satisfied). Not bad. Feel free to write to us if someone knows a better realization for this task.
There is a dual gauge chart on the right side. With its help, it’s easy to show the variance. We wanted to dispel misconceptions that the gauge chart cannot display plan vs. actual type indicators. This is a function not widely used, although it is visually effective. There is a demanding user class that can’t stand reports based on column or bar charts.
Alternatives of gauge charts
But there are situations when it’s best to use other solutions. For example, we can show the trend with line charts. The bar chart is great for comparisons, and the bullet chart can be helpful if your goal is to create a target vs. an actual comparison.
We have already written a long tutorial about Excel heat maps. Its characteristic represents the relations of complex data sets where colors display values.
Create attention-grabbing visualizations
At Excelkid.com, we believe the gauge chart is the best tool for revolutionary data visualization in Excel. We can examine one value (radial gauge chart) or differences (dual gauge chart); its use is evident. Moreover, if we create a multi-zone chart, we can count on it even with complex problems.
What can we say about that little group of people who reject its use at first glance? To them, we recommend walking with open eyes in Excel charts and BI. After some experience with them, they will realize it is hard to be without them.
Additional resources:
