Excel chart with y axis in meter and secondary y axis in feet

Question

I have simple Excel chart, numerical values by date.

The x axis is the date.

The y axis shows numerical values in meters.

I want to show the equivalent numerical values in feet on a secondary y axis.

Is the only option to create two series, one for meters and a second for feet , and then plot both on chart, and modify axis ranges so they overlap?

Is there some option in Excel chart I haven't found that allows secondary y axis to display the y axis values in another unit, perhaps by putting in conversion formula eg feet = meters / .3048 ?

Answer 1

In order to show a secondary axis you need to plot a series on the secondary axis. If you want the secondary axis to show a different unit of measure, then, yes, the only option is to plot a data series on that axis. It does not have to contain all the values of the series on the primary Y axis. In fact, just one data point will be sufficient.

The challenge is to keep the axes aligned so the series as plotted shows the correct data points for both axes.

Excel will calculate the minimum and maximum values for a chart's Y axis and it will strive to create round numbers for that. If the metric maximum value is, for example, 100 metres, then the equivalent maximum for the imperial axis needs to be 328.084 and that is not something that Excel will default to.

You would need to inspect the maximum value of the primary Y axis, convert it to the desired unit and manually set the maximum value of the secondary Y axis to that number. This process can be automated with VBA.