- 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 Why should we use gauge charts?
- 6 Create live dashboards using Gauge Charts:
- 7 Free Gauge Chart 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!
Why should we use gauge charts?
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 infographics 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.
Free Gauge Chart Templates:
We’ve divided this Gauge chart tutorial into some useful articles:
- Advanced Visualization – Create Dual Gauge Template
- KPI Report Template – Create Gauge Chat using MATH function
- Sales Activity Template – Data Visualization using Gauge Chart
- Free Dashboard Widgets – Circle, Quarter and semi circle style gauges
- Product Metrics Dashboard using Gauge Chart