(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-
- Upper Cap
- Service Level
- Blank Area
- 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.