Multiple Y axis in Excel charts

Question

As far as I can tell Excel has a limit of 2 y axis, i.e. 2 vertical axis, when creating a chart.  I would like to be able to create charts with more than 2, possible up to 8 vertical axis in order to provide comparisons on a single chart, rather than have multiple charts with just 2 datasets compared.

For example, I want to compare the price history of aluminum, copper, iron, and zinc all on one chart with a line for each metal in different colors, or possibly different combinations of dashes and dots for black and white printing.

Is there a way to do this in Excel?

If not I would like to suggest that feature be added.

Answer 1

Hello,

this can be done in Excel, but it's not a very good data visualization. The screenshot shows the final result.

What you are looking at are three overlaid charts:

• chart with blue and orange lines, blue on primary, orange on secondary axis
• chart with green and yellow lines, green on primary, yellow on secondary axis, axes line and labels hidden, chart background set to "none", then placed on top of first chart.
• chart with green and yellow series, green on primary, yellow on secondary axis, series formatted to have no visible lines, chart background set to "none", chart made wider so vertical axes show outside of blue/orange axes when overlaying first two chart

Basically you need to play with hiding and showing chart elements, making the background of the overlaying charts transparent and then carefully placing the charts on top of each other.

Answer 2

Respectfully, I think you a little confused about axis and data series.

See top chart in screen capture (the data is total junk!)

I could have used black lines for all data series and had different dots/dashes for each line.

When the X values are numeric (here the years 2000 to 2016) it is best not to have text in the top cell (here A1) while making the chart - Excel will think ths column is a data series.

The bottom chart shows the correct use of the secondary Y axis: the range of values in the two data series are very different.

You might like to do an internet search for <excel chart tutorial>

best wishes

Answer 3

Thank you for the reply.

I am looking for something like this chart that has 4 vertical axis that I copied from a website.

http://www.infomine.com/ChartsAndData/ChartBuilder.aspx?gf=110569.USD.lb|110563.USD.lb|140263.USD.lb|110565.USD.lb&df=19160101&dt=20160413&dr=MAX

I would like to be able to do this in Excel where vertical axis marks are "stacked" and color coded to the lines on the graph.


Answer 4

Hi Teylyn,

Thank you for the example. It is a good suggestion for a workaround I was hoping to avoid.

For those who are interested, here is a link to the underlying data:

World Bank Commodity Markets Monthly Price Data in Excel Format

http://www.worldbank.org/en/research/commodity-markets

As I mentioned above, I would like to compare the price history of aluminum, copper, iron, and zinc all on one chart with a line for each metal in different colors, or possibly different combinations of dashes and dots for black and white printing.

The InfoMine.com website has a web based version of what I would like to do in Excel that has 4 vertical axis (see example below).

http://www.infomine.com/ChartsAndData/ChartBuilder.aspx?gf=110569.USD.lb|110563.USD.lb|140263.USD.lb|110565.USD.lb&df=19160101&dt=20160413&dr=MAX

I would like to be able to do this in Excel where vertical axis marks are "stacked" and color coded to matched the lines on the graph like this example:

Thank you for all your suggestions and advice.

If Excel is not able to do this, I would like to suggest that this functionality be added.

If there are inexpensive or free third party products that can do this please let me know.

Answer 5

Excel 2010 with free PowerPivot and Power Query Add-In.

Compatible with Office 2013/2016 Pro Plus with built-in PP & PQ.

With 6 (six) metals and 1 (one) vertical axis.

With Normalized Measures as explained in the book:

"Power Pivot Alchemy" by Rob Collie and Bill Jelen.

http://www.mediafire.com/download/4hnjgb27gu2ebba/04_14_16a.xlsxhttp://www.mediafire.com/download/im8d594t9gqz8e3/04_14_16a.pdf