Excel - add target line to stacked bar chart

Question

I've got a stacked bar chart. I'm displaying a set of floating bars to represent hi/low ranges for some metrics, by using a transparent fill on the bottom section of the bar I achieve the desired look.

What I now need to do is add a horizontal line across the chart to indicate how a particular users score relates to all of these hi/low ranges therefore the placement of this line needs to be dynamic based on a value in a cell.

Is there anyway to do this as I can't find an easy option. If this was a simple bar chart I could add the target scores as new series and use the line chart type but I don't seem able to overlay a second series on the stacked bar chart.

I'm using 2003 at the moment but run this in 2007 if that helps.

Solution

Similar to what petersohn said, can you use an XY chart with two data sets - the first being the your current data that you're currently representing with the stacked bar chart, and the second being your horizontal line?

You could recreate your current graph by using error bars in an XY chart instead of the stacked bar chart. Just turn off the line that connect the data points.

The horizontal line is easy - make a second data set with two points, the y value being your calculated value for the horizontal line, and the x value being the max and mins of your graph.

Comments

[petersohn]

I remember a similar question from some time ago. As for the answer, you can construct practically anything using XY charts, however it may sometimes be a bit complicated.

[Martijn]

That looks like it'll do exactly what I need - thanks.