The popularity of the gauge chart is indisputable. It’s a combination chart: a doughnut that displays the zones and a pie chart indicate the actual value.
Not only is it used by Excel PowerBI but leading BI providers too. (Like QlikView, Dundas BI, ZingChart.) 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 its 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 it is to read the current speed? Probably this is why one other name for the chart is speedometer. This is how 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 lengthily examine reports. A solution was needed that gives the feedback ASAP. “A” or “B”, “yes or no” it 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 order to make the creation easily understandable for you. The gauge chart is a key element of Excel dashboard, we can even call it industry standard.
How to create Gauge chart in Excel?
To build a Gauge Chart in Excel, follow these instructions:
Step 1. First select the data range you want the speedometer shown! In this example choose the range C4:F8 (Column D for Doughnut Chart) and (Column F for Pie Chart)
The Pie chart series based on 3 data points and the Doughnut chart series has 4 data points.
Step 2. Select the D5:E8 range. Click to the Insert Tab on the ribbon. To create custom combination chart, select the Combo Charts Group. Click to ‘Create custom combo chart’ icon. The default chart will be inserted.
Step 3. Choose Doughnut as the chart type for Series 1 and Pie chart type for Series 2. You should to mark the checkbox to Plot the Pie series on the secondary axis.
Step 4. Click to OK button!
Step 5. You should remove the title, border, background fill and the legend to clean up the combo chart area.
Step 6. Now select the chart area. Go to the Format tab on the ribbon. Select the Pie series using the drop-down list on the top-left corner. You can find this tool on the ‘Current Selection’ group.
Step 7. Select the Format tab. In the Current Selection group, click Format Selection type. Move the slider right to add 240 degrees into Angle of first slice field.
Step 8. Choose the Doughnut Chart Series. Repeat the last step and change the Doughnut Chart Hole size to 80%.
Step 9. Use the Ctrl and the left or right arrows to change the data points. Click on the Format tab then 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. You can use 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 for Chart Series:
To change the actual value between 0 and 100 change the value in cell E5. The formula in cell E7 ensures that the red, yellow and green slices sum up to 150 points.
You can change the upper and lower limits of zones. 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 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 maximum two values can be displayed on it
Extended usability: Multi-zones and Dual Gauge Chart
On 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 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 portraying plan vs. actual type indicators. This is a function not widely used although it is very spectacular. There is a demanding user class who can’t stand reports based on column or bar charts. The now approach of variance portrayal important to users open to novelties.
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 target vs. actual comparison is the goal.
We have already wrote a long tutorial about the Excel heat maps. Its characteristic is a representation of the relations of complex data sets where values are displayed by colors.
Download free gauge chart add-in
We would like to share our free chart tool! It’s free for personal use. You can create 5 gauge charts by worksheets.
Conclusion – Create attention-grabbing visualizations
At offvis.com we believe that the gauge chart is the best tool of 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 sophisticated problems.
What can we say about that little group of people who simply reject its use from first glance? To them we recommend to walk 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.