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.

 

Battery Chart
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”
Data for Battery Chart
Data for Battery Chart
  • Select the range A1:B4 and go to Insert>>Charts>>Insert Column Chart>>3D Column>>3D Stacked column
Insert 3D Stacked Column Chart
Insert 3D Stacked Column Chart
  • Select the chart and go to Chart Toos>>Design Tab>>Click on Switch Row/Column
Switch Row/Column
Switch Row/Column
  • Click on the Chart Element button (Plus button of chart) and unchecked Axes, Chart Title and Gridlines.
Chart Elements
Chart Elements
  • Right click on 3D column and click on Format Data Series.
Format Data Series
Format Data Series
  • In Format Data Series option select Cylinder option which is available under Column Shape.
Cylinder Option
Cylinder Option
  • Right click on cylinder (in chart) and click on 3-D Rotation…
3-D Rotation
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º
3-D Rotation option
3-D Rotation option
  • Right click on cylinder and click on Select Data…
Select Data option
Select Data option
  • 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
Select Data Source
Select Data Source
  • Right click on Upper Cap or Lower Cap Area and click on Format Data Points…
Format Data Points
Format Data Points
  • In Format Data Point click on Fill and Line Option.
Fill and Line
Fill and Line
  • 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).
Color Fill option
Color Fill option
  • Select the Blank Area series and change the Transparency as 75%
Transparency option
Transparency option
  • Right click on Battery and click on Format Data Series…
Format Data Series
Format Data Series
  • In Format Data Series option go to Series Option and change Gap Width 300% from 150%.
Gap Width option
Gap Width option
  • Select the chart and go to Insert>>Shapes>>Flowchart>>insert Flowchart: Magnetic Disk
Insert Shape
Insert Shape
  • Change the size of shape according to Batter size.
Resize Shape
Resize Shape
  • Select the shape and go to Format Tab>>Shape Fill >>Fill black color
Shape fill option
Shape fill option
  • Select the shape and go to Format Tab>>Shape Outline>>Select No Outline
Shape Outline
Shape Outline
  • Right click on battery chart (in green area) and click on Add Data Labels.
Add Data Labels
Add Data Labels
  • After adding the data label change the font color of data label as White.
  • Change font size of data leble as 11

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

Batter Chart
Click on the chart to download the excel file

Video Tutorial for Battery Chart: