Calling All Graph Wizards - Overlapping/Stacking Graphs w/o secondary axis

[SDJ98]

I have a graph that works relatively well, but not quite. There are two columns/bars plotted on top of each other, with the blue one having wider borders. The purpose is so that you can see if actual results are beating or falling short of plan. You can't do a 'real' stacked bar, since the results could be higher or lower than plan, and you'd only see one of the scenarios. My issue is that adding thicker borders also makes the blue border thicker on top as well, causing it to extend higher than the data point would suggest and throwing off the comparison to actuals. I have a second set of data plotted on the secondary axis that must remain, which I think is limiting my options. Any thoughts on how to make this all work.


Graph and Data below (the columns are primary axis, line graph is secondary)

 

[murphm03]

Why don't you make the inside bars thinner

 

[Jon Peltier]

Make the inside bars thinner. But he can't increase the gap width of just one series on the primary axis.

But you can fake out Excel, as I demonstrate below.

When I simply plot the data as in the upper chart, the taller orange bars hide the shorter gray bars, so I don't know how actuals compare to projected.

To the right of the data and charts, I've drawn to tall rectangles (taller is better for the quality of the rendering in the chart). One is the color of the bar, the other is transparent, and they are horizontally centered.

Making sure I've selected both, I copy them. Then I select the orange series in the chart, and paste (Ctrl+V). The copied shapes are now used to fill the selected series, as shown in the lower chart.

 

[SDJ98]

Excellent...thank you so much!

This place never fails to deliver.

 

[Jon Peltier]

I wrote a tutorial on my blog to share this technique with the masses. The article shows the approach I described above, and a second approach that uses error bars of various thickness, suggested by Andy Pope. The article is at Multiple Width Overlapping Column Chart.

 

[SDJ98]

Jon,

Your first suggestion was exactly what I wanted and ended up using. That said, a coworker pointed out another relatively easy solution that might be acceptable in some cases: changing the series overlap. I didn't think of it since the gap width functionality wasn't working. Playing with the numbers I bit (40% overlap), I produced the following.