Abdul Alim: a little bit learning, let's go

Excel, VBA and Power BI tutorials 

Battery Chart

(A Chart for KPI Metrics)

Battery chart is used to show the KPI metrics. For example if we have to create a chart for Process Service Level then we can use battery chart.

Below are the steps to create a battery chart in excel:

• Put your metrics (for example Service level) on range A1 and its value on range B1 (like 80%)

• On Range A2 Upper cap and on range B2 5% (Upper cap size of battery)

• On Range A3 Lower cap and on range B3 5% (Lower cap size of battery)

• On Range A4 Blank Area and on Range B4 put a formula “=100%-B1”

• Select the range A1:B4 and go to Insert>>Charts>>Insert Column Chart>>3D Column>>3D Stacked column

• Select the chart and go to Chart Toos>>Design Tab>>Click on Switch Row/Column

• Click on the Chart Element button (Plus button of chart) and unchecked Axes, Chart Title and Gridlines.

• Right click on 3D column and click on Format Data Series.

• In Format Data Series option select Cylinder option which is available under Column Shape.

• Right click on cylinder (in chart) and click on 3-D Rotation…

• In 3D Rotation option change the X Rotation 0º in place of 20º

• In 3D Rotation option change the Y Rotation 10º in place of 15º

• Right click on cylinder and click on Select Data…

• In select data option use Move Down and Move Up option and arrange the series in below order-

1. Upper Cap

2. Service Level

3. Blank Area

4. Lower Cap

• Right click on Upper Cap or Lower Cap Area and click on Format Data Points…

• In Format Data Point click on Fill and Line Option.

• In Fill option select the Solid fill  and change the color as Gray.

• Similarly fill the color in other Lower Cap (Gray color), Service Level (Green Color) and Blank Area (Green Color).

• Select the Blank Area series and change the Transparency as 75%

• Right click on Battery and click on Format Data Series…

• In Format Data Series option go to Series Option and change Gap Width 300% from 150%.

• Select the chart and go to Insert>>Shapes>>Flowchart>>insert Flowchart: Magnetic Disk

• Change the size of shape according to Batter size.

• Select the shape and go to Format Tab>>Shape Fill >>Fill black color

• Select the shape and go to Format Tab>>Shape Outline>>Select No Outline

• Right click on battery chart (in green area) and click on Add Data Labels.

• After adding the data label change the font color of data label as White.

• Change font size of data label as 11

Our battery chart is ready. Please download the excel file for practice.