A Chart for KPI Metrics
Speedometer Chart is very useful Visual to display KPI metrics. We can showcase metric like Service Level.
To create above given Speedometer chart in MS Excel below steps to be followed-
- On Range “A1:A10” put 10%
- On Range “A11” put 100%
- On Range “D1” put your metric name (like- Service Level)
- On Range “E1” put metric performance number (like – 85%)
- On Range “D2” put Needle Width
- On Range “E2” put 1%
- On Range “D3” put Blank Area
- on Range E3 put formula “=200%-SUM(E1:E2)“
- Select Range “A1:A11” and go to Insert>>Chart>>Insert Doughnut Chart
- Remove all the chart elements like Chart Title and Legend.
- Right Click on Doughnut and go to Format Data Series…
- In Format Data Series change the Angle of first slice from 0° to 270°
- Double click on half circle (available in below side).
- Format Data Point window will be opened.
- Click on Fill and line option.
- Fill as No fill in half circle.
- In the other slices fill the Red to Green color as given in below image.
Right Click on Doughnut and click on Select Data …
- Click on Add (available under Legend Entries (Series)) to add new series.
- Select Range “D1” (Service Level) in Series Name box
- Select Range “E1:E3” in Series Values box
- A new doughnut will be created in outside of old doughnut
- Right click on outside doughnut and click on Chang Series Chart Type.
- In Change Chart Type window Change Chart type as Pie for the Service Level Series or Series 2.
- Also check the Secondary Axis option.
- Right click on Pie and click on Format Data Series.
- Change the Angle of first Slice in format Data Series window.
- Click on the Pie and go to Format Tab>>Shape Outline>>Select No Outline
- Double click on blue slice of Pie and fill as No Fill.
- Double click on Gray slice of Pie and fill as No Fill.
- Double click on Needle and fill as black color.
- Go to Insert Tab>>Shapes>> Select Oval under Basic shapes
- Drag the a small oval on center of needle.
- Select the oval and go to Format Tab>>Shape Fill>>Fill black color in the Oval
Select the Oval and go to Format Tab >>Shape Effects>>Preset>>Select Preset 2
- Double click on needle speedometer, needle will be selected
- Right click on needle and click on Add Data Label.
- 1% will be available as data label, click on 1%
- Go to formula bar and press “=” and click on range “E1”
- Data label will be connected with cell “E1” which is metric performance.
Our Speedometer Chart is ready.
