Table of Contents
- 1 What is a Gauge Chart and how to use it?
- 2 To create a Gauge Chart, do the following steps
- 3 Real life Gauge Chart example
- 4 Creating Gauge Chart using macros
- 5 How to create Dual Gauge Chart?
- 6 Why should we use Gauge Charts in Excel?
- 7 Create live dashboards using Gauge Charts
- 8 Additional resources: Free Gauge Chart Templates
In this gauge chart tutorial, you will get to know everything about the speedometers. And how this will go? The following will be an irregular Excel tutorial! We can even call it a summary. We will show you the creation of the gauge chart and all of the articles we have written about this subject in the past years. First of all we’d like to give you a comprehensive picture about the speedometer kind of excel dashboard templates!
Maybe some of our notes will be a little bit dividing and provocative. And we proudly own up to this. With 15 years of Excel experience behind us, we will not be making a slip in speaking. You have to know that nothing is impossible with the use of the gauge chart! And as usual, we have not forgotten about the free solutions either, there will be several Excel templates downloadable for free.
What is a Gauge Chart and how to use it?
The speedometer chart template offers a spectacular visualization to present sales performance of representatives. In Excel there are more tricky ways to create the gauge chart, we will show you the designs that are built on the pie chart and doughnut chart.
The created gauge could be a useful instrument for other (not only for marketing/sales) fields also for visualization between the goal and actual values. With a little modification it is able to show not only the 0-100% range but to display other arbitrary values on the speedometer graph. In this detailed tutorial we’ll show you how to create gauge chart from scratch!
To create a Gauge Chart, do the following steps
1. Specify the value range and parts you want the speedometer chart shown! Select the range F6:G10 (Column F for Donut Chart – Zone Settings) and (Column G for Pie Chart – Ticker Settings) The Pie series has 3 data points and the Donut chart series has 4 data points.
2. Go to the Insert Tab on the ribbon and in the Charts Group, click to Create Custom Combo chart icon to create the default chart.
3. Choose Doughnut as the chart type. For the Pie series, choose Pie as the chart type. It’s very important to mark the checkbox to Plot the Pie series on the secondary axis.
4. Click to OK button!
5. Clean up the combo chart: Remove the chart title and the legend.
6. Select the chart area and go to the Format tab on the ribbon. In the Current Selection group, select the Pie series using the drop-down list.
7. On the Format tab, in the Current Selection group, click Format Selection type 240 degrees into Angle of first slice textbox.
8. Choose the Donut Chart Series and Repeat the Step 7! If you want want to change the Donut Chart Hole Size change the default value to 70%
9. Select the Pie Chart series! Use the Ctrl and the left / right arrows to select / change a single data point. On the Format tab, in the Shape Styles group, change the Shape Fill of each point. Point 1 = No Fill, point 2 = black and point 3 = No Fill.
10. Select the Donut Chart series! Use the Ctrl and the left / right arrows to select / change the Shape Fill of each point. Point 1 = Red, point 2 = Yellow, point 3 = Green, point 4 = No fill.
11. Explanation for Pie Chart Series: How to change the actual value between 0 and 100? First click on the cell G9 and check the formula. Use the Spin Button to change the value in cell G7. The formula in cell G9 ensures that the 3 slices sum up to 150 points.
Explanation for Donut Chart Series: You can change the Red, yellow and green zone values freely. Importan rules: Red zone + Yellow Zone + Green Zone = 100!
Excel Speedometers – Love it or Hate it?
This question can be heard a lot of times, when we talked with Excel experts, even the opinions of the professionals are divided if it is lucky to use them for displaying key performance indicators.
When we are running in the wrong direction there is no sense in increasing the speed. We completely agree with this remark, but sometimes it is worth to spend much energy and time for development so we can create something totally new and unique.
This is our case and we hope that the speedometer will be a very favored tool!
There are some gauge chart tutorials for them also. In this part we’d like to introduce the use of gauge chart for the beginners. The following dashboards are all absolutely free. We’ll present them below, and will say a few words about their usage.
We have also used some graphics in order for you to see the almost endless possibilities of the speedometer dashboard.
Real life Gauge Chart example
In the first step we have to create the data structures used by the dashboard and fill them up with the desired values. For this we use four simple data tables:
In the first data table there is the performance of the sales representatives (their name, actual value and goal value) which here we filled out with arbitrary values.
To the second data table “Selected Sales rep” we put the data of selected sales representatives from the dashboard (in K7 cells) from the Sales Performance table for which we used the VLOOKUP function.
The third “Zone Settings” chart controls the colors of the speedometer red, yellow and green zones. We talk about this in detail in the 2. Step. The fourth data table controls the ticker of the speedometer. We talk about this in detail in the 3. Step.
Color zones for Gauges
We create the color zones of the gauge with a help of a doughnut chart. In diagram controller chart 3 first we determine the values we consider bad (red zone), acceptable (orange zone), and good (green zone) comparing the actual values to the goal values (in our example 35%, 35% and 30%).
After this we fill out the proper values to the proper places in the chart, then into the hidden zone/section write 50. (Into the hidden zone/section we can write a number other than 50, but then the later be created doughnut chart wouldn’t be 240 degree but will show the color zones in a different range.)
After the fill out choose Charts and “Insert > Charts > Other Chart” menu choose doughnut chart. After this right click on the area of the chart and choose “Format chart area” option.
With the help of the highlighted cells on the right hand side picture turn the diagram so the “hidden zone” will be on the bottom. Also set the inside size of the doughnut chart to 90% so that the outside colored zone be thinner.
When we done with this we will do the unique filling of the color zones. Right click on the given zone and choose the Format Data Point option. Under the Fill option fill the given sections with arbitrary ways and color-shades (in our example we have chosen the color-transition filling), also in the hidden zone chose no filling.
Creating the indicator of the gauge chart
The whole values of the cells of Table 4, that controls the speedometer, and whole values of Table 3 has to be the same in order for proper operation. We create the pie chart after filling out the values in the following manner.
Right click the given doughnut chart and chose the “select data” option. Click on the “Add” button and mark the values of chart 4 (H15:H17). Than Excel will create a new doughnut chart over the already existing one.
Right click this new diagram and chose “Change series chart type” option, and in the pop up box chose the two dimensional pie chart.
To create the selector stripe click in cell L7, than on the Data ribbon select the “Data validation” option. In order for the stripe better appearance in our example we wrote out the goal, fact and status (%) values, also under the conditional formatting button we assigned the selecting stripe with the value responding colors corresponding to Status (%) values. Follow our excel dashboard training!
Creating Gauge Chart using macros
After one year of our first article ‘Building Excel KPI Dashboard with Gauge control’ we have a new and totally redesigned gauge dashboard template for you. It is better, faster and easier than the previous one.
It’s a big problem with Excel that it doesn’t have a speedometer chart template as a default chart type. We will help you creating excel gauge chart template or a great looking excel dashboard.
Each segment is representing a color range (typically red, yellow and green). It is an easy and efficient way to display a quick status update and to present real time changes.
Build Excel speedometer graph form scracth!
We would like to turn away from the misbelief that complex problems can only be solved by complex solutions. In order for the dashboard to be easily understood we abolished the complex formulas and come up with a new idea.
Finally, you don’t have to be an experienced programmer to succeed operating more complex dashboards.
Are you ready for the ride in the world of dashboards? The real speedometer dashboard provides key performance information about current indicators. You are in the right place at the right time to learn how to create Excel Gauge charts.
Color calculations for Gauge widgets
On the next picture we show you briefly how simple it is. It is enough for you to fill up the chart with variable values regarding the illustrated 24 products (of course you can extend this).
The little VBA code we used makes it possible to automatically divide the scale for 16 equal segments and fill the colors according to this.
Regarding calculations we strived to make the logic of the dashboard quickly and easily understandable. On the calculation sheet you can see the structure of the dashboard.
Updating the chart colors
Changing gauge colors has never been so easy! Although we shouldn’t even note this, you can use a color chart of your liking because the template gives you an option to do so.
Look at the picture and you can see the updated speedometer! And this is what the “Speedometer” looks like. The biggest change compared to the former one is that you can give a scale or value not only between 0 and 100 but any optional interval.
You can find this option under the worksheet “Scales and Colors” tab. Let’s say a few words about the needle. Following the well-tried practice didn’t over complicate the matter.
Just add the actual data to the products table and the gauge and needle position will be calculated automatically. Use the product selector on the Sheet ‘Dashboard’ to select the product. We used the Excel’s data validation tool to create a dropdown list.
A simple VBA Excel color changer function can be used to simplify our gauge colors on the spreadsheet. You can implement this gauge on your product comparison or market share template.
You can also use this in several other fields the possibilities are uncountable.
VBA programmed Speedometers
The possibilities of the VBA language are basically endless if you consider taking up Excel more seriously it is worth to get into VBA programming. You can use this knowledge not only exclusive to Excel, but other fields as well. The good news is that the following Excel versions will also use this programming language.
Our opinion using gauges for business dashboards or interactive visualizations in Excel is a perfect decision. It is an efficient method to manipulate your data without the need for difficult procedures.
To sum up the most important advantages in a few thoughts: don’t require you operate a chart or formula; Gauges are presented in our dashboard templates, prepared for you to input and display your raw data.
From now on you can simply use this little tool!
How to create Dual Gauge Chart?
Dual Gauge Template is our most liked free gauge chart template!
It gives up the usual data-visualization and beyond this enables the spectacular portrayal of the differences between the plan / actual values.
Many Excel expert criticized speedometer kind of solutions, so we have created a unique, never published example.
We hope this will convince the skeptics.
The development of the dual gauge template is done! And exciting Excel story starts today, with the help of the dual gauge template never seen dashboards waits for you!
And why are we so happy? You will soon find out!
Stay with us today, we show you a real novelty.
After the well-known Excel charts and dull dashboards, we have experimented with something completely new. The development phase is over, and we happily share with you our experience.
Until now their biggest setback was that they could only display only one value (and we agree with this fact), it was impossible to compare two different values with them.
The dual gauge template puts an end to this condition!
As you can see it on the picture we have portrayed a plan / actual difference simply and spectacularly. Below we’ll show you how to create this chart.
Dual Gauge Template – Under the hood
For starter we tell you that two general speedometer templates were built together in that way so that it can provide the previously noted functions (the display of more values at the same time).
We have detailed the creation of these kinds of charts several times in our previous articles, so today we’ll give only a short summery.
Let’s see the facts!
On the picture you can see two donut rings. We are able to represent values on the inner one and also on the outer one. In this example the plan / actual indicator will play the main role.
On the picture below on the outer ring you can see the settings of the Plan part – in this case 138. We can set the red-orange-green scale sections in the first three rows in the ‘Scale Settings’ field.
The must know information about it that because the scale is in percentage the sum of the three values can only be 100. Here the color scale is divided in rate 30-40-30, but this can be anything you want if you apply the above noted rule.
For example, there can be a division of 10-20-70, here the red zone will be very small, the orange zone will be a little bigger, and 70% of the ring will be filled with green.
It must be said of these parameters that you have to set them accordingly to the given situation. The 10-20-70 is a good example when the examined value can be almost anything; we only consider the lower 10% bad.
The ‘Target Settings’ offers two setting options. The Target field controls the maximum value we would like to display. The very important thing is the value of the ‘Current (Plan)’ field. We have highlighted this setting with burgundy, here the value can be set that indicates Plan value.
Let’s stop here for a moment! This dashboard is called dual gauge chart template because it is based on the combination of two speedometers. There is a very similar help tablet for the inner ring. The difference is only that in the field highlighted with burgundy there is the ‘Actual’ value, in this example it is 88.
If you don’t like this chart type, we recommend the bullet chart to display variance.
We can imagine the great excel dashboard we can create using this solution! For example, on the outer ring we can display the planned income for 2016; on the inner ring we can display the actual income of 2015. This sounds really good, doesn’t it?
Another great opportunity is when we plan a KPI dashboard with Excel where we would like to compare one worker’s performance with the average of the group.
We have only mentioned two examples and we could list many more for its use, it is possible we will make several similar dashboards this year with the help of the just now introduced dual gauge chart template.
If you in a mood for hard work and like Excel, you can find the speedometer widget template here!
Why should we use Gauge Charts in Excel?
The gauge / speedometer chart can be applied in many-many fields. It is a serious task to properly determine the key performance indicators and the creation of the connecting KPI dashboard. KPI is a huge subject; soon we’ll introduce an article about it. To tell the truth this subject can be equally loved and hated.
We don’t mind that this subject divides people’s assumptions! It makes this profession beautiful! What would happen if everyone would use the same tools? It would create templates that are spitting images of each other.
We recommend you the following steps. First you should think it through what would You like to use this tool for? Carefully plan the dashboard! It is even worth to draw up a paper based sketch.
If you have the resources and the key performance indicators are all right, then you can take the last step, to choose the appropriate kind of gauge. Do not forget the Excel info graphics either; they can boost the overall picture. Like always, the decision is yours! Thank you for being with us today!
Conclusion – Pros and Cons
And finally let’s see here a little summery that will help you chose the cases you should use this kind of chart and the cases when you should avoid it from afar!
Use gauge chart templates if:
• You like data-visualization and would like to create unique dashboards.
• Don’t get scared of applying a few settings.
• The goal is to use one or two key performance indicator.
Not recommended to use this chart if:
• You have to display more than two values.
• You wish for something simpler.
• Don’t even have enough time to read this article through.
• Not interested in the topic of KPIs.
The decision is of course Yours as always, we can only share you our experiences with you. Thank you for being with us and if you interested in novelties join our Excel Dashboard School group on Facebook where the novelties are shared primary on a weekly basis.
Create live dashboards using Gauge Charts
If the goal is to display only a few key performance indicators then that take only a maximum of 5 minutes, but if you have to show all the indicators of a company with a use of 50-100 gauge widget, the work still wouldn’t take more than 1-2 hours.
Our professional data visualization add-in can dramatically enhance the functionality of Excel.
The Gauge Chart add-in that we have made is a special Excel add-in that elevates KPI dashboard making to a new level especially for top managements and business analysts.
Additional resources: Free Gauge Chart Templates
We’ve divided this Gauge chart tutorial into some useful articles:
- KPI Report Template – Create Gauge Chat using MATH function
- Free Dashboard Widgets – Circle, Quarter and semi circle style gauges
- Product Metrics Dashboard using Gauge Chart