Menu
, 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.
When I build a chart excel for mac seems to default to having the largest scale numbers as the y axis, regardless of what column order I build the chart with. I need to swap the x and y axis in order to demonstrate the relationship more clearly.
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 (if your corporate network blocks Dropbox, send me an and I’ll forward it to you).
The final product looks like this. Steps to reproduce:. Highlight your data, insert a 2-D clustered column chart. Change the bar and line colors if desired. Right-click on one of the bars that you want on the secondary axis and choose Format Data Series. Change the Plot Series On option to Secondary Axis.
With the bars on the secondary axis still highlighted, from the Chart Tools Design menu, change the Chart Type to a line. Right-click on one of the bars that are on the primary axis and choose Format Data Series. Change the Series Overlap to 100%. The two bars on the primary axis now completely overlap each other. You’re almost done!
M-b 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: This reminded me of an interesting article by Stephen Few on dual axis which you might find interesting: http://www.perceptualedge.com/articles/visualbusinessintelligence/dual-scaledaxes.pdf.