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 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 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 create our chart. After that, we’ll talk about every little detail in the next chapter. The gauge chart is a key element of the 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 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. 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. Choose Doughnut as the chart type for Series 1 and Pie chart type for Series 2. Make sure that 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. 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.
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. 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 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 more easily 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 (for example, 1 – not satisfied, 10 – very satisfied). Not bad. If someone knows a better realization for this task, feel free to write to us.
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. 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 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. 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 first glance? To them, we recommend walking with open eyes in Excel charts and BI. After a bit of experience with them, they will realize it is hard to be without them.