adding target 'banding' to a chart

[Deanomcbeano]

Hello

I am trying to put together a simple bar chart in Excel (2007). I have one row of data with percentages, C3:F3. I have a row with percentage targets in C2:F2, however these targets are not set at one figure, they are often say 0-10%.

Is there a way to represent the target as a banding on the chart? I have attached a copy as an example

Cheers
Dean

[Andy Pope]

Something like this?

[Deanomcbeano]

Hi Andy

Thanks for this, that's definitely along the right lines. I've amended it slightly. However it does look quite clunky. Is there any way to represent the target as a wide banding that sits behind the 2011/12 figures, instead of having the target represented as a bar itself?

Cheers
Dean

[Andy Pope]

What if you set the bar gap to zero.

Or change the chart type to stacked area. You can then format the axis to cross between categories

[Deanomcbeano]

Thanks Andy, definitely getting there, though I can't quite understand how you managed to get it to do that! I need to incorporate several years data - see the new excel sheet. On the chart in the bottom left I can't get the target banding to work out the same as you had it. Can you help? I also can't get the hashing to show up in the legend on the left.

Cheers
Dean

[Andy Pope]

You need to alter the data series reference of Lower series, it is using 2008 data.

Change Lower and Total series to area chart
Display secondary horizontal axis.
Format primary horizontal axis to cross between tickmarks.
Set tick labels to none.

Use the secondary axis tick labels, set to Low.