VizWiz

Launch, grow, and unlock your career in data

August 26, 2011

Excel: How to create a dual axis chart with overlapping bars and a line

, , , , 17 comments
I came across a challenge last week while working on a project.  I needed to create a dual-axis chart, with two bars on the primary axis and a line on the secondary axis.  Tableau makes this task incredibly easy, but I needed to do this in Excel.  Well, I really didn’t HAVE to create the chart in Excel, but others needed to be able to update the chart and they, gasp, don’t have a Tableau license.

It’s easy enough to create a dual-axis chart in Excel, if you want the bars side by side.  However, I needed overlapping bars.  There’s no standard chart design within Excel to accommodate this, which meant I had to come up with a workaround.  I perused the internet and didn’t find anyone else that had done this (I’m sure people have done it, but haven’t shared their work), so I wanted to share it with anyone that may need to do it in the future.

A sample Excel workbook can be found here (if your corporate network blocks Dropbox, send me an email and I’ll forward it to you).

The final product looks like this:

image

Steps to reproduce:

  1. Highlight your data, insert a 2-D clustered column chart

    image

  2. Change the bar and line colors if desired
  3. Right-click on one of the bars that you want on the secondary axis and choose Format Data Series
  4. Change the Plot Series On option to Secondary Axis
  5. With the bars on the secondary axis still highlighted, from the Chart Tools Design menu, change the Chart Type to a line

    image

  6. Right-click on one of the bars that are on the primary axis and choose Format Data Series
  7. Change the Series Overlap to 100%

    image

  8. The two bars on the primary axis now completely overlap each other.  You’re almost done!
  9. Remove the gridlines (important…I’ll explain in a bit) and add the axis labels (I hate it when people create dual-axis charts in Excel and don’t add the axis labels!)

    image

  10. Give the bars the “overlap” look (these are the most important steps to give the bars the proper) by right-clicking on the gray bar (i.e., the bar that’s in the first column of the table) and choosing Format Data Series
  11. Choose the Border Color option, select Solid Line and set the color to the same color as the bar (in my case, light gray)

    image

  12. Choose the Border Styles option, change the Width to 10pt (or whatever floats your boat) and change the Cap type to Flat and the Join type to Miter

    image

  13. That’s it!  You’ve create a dual-axis chart in Excel with overlapping bars on the primary axis and a line on the secondary axis.

    image
Now wait, I mentioned earlier that you should delete the gridlines.  This is critical because we’ve changed the height of the bar in the back by adding the border.  This leaves you with a couple of options:
  1. Make the border of the bar in the back really thin, but this makes it challenging to see the overlap
  2. Change the data to account for the thickness, but it’s kinda scary to me to alter the data because if you mouse over the point you see the original data
  3. Leave the bars thick so that you can clearly see the overlap

I would go with option 3 because the naked eye won’t be able to discern the difference to the bar height anyway.  The purpose of the chart, after all, is to give the overall trends and comparisons anyway, not focus on the original data. 

If that “change” in the bar size really bothers you, then simply add the data table under the chart:

image

Don’t we all love challenges!  I know I do.

17 comments :

  1. Joe MakoAugust 26, 2011 at 3:54 PM

    There are many pitfalls when creating a chart in Excel. For example, when adding the border to the budget, not just the width of the light gray bar was increased, but also the height, this can be seen in the first pair of bars where the values are 200 and 150, but visually looks like 200 and 190.

    ReplyDelete
  2. m-bAugust 26, 2011 at 5:42 PM

    By adding a line to the bar you visualize the budget as being higher than it actually is so I wouldn't use that technique. There is another option but it involves the secondary axis so I'm not sure if you can still get the line there as well:

    http://datapigtechnologies.com/blog/index.php/stacked-thermometer-chart/

    This reminded me of an interesting article by Stephen Few on dual axis which you might find interesting:

    http://www.perceptualedge.com/articles/visual_business_intelligence/dual-scaled_axes.pdf

    ReplyDelete
  3. VizWizAugust 27, 2011 at 4:23 AM

    Mike,

    I've used the secondary axis trick in the past as you outline in your excellent blog post, but you have to be 100% certain your axes are synchronized. In the example you showed, the axes didn't appear to be the same.

    Also, there is no way in Excel to use your technique, but to also have a line.

    The easiest solution is to always use Tableau.

    Andy

    ReplyDelete
  4. Keith SucklingMay 3, 2012 at 7:03 AM

    Andy: I have used the dual axis trick in Excel to show the overlap you are talking about. I solved the synchronous axis problem by having a unseen element in both charts which was the maximum value (ie. max of budget and actual in your example). Since we always start at zero so you always get the same scale on both sides.
    - can't do the line as well unless you really want to cheat though, and I have done that before as well :-S

    ReplyDelete
  5. Aayush AnandOctober 1, 2012 at 1:43 PM

    Hi,

    I wanted to know how to create non-overlapping bars in tableau as excel does normally. I don't have a trend line to show. I just need to have a dual axis chart with the bars placed adjacently as excel does normally.

    Thanks in advance,
    Aayush

    ReplyDelete
  6. VizWizOctober 1, 2012 at 5:09 PM

    Aayush,

    You don't need a dual axis chart to build this in Tableau. Use the Show Me if you're ever unsure what you need to build a standard chart type.

    As an example, connect to Superstore Sales. Choose Region, Category and Sales. Click on Show Me, then choose side-by-side bar chart.

    That's it!

    Andy

    ReplyDelete
  7. AnonymousJune 13, 2013 at 9:03 AM

    Hi!
    Could you please explain a bit more the step 10?
    I don't manage to get the overlap as in your chart.
    Thanks!
    Maggie

    ReplyDelete
    Replies
    1. VizWizJune 13, 2013 at 9:52 AM

      You have to click on the gray bars. When you click on one of them, they should all be selected. Then go to step 11.

      Delete
    2. AnonymousJune 13, 2013 at 10:24 AM

      Hi,
      I might have difficulties to follow those step as I still have Excel 2003 (yes it's possible unfortunetly :-/)
      Any back-up solution for old excel version user maybe?
      Thanks!
      /Maggie

      Delete
  8. AnonymousFebruary 24, 2014 at 10:51 PM

    Thanks for this! really helped

    ReplyDelete
  9. AnonymousJune 25, 2014 at 4:27 PM

    Thank you!!!!!

    ReplyDelete
  10. UnknownFebruary 10, 2015 at 10:52 AM

    Awesome!
    A question: Do you know how to do this for a total of 4 series, (on the chart)? (I.E ,making 2 sets of overlapping bars)

    eg: For the columns/series A,a, B,b : -> "A" over "a" , and "B" over "b".

    Thanks,,!

    ReplyDelete
    Replies
    1. VizWizFebruary 10, 2015 at 3:04 PM

      I do not know how Dan. I haven't built a chart in Excel since probably around the time I wrote this blog post.

      Delete
  11. UnknownFebruary 8, 2016 at 3:42 PM

    When you have two number of the same value the bars' top lines dont match because the thicker outline creates a problem for the bars

    ReplyDelete
  12. UnknownJune 1, 2016 at 4:26 AM

    Thank You very must.

    ReplyDelete
  13. UnknownApril 5, 2017 at 10:34 AM

    I'm stuck on how to do this in Tableau. Any suggestions??

    ReplyDelete
    Replies
    1. VizWizApril 5, 2017 at 8:23 PM

      I outline how to do this in Tableau in this post http://www.vizwiz.com/2016/09/tableau-tip-tuesday-how-to-create.html

      Delete

Subscribe to: Post Comments ( Atom )