In this gauge chart tutorial, you will get to know everything about the speedometers. Maybe some of our notes will be a little bit dividing and provocative. With 15 years of Excel experience behind us, we will not be making a slip in speaking.
First of all we’d like to give you a comprehensive picture about the speedometer kind of dashboards!
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!
Explanation for Donut Chart Series: You can change the Red, yellow and green zone values freely.
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. 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.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. We create the color zones of the gauge with a help of a doughnut chart.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 fourth data table controls the ticker of the speedometer. 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%).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. 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. Point 1 = No Fill, point 2 = black and point 3 = No Fill. Point 1 = Red, point 2 = Yellow, point 3 = Green, point 4 = No fill. Explanation for Pie Chart Series: How to change the actual value between 0 and 100? 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.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. 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.You have to know that nothing is impossible with the use of the gauge 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.