Speedometer (Gauge) Chart in Excel 2016

0018
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.

    1. Speedometer, will have markers as Poor, Average, and Good. Prepare, this data.
      00019
      The invisible part should be equal to sum of the markers . 30+40+30=100.
    2. Now select the markers and go to Insert ? charts ? Donut and plot a donut chart

0020

    1. Right on the donut and click on format plot area.

00021

    1. 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.

0022

    1. 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.

0023

    1. Now we need the needle that will show, how much target is achieved. Prepare this data.0024
      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.
    2. Now right click on chart, and click select data.

0025

    1. 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.

0026

    1. 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.

0027

    1. Tick mark the checkbox of secondary axis for the pointer, and change chart type of pointer as Pie chart from drop down.

0028

    1. Wait wait. It will look like a speedometer. Just, select the chart. Rotate it 270 degrees as you did before.

0029

    1. Go to fill and, select no fill for each part of pie chart, except needle part. Change needle color using black.
      0030
      We have our speedometer (gauge) chart ready. When you change the data in achieved the needle will move.
    2. 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.

0031

 

  • 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.

 

0032
You’re speedometer is ready. You can now formate it to suit you or your organisation.
00032
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.

Related Article:

Creative Column Chart that Includes Totals

Create Overlay Chart in Excel 2016

Pareto Chart and Analysis

Waterfall Chart

Excel Sparklines: The Tiny Charts in Cell

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube