Speedometer (Gauge) Chart in Excel 2016
A beautiful, and easy to understand chart always make your excel dashboard stand out. Excel Speedometer or Gauge graph is one of the most popular chart types that attract managers. It’s easy to understand. It visualises achievement or growth rate creatively. I read many articles about how to create Speedometer graph in excel, but none of them were explaining it easily. This article focuses on an easy explanation about creating a speedometer graph in excel. So, sit tight, and let’s get started.
Prepare Data For Speedometer Chart
Here, I have to show everyday target achieved in speedometer chart.
- Speedometer, will have markers as Poor, Average, and Good. Prepare, this data.
The invisible part should be equal to sum of the markers . 30+40+30=100.
- Now select the markers and go to Insert ? charts ? Donut and plot a donut chart
- Right on the donut and click on format plot area.
- Go to series option, and make “angel of first slice” as 270 degrees. This will rotate your chart. And will start looking like a speedometer.
- Click on the invisible part (100%). Go to fill and line. Click on no fill. Now it looks like a speedometer. If you want, change the colour of markers. Colour poor as red, average, as yellow, and good as green.
- Now we need the needle that will show, how much target is achieved. Prepare this data.
The 5% is achievement point that will be shown, it will of course change. 1% is pointer. It is for thickness of needle in speedometer. 150% is garbage number, that is used to make pointer. You can adjust it according to your will.
- Now right click on chart, and click select data.
- In Legend Entries (Series): click on Add. In series name: click on pointer. It is optional, you may leave it. In series Value, give reference of the data we prepared.
- Now, you have a chart that looks like this. We don’t want this. We want speedometer chart. Right Click on chart, and click on “Change Series Chart Type.
- Tick mark the checkbox of secondary axis for the pointer, and change chart type of pointer as Pie chart from drop down.
- Wait wait. It will look like a speedometer. Just, select the chart. Rotate it 270 degrees as you did before.
- Go to fill and, select no fill for each part of pie chart, except needle part. Change needle color using black.
We have our speedometer (gauge) chart ready. When you change the data in achieved the needle will move.
- Now we need to do a little bit of formatting. This needle looks to thin, make 1.5%.
Add a text box or shape. Click text box, go to formula bar and click on. Write = and give reference of achievement.
- Now, whenever the achievement changes needle will move. You should link achievement to actual data.
- Let’s say we have some target and achievement data ready. Click in Achieved cell, write this formula.
You’re speedometer is ready. You can now formate it to suit you or your organisation.
So, you have gauge chart now to rock your excel dashboard and powerpoint reports. Hope it was helpful. If want to learn more creative chart in excel, keep in touch. If you have any other chart ideas, do share in the comment box below.
Creative Column Chart that Includes Totals
Create Overlay Chart in Excel 2016
Pareto Chart and Analysis
Excel Sparklines: The Tiny Charts in Cell
The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel