Excel Gauge Chart (Table of Contents)
Gauge Chart in Excel
Charts are more understandable than the numbers, it is more appealing to the reader to understand the data very quickly. Like many other charts in excel, a gauge chart is one of those which can tell the story graphically. Just like our bike or car gauge shows at what speed we are heading towards the target.
Excel Gauge chart often called a speedometer chart just like our bike or car speedometer, which helps us to understand the performance of a project or sales cycle. Gauge chart shows the visual from 0 to 100% efficiency level in Excel. The problem with Gauge chart in excel is excel does not have any in-built Gauge or Speedometer chart by default, we need to make some arrangements to create our own Gauge chart in excel.
How to Create a Gauge Chart in Excel?
Gauge Chart in Excel is very simple and easy to use. Let’s understand the creation of a Gauge Chart in Excel with some examples.
Example #1 – Create a Simple Gauge Chart in Excel
A pie chart can resemble a simple gauge chart in excel. It does not involve any kind of levels like the speedometer has Poor, Average, Good, Excellent parameters. It shows only two items what is the current achievement and what needs to be achieved.
For this example, I have set up data. This data includes sale starting date, sale ending date.
By using NETWORKDAYS function I have shown the total days available in the sales cycle. NETWORKDAYS excludes weekends and gives the number of days.
In the next cell, I have entered the current date.
Again using the NETWORKDAYS I have shown how many days are over from the sales start date.
And how many days left in the sales cycle.
Now we will create a simple gauge chart in excel which shows the total number of day’s available, the total number of days over and the total number of days remaining.
Step 1: Select B4, B6, and B7 cells by holding the Control key.
Step 2: Insert a Pie Chart for the selected cells.
Step 3: Now we have a chart which looks like this.
Step 4: Select the chart and press Ctrl + 1 which is the shortcut key for the format chart. On the right-hand side, you will get format options.
Make the Angle of the first slice to 90 degrees.
Step 5: Now select the blue colored area i.e. the highest value and make the fill as NO FILL.
Step 6: Now select the entire chart. Under Format tab make the Shape Outline as No Outline.
Step 7: Now select only the GREEN Colored part make and the FILL as NO FILL, shape outline as BLACK LINE.
Step 8: Now select only the colored area and make the shape outline as a black line. Go to FILL > Pattern Fill > Select 90% pattern fill.
Step 9: Make the chart title as No. of Days Over.
Now we have set up a simple Gauge chart. But it does not have any kind of indicators such as Poor, Average, Good, and Excellent parameters we will see this in the next example.
Example #2 – Create a Speedometer Chart with Indicators
For this example, I am taking sales individual employee’s performance in the last year. In order to judge them, I have created some slabs for their efficiency level.
On the other side, I have individual sales employee’s performance for the entire year.
Now create one more data series as shown in the below image.
Create a drop-down list of employee names.
Apply VLOOKUP to get the target, actual, and efficiency level based on the selection from the drop-down.
Now create one more table for Speedometer needle.
For Efficiency Level give a link to VLOOKUP Efficiency cell i.e. N2.
For Needle Size mention 1%.
For Remaining cell mention the formula as =200%-I2-I3.
Now we have set up our data which is required to create a Gauge chart in excel.
Step 1: Select the first table and insert the donut chart.
Step 2: Your chart should look like this now.
Step 3: Select the chart and make the Angle of the first slice as 270 degrees.
Step 4: Now select the big portion of the donut chart and make the fill as NO FILL.
Step 5: For the remaining 5 parts fill different colors as shown in the below image.
Step 6: Right click on the chart and click on Select Data.
Step 7: Here select the second table we have created.
Step 8: Now our chart looks like this.
Step 9: Again select the big portion of the newly inserted chart and fill as NO FILL.
Step 10: Right click on the chart select Select Data. Here select the final table we have created.
Step 11: Our chart looks like this now.
Step 12: Now select the newly inserted chart and click on Change Series Chart Type.
Step 13: Select the PIE chart and then click on OK.
Step 14: Make the Angle as 270 degrees.
Step 15: In PIE chart fill the two big portion as NO FILL and change the needle size to 5%.
Step 16: Right click on the first donut chart and select format data labels in that select only the category name.
Step 17: Select the second donut chart and add data labels but this select Value.
Step 18: Make some color adjustments for data labels to look better.
So our Speedometer or gauge chart is ready to use.
As per the change of the employee name, you make from the drop-down list and your gauge chart will show the results.
Things to Remember About Gauge Chart in Excel
- Create a drop-down list to make the chart dynamic.
- Even though it looks but a great chart will take a tremendous amount of time to create.
- It is often used in KPI presentation.
- Try different colors to make the chart look great and appealing.
Recommended Articles
This has been a guide to Gauge Chart in Excel. Here we discuss how to create a Gauge Chart in Excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –
