Speedometer Chart in Excel

Today I will tell you how create a Speedometer chart in excel. we can use speedometer chart to show a KPI metric. An Image has been given below for a speedometer.

Step by step tutorial for a speedometer:

1.       Put “A1” to “A10” 10%

2.       Put 100% on “A11”

3.       Actual Value on “D2”

4.       Indicator width on D3 as 1 %

5.       Rest value at D4. Put formula “=2-Sum(D2:D3)”

6.       Go to Insert > Chart >

7.       Insert a Doughnut chart

8.       Right click on doughnut and click on Format Data Series

9.       Under Series Option change "Angle of first Slice" from 0 to 270 degree.

10.   fill the half part of doughnut as no fill.

11.   Fill color in the rest slices as give below image.

12.   Right click on the graph and click on “Select Data”

13.   Under “Legend Entries (Series)” click on Add.

14.   Select Series Name “C2”

15.   Series value range “D2:D4” and click OK

16.   Right click on doughnut and click on “Change Series Chart Type”.

17.   Select Actual value “Pie” as given below image.

18.   No fill in Pie chart except Indicator width part (“1%”)

19.   Fill black color in Indicator width part (“1%”) and remove outline in pie chart.

20.   Right click on Pie chart and click on “Format Data Series”

21.   In “format Data Series” under Plot Series on click on “Secondary Axis”

22.   In “Angle of first Slice“ put value 270 degree.

23.   Now go to Insert > Shapes > Ovel

24.   Drag Ovel shape on the Image.

25.   Select the Ovel shape and go to Format > Shape Effects > Preset > Preset 3

26.   Inset a text box and connect with Actual value “D2”

Speedometer chart is ready. If value of D2 will be changed, indicator of speedometer chart will be changed accordingly.

Please download the excel file from below given link

https://drive.google.com/open?id=1AeUioNO7F5iKBj1uodEKgkX-Dj9m3hIs

Watch this video to learn step by step tutorial of Speedometer-

Conditional formatting in a Chart

Conditional formatting in a chart gives an analytic view.Anybody can easy find the low performance (Red columns) and high performance (green column) on the graph. As in below as soon as Target SL% (on range F1) is being changed, columns color (red and green) is getting changed according to target.

  

Steps to use conditional formatting in a chart

1-Take two support column on C (for Red) and D (for Green)

2-Put =IF(B2<$F$1,B2,NA()) formula on column C

3-Put =IF(B2>=$F$1,B2,NA()) formula on column D

4-Fill down the formula till the end.

5-Select range A1:A11

6-Press Ctrl and select range C1:D11

7-Insert a Stacked column chart under 2D column chart

8- Change the Color for columns Red color for blue columns.

9-Green color for magenta columns (in above picture 8 May and 10 May)

10- Your chart is ready with conditional formatting.

Please download this excel file from below given link:

https://drive.google.com/open?id=1lptyeE6fFN5Xw0w483a5DNaEkVA8rQUq

Tutorial video for Conditional formatting in a Chart

Infographics: Beer mug chart in Excel

Beer mug chart can be used to showcase KPI metrics between 0 to 120% (20% bonus). Below are the images of beer mug charts for 20%, 50%, 100% and 120%.

As showing in below images, beer position will be changed whenever metric value will be changed. whenever metric value will be more then 100% (up to 120%) a beer foam will be displayed on beer mug.

Watch below video to learn step by step tutorial of beer mug chart:

Infographics: Battery Chart in Excel

Battery Chart in Excel:

We can use battery chart to showcase KPI metrics (0% to 100%) in excel. 

It is a great visual way of viewing the percentage remaining of a value.

In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or Service Level etc.

There are many reasons why you may want to visualize an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.

Please watch this video to learn step by step tutorial of a battery chart in excel.