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 leading BI providers too. When you take a look at the above visualizations, it is clear why it is so widely used.
When we talk about key performance indicators, the use of gauge charts is essential. In today’s tutorial, we show you the making of the gauge chart with the use of 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. A solution was needed that gives 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 most important part of the article, and let’s create our chart. We’ll talk about every little detail in the next chapter. The gauge chart is a key element of the Excel dashboard; we can even call it the industry standard.
How to create a Gauge chart in Excel?
1. Select the data range you want the speedometer shown! 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. Select the D3:E6 range. Click to the Insert Tab on the ribbon. To create a custom combination chart, select the Combo Charts Group. Click to the ‘Create custom combo chart’ icon.
3. Choose Doughnut as the chart type for Series 1 and Pie chart type for Series 2. You should mark the checkbox to Plot the Pie series on the secondary axis.
4. Click the OK button!
5. Remove the title, border, background fill, and legend.
6. Select the chart area. Select the Format tab on the ribbon. Choose the Pie series using the drop-down list on the top-left corner. You can find this tool in the ‘Current Selection’ group.
Format Data series
7. 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.
8. Choose the Doughnut Chart Series. Repeat the last step and change the Doughnut Chart Hole size to 80%.
9. Use the Ctrl and the left or right arrows to change the data points. As first, click on the Format tab. Check the Shape Styles group. Change the Shape Fill of each point. In this example, use fill for Point 2 and no fill for Point 1 and Point 3.
10. 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.
An explanation for Chart Series
Let us see how the change the actual value between 0 and 100. Edit the value in cell E5. 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 be equal to 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 insights in an easier and more understandable way, 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. On the left side chart, we created more zones than the usually used three. Why is this good for us? We can even portray 10 zones, just think about the end result of a customer satisfaction survey (for example, 1 – not satisfied, 10 – very satisfied). Not bad, isn’t it? If someone knows a better realization for this task, feel free to write to us.
On the right side, there is a dual gauge chart, with its help variations are easily portrayed. We wanted to dispel misconceptions that the gauge chart isn’t capable of displaying plan vs. actual type indicators. This is a function not widely used, although it is very spectacular. 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. We can show the trend with line charts. The bar chart can be greatly used for comparisons, and the bullet chart can be useful when the target vs. actual comparison is the goal.
We have already written a long tutorial about Excel heat maps. Its characteristic is a representation of the relations of complex data sets where values are displayed by colors.
Create attention-grabbing visualizations
At Excelkid.com, we believe that 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. If we create a multi-zone chart, we can count on it even with the solution of complex problems.
What can we say about that little group of people who simply reject its use at first glance? To them, we recommend walking with open eyes in the world of Excel charts and BI. After a little experience with them, and they will realize it is hard to be without them.