Creating a 2+ column chart with a secondary axis

Creating a multicolumn chart with a secondary axis sounds easy, but there's a quirk in Excel that makes it more difficult than it should be.

Background

For those who don't know, a secondary axis is where you have a different scale/units on the opposite side of the chart. This is useful for charting two values against a third when the two values are significantly different scales. For example, if you are charting the amount and number of items you purchase at various stores, you might have some data and a chart that looks like the image below. It's quickly apparent that the second values are not easily identifiable. A secondary axis in this case will put a smaller range on the left side, and the items data will be plotted using that range instead.

When creating a column or bar chart and you want to use this secondary axis, you could run into trouble with the columns or bars overlapping each other once you create the new axis. It would look similar to this.

Note: To create the secondary axis, click on one of the data series (ie, a column), then format and choose secondary axis.

What is happening is that Excel plots each axis individually, and does not take into account the other axis. That is, the primary axis only has 1 set of data (dollars) so it plots a single column graph. Similarly, the secondary axis only has one value, so it plots a single column graph over top of the primary.

Our recommendation

There is a somewhat simple workaround for this. What we want is a 2 column graph, with the primary values in the first column and secondary in the second (tweak as needed). So, for the primary values, we'll add a second column of data to the primary axis after our primary data, with the values all being 0. Now, for the primary axis, we have two values again for each company, so it plots 2 columns, with the second column always being zero. Similarly, we'll add a another column of 0's for the secondary axis, but before the data this time so that the first column is empty and the second has the real data. Now, when the primary and secondary overlap, the primary is always on the left side and secondary on the right. Delete the extra names from the legend by clicking on each extra name, and you'll have the following.

This can be applied to when you have more than just 2 values as well, and the trick is to have double the amount of data, so that the columns are properly adjusted. For example, if you had 3 dollar columns and 2 item columns, then you would need 10 columns, adding the 0 columns just before or after each respectively column.

Alternatives

A second option which is not as visually pleasing, but does not use extra data columns, is to modify the gap width for the secondary axis, thereby making the secondary axis column narrower.