How to make speedometer chart in Excel

In today’s growing need of using powerful yet eye catching charts in analytics, speedometer reserves its place to be one of the top chart used widely to showing KPI performance, especially sales performance. It presents a spectacular visualisation to show sales as well as other KPI’s performance lying within the range of targets and actual values. It is widely used as a smart presentation tool rather than a simple analytics instrument.

Speedometer doesn’t come as a pre-built chart component in office chart tray, but it can be created very easily by using two or more different charts combination.

Steps to create a simple speedometer in excel

Step 1:- To create a speedometer we need to first set up data in excel.

Below screen shot shows the value range for setting up a semi-circular gauge having curve at top with three colour categories.

guage

Step 2:- Now choose other blank range in excel and setup below shown range for gauge chart’s needle. Type the formula for End = 360 – (Value + Pointer).

indicator

Step 3:- Now select Gauge value range from step 1 and insert Doughnut chart as shown below.

step3

Step 4:- Right click on the chart and choose format chart area and select series option to adjust angle of first slice to 270 degree as shown below. You may adjust other chart variables to suite your need.

 step4

Step 5:- Now right click on Doughnut slices to fill colours.

step5Step 6:- Select series in indicator table to insert a pie chart.

 step6

Step 7:- Do the colour formatting of pie slices to make slice having pointer’s value look red and other slices to carry no fill. Also, make set chart background to no fill. Follow step 4 to adjust needle’s position to look at 0 degree for value=0.

step7

Step 8:- Place pie chart on Doughnut chart as shown below and add data label for needle only. Now, select data label to edit it in formula bar and write formula to set its value from value.

step8

Step 9:- We can now alter value in component table to animate needle over the gauge.

You can format the chart as you like. There are number of possibilities to create elite visualisations.

Please share your comments below…………



mm
Author: Manish Singh
Manish has over 3 years of experience in Analytics and has delivered several BI solutions for renowned clients. He is a part of Data Analytics team having extensive knowledge in various analytics tool like excel, tableau etc.

Leave a Reply