Excel provides some nice Charting features. One really nice feature is the ability to combine chart types.
Here’s how to graph KPI data together with both a line and column chart.
You will need the Charting toolbar for some of it. Right-click the toolbar and select Chart.
I’ll start off with some monthly data for the last two years.
E14 and F15 are =AVERAGE(B2:B13) and =AVERAGE(C2:C13) respectively.
|Highlight A1:F15 and use the Chart Wizard to create a simple line chart without markers.
Because Avg 03 and Avg 04 have only one value, they cant be seen in a line chart.
From the Charting toolbar, use the dropdown box to select Series “Avg 03?. Then from the Chart menu, select Chart Type. Set it to a Column chart.
The same needs to be done for Avg 04.
I’ve set 2003 to Red, 2004 to Dark Teal, and KPI to Black. All three with a weight of 2.
|Doubleclick the Avg 03 bar to set formatting options.
Pattern:Red, Data label:Value=Ticked, Options: Overlap=100, Gap=5
For Avg 04… Pattern:Dark Teal, Data label:Value=Ticked
Doubleclick the number appearing over each bar to set formatting options.
|I then did some general tidy up with the Plot Area.
I made it bigger and set the Background to Light Green, Gridlines White