Stacked Bar chart with individual Target line for each bar

[Vivek D]

I was able to get a stacked column chart with a target line but cannot get a stacked bar chart with a target line.



If I convert the Stacked column charts to stacked bars, below chart is what I end up getting and the option to change axis for the target line is disabled.



Is there a way around this?

 

[chirayu]

http://exceluser.com/excel_dashboards/bullet-graph.htm

 

[Luke M]

Yep, need to make the bar chart first, and then plot target series as an XY series.

 

[Vivek D]

http://exceluser.com/excel_dashboards/bullet-graph.htm

I had tried this one but thought there might be an easier option considering I don't really need a full fledged bullet chart like in this example.

 

[Vivek D]

Yep, need to make the bar chart first, and then plot target series as an XY series.

Thanks. Neat trick for the target line.

One question though, can I increase/decrease the width of the target line in case I decide to increase/decrease the width of the bars in the chart.

I didn't quite understand how you go this part too
"We realize that each bar is staggered at 0.5, 1.5, 2.5, etc"..
How do you know the 0.5, 1.5, 2.5 thing?

 

[Luke M]

In my chart, the target line is actually a Y-error bar. I adjusted it's length by changing the fixed value. You could do it this way, or turn on the marker, and draw a shape/image, and copy that image onto the marker. Either way works.


For the spacing, it's just a matter of thinking how XL spaces things. If you look at the tick marks on y-axis here:

you can think of them as being at the 1, 2, and 3 level marks. THus, the labels are halfway between, so they are are the 0.5, 1.5, and 2.5 mark. We use that to align the XY series vertically.

 

[Vivek D]

Got it. Thanks.