Multiple Width Overlapping Column Chart

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.

Dual Column Width Chart Using Primary and Secondary Axis

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.

Mono Column Width Chart Using Only Primary Axis

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.

Rectangles for Dual Column Width Chart

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.

Dual Column Width Chart Using Only Primary Axis

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:

Triple Column Width Chart Using Only Primary Axis

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:
Pairs of rectangles for bar chart fills

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.

Using Error Bars for Multiple Width Chart Series Bars

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.

Using Error Bars for Multiple Width Chart Series Bars

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.

Using Error Bars for Multiple Width Chart Series 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.

Using Error Bars for Multiple Width Chart Series Bars

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.

 

Peltier Tech Charts for Excel

Comments

  1. Cool trick!!

  2. This post is very useful, but after seeing “You can almost always make Excel do what you want.” I had to ask…

    I saw this graphic
    http://www.economist.com/news/united-states/21591190-united-states-amoeba
    before reading your post and idly wondered if one could make it in Excel. With your comment I felt I had to bring it to your attention. I would assume Excel could handle it, though don’t know if it would be worth the effort on a large dataset.

  3. GMF –
    About that Amoeba graphic…
    Sure, you could use Excel for this, but the data handling must be overwhelming. Each of 100 points can connect with any of the remaining 99, and the position of each point is based on which other points it is connected to. Excedrin headache.

  4. Another approach would be to use Custom Error Bars, so limited to xl2007 onwards.

  5. Well, there is a simpler trick that can do the job if you need to have two overlapping columns and that is stroke and fill. I use full color 2 point stroke with no fill for forecast and no stroke 40-60 % color fill with about 50% transparency settings. So if your actual data is lower than FC it seems that the bar is only partially filled. It actual is higher, thanks to transparency you can still see what the FC was…

  6. Jean-Paul Roche says:

    Jon, Excellent as usual.
    For the column bar chart with 2 bars : no problem.
    I try to build the column chart with 3 bars, I could not get the expected result by drawing three large rectangles.
    Can you complete the process of constructing the chart ?
    Thanks

  7. Goodness, Jon, that’s genius.

    You also finally solved a problem I’ve had with displaying two points of data. I was working with error bars to show the “goal”, but I think this version will look a lot better for what I want.

  8. Jean-Paul –
    You need 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:
    Pairs of rectangles for bar chart fills

  9. Lockdalf –
    By “stroke”, I assume you mean the border of the bars. I’ve actually written about this technique in the past, somewhere on this blog. The result is something like this, no?
    Alternative
    I typically plot the target series second, so it appears in front of the actual series, and I don’t need to use transparency.

  10. Jon,

    precisely like that… who knows, I might have gotten it from you some time ago after all… ;o]]] I just use thicker border to make it more prominent… and good idea with changing the plotting order would make it look a bit less ‘dull’. Will have to implement it…;o]]]

  11. Andy –
    Thanks for the suggestion. I’ve added it as a second approach.

  12. Jon,
    Done perfectly
    Thanks a lot

  13. Michael Slattery says:

    Thanks Jon:

    I am attempting to do something similar, but sufficiently differently that you approach does not address my issue. I simply want to graph, 3 bar charts so they appear to be directly in front of each other. I change the transparency of the top two in order to always be able to view all of the data. Each bar chart is always on the same axis with a scale of 0 to 10. I don’t want a stacked chart, i.e., all three data points in one bar.

    My current solution is to make three separate charts and make both backgrounds transparent.

    Can you point me to a solution for this?

    Thanks Michael

  14. Michael –
    99% or more of the time, when someone is stacking up charts and making the top ones transparent, all the data can be shown in one chart.
    In your case, you have three clustered column series, three area series, and two line series. They can all be plotted on the same chart. Start with a clustered column chart with all of them, then one-by-one select a series and change its type.
    The three clustered column series have the primary color fills and 100% overlap.
    The three area series have thick primary color borders and primary color fills with 50% or so transparency.
    The two dashed lines actually require two lines, to get the two-colored dash effect. Plot each twice, color the first one (underneath) solid white and the second one (on top) with the primary color.
    Of course, it may be possible to redo the chart to make it more visually appealing and more cognitively effective. Light or white background, less saturated colors, thinner lines. Are the series all directly comparable, or can they be partitioned into separate panels of a new chart, or even into separate charts?

  15. Hi Jon,
    Thank you for sharing these tricks!
    I have only one comment: I think there is no need to draw pretty tall rectangles, because Excel stretches it. The drawing is a rectangle, so there will not be any visual quality problem in case of vertical stretching.
    Cheers,
    Kris

  16. Kris –
    Back in Excel 2000 what you say is true. In Excel 2002 or 2003 Microsoft started on a big antialiasing kick, where the edges of any feature in an image would be smeared out when it was stretched or shrunk. If you tried to fill a chart bar with a copied rectangle, the edges would not be sharp, and there would seem to be a border even if the rectangle had no border or a border with the same color as the fill. The only way around this was to use very large rectangles, so the pseudo border would be at most one pixel wide.
    I haven’t checked 2013 carefully. The edge problem in stretched images isn’t as bad as in 2003, but to be sure my chart appears as nice as possible, I’ll keep using large images and shrink them.

  17. Thank you Jon for the answer.
    I see now, you are right, in 2007 the top and bottom of the bar has some border, while in 2010 and 2013 it seems correct, so not border. But it is true better to use a method which works in all versions.

  18. I am a big fan of using the x-y plot with error bars methodology. Thank you for including it in your solution. The x-y plot with error bars solution also works well for adding “programmed” rectangles (think quadrants) of any dimension anywhere on the plot. The error bars approach could be an alternative solution to the great article you wrote a while back – Shaded Quadrant Background for Excel XY Scatter Chart. Thanks!

  19. Thanks a lot for this post. Is it possible to go one step further and show 3 sets of 3 overlapping bars for each month ?
    That is, the above example would then have a total of 9 sets of 3 overlapping bars, 3 sets for each month

  20. Rohit –
    If I knew exactly what you wanted I could answer more intelligently. Do you want three clusters of three overlapping bars for each month? If so, her you go:

    Starting with this typical data, create a clustered column chart.

    Arrange the additional data as shown below. The values in the first column are for the X values of these points, and will line up with a column chart that has 0% overlap and 100% gap width.
    Copy and paste this additional data into the chart as new series (bottom left).
    Convert these added series to XY type (bottom right).

    Add error bars to all of the XY series (below left).
    Delete the horizontal error bars (below right).
    Change the vertical error bars to show only minus bars with a percentage of 100%, and with no end caps (bottom left).
    Choose appropriate colors and line widths for the error bars (in this chart, for example, I used 8pt and 4pt) (bottom right).

    Format the XY series to show no markers (they already showed no lines). Then adjust the colors if necessary to find a combination that doesn’t obscure itself.

    If you can arrange the series in order of height, it may help with readability. Or not.

  21. That is exactly what I was looking for Jon. Thanks! I later realised that I could do with two over lapping bars and set the color fill for the third bar to “None”

    I am amazed to see how you and other folks on the internet manage to find workarounds to get the data visualization needed in Excel. For example, in the overlapping bar charts, the concept of pasting data from the second table directly onto the chart. I had never thought of that.

  22. Rohit –
    Paste Special isn’t a workaround, but I guess people aren’t so familiar with it. Much easier than adding data series by series in that awful dialog.

  23. Jon,

    Thanks for these tips, they’re really awesome. I wanted to write a note cause im having trouble. I’m using the error bar method, and am having trouble putting it in a presentation everytime i copy the graph into powerpoint it screws up the overlapping bars. it doesn’t matter if i use paste special or not. do you know what’s going on here?

    Here’s the link to the pictures:
    https://drive.google.com/file/d/0Bz8i1aVNCT1HSTFMNElycGt0dTg/edit?usp=sharing

    I put the screenshots in a word file so you can see the appearance in excel and then in powerpoint

  24. Richard –

    Looks like PowerPoint is tampering with the stacking order of the error bars. If you change the series order (before or after pasting into PowerPoint), does that have any effect.

    I suspect if you copy the chart as a bitmap, it will be pasted with everything in the right order. You need to make the chart the same size in Excel as it has to be in PowerPoint.

  25. My data is –
    Budget Actual Joiners
    Jan 2000 1000 10
    Feb 3000 3300 20
    Mar 2500 1500 30

    I created overlapped graph for Budget and Actual column by moving Budget column to secondary axis. Now since values of Joiners column are so small, I am not able to create line graph using secondary axis. Please help.

  26. Ambarish –

    Neither Option 1 and Option 2 in my tutorial require the use of the secondary axis, so it should be available for the Joiners data.

  27. The error line method worked perfectly! Thanks!!

Trackbacks

  1. […] 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. […]

  2. […] Peltier gives step by step instructions for building a multiple width overlapping column chart. You can see the final result in the screen shot […]

  3. […] and thanks to Jon Peltier for writing how to do this in […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0