I read a post entitled Calling All Graph Wizards – Overlapping/Stacking Graphs w/o secondary axis on the Mr Excel forum today, and decided the question was broad enough and the answer quick and elegant enough that it was worth sharing.
The problem was that the user wanted to show projected and actual values of one variable as columns on the primary axis and of another variable as lines on the secondary axis.
If you only have the one variable, you can plot projected on the primary axis and actual on the secondary axis, then use a smaller gap width (wider bars) on the primary axis and a larger gap width (thinner bars) on the secondary.
But you can’t use two gap width settings if the columns must be plotted on the same axis. When you plot them, the taller bars in the front obscure the shorter bars in the back, so you can’t compare the values.
But you can still make Excel do what you want. (In fact, you can almost always make Excel do what you want, if you know how.) I’ll describe two ways to accomplish this.
Option 1: Fill Bars with Rectangular Shapes
This approach was the original topic of this tutorial.
Draw two rectangles, pretty tall. The taller the better for visual quality of the chart. Make the thinner rectangle the color you want, and make the thicker one transparent. Make the widths of the two rectangles in the same proportion as the widths you want for the bars in the chart. Center the two rectangles horizontally.
Select both rectangles, and copy (Ctrl+C). Select the series in the chart, and paste (Ctrl+V). The chart now uses the copied shapes as the fill for the selected series.
Pretty easy, once you know how.
This comes in handy too if you need more than two widths. Without this trick, you couldn’t make the following chart even if you could use the primary and secondary axes:
This requires two pairs of rectangles, a clear one and a relatively wide filled one for the second series, and a clear one and a relatively narrow filled one for the third. These are shown below:
The advantage of this approach over the next is that the narrower bars keep their relative width, which is fixed by the ratio of filled rectangle to transparent rectangle used to fill the bars. The disadvantage is that if you want to adjust the width of the narrower bars, you need to adjust the width of the rectangles, then copy and paste onto the chart series.
Option 2: Error Bars with Multiple Widths
My colleague Andy Pope has pointed out in the comments that another approach for this effect is to use error bars for the narrower bars. In Excel 2003 and earlier, you had few options for line width, but since Excel 2007, you can make lines of seemingly any arbitrary thickness.
I’ll show Andy’s technique for three sets of bars. It’s even easier for two sets of bars.
I’ll start with the original column chart, setting overlap temporarily to zero so the different sets of error bars don’t obscure each other. The first thing to do is hide the bars you want to display narrower, that is, use no fill color for them. I’ve kept a colored outline to show what’s going on.
Add error bars to the series you want to show as narrower bars.
Customize the error bars using the Minus Only, No Caps, and 100% Percentage value options.
Now comes the magic. Apply the desired line colors to the error bars, and make the error bar lines thicker. Here I’ve used 11.25 pt for the orange bars and 5.75 pt for the blue bars.
Then I’ve hidden the outlines of the original bars.
Change the overlap back to 100 so the bars are all centered on the categories (the month labels).
This has made the unchanged bars for Plan much wider, so we need to adjust the line widths of our error bars. I’ve settled on 30 pt for the orange and 12 pt for the blue.
I used a little trick to reapply the bar colors to the legend. When I made the bars transparent, I started with the entire series formatted with the desired color. Then I selected one bar at a time instead of the entire series, and used no fill for the bar. Working point-by-point in this way leads Excel to believe that the series as a whole has not been changed, so it leaves the legend entries alone.
The disadvantage of the error bar approach is that any reformatting that changes the widths of the original bars (changing overlap, adding points to each series, stretching the chart) will distort the relative widths of the error bars and the original bars.
The advantage is that these widths can be adjusted very easily by formatting the error bar lines, without having to fiddle with the widths of the rectangles which must then be copied and pasted onto the series bars.