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.

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

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

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.

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

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

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.

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.

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…………

The post How to make speedometer chart in Excel appeared first on Actiknow : BI consulting | Data Analytics Consulting | Automation | Mobility.