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

SDJ98

New Member
Joined
Apr 21, 2011
Messages
27
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)

2ni5tzt.png


52060g.png
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.

ThinnerColumns.png
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.

vieg6d.png
 
Upvote 0

Forum statistics

Threads
1,224,890
Messages
6,181,612
Members
453,057
Latest member
LE102024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top