Sometimes rarely used features in an application are so prominently placed that we become blind to them, and can’t find them when we need them.

This happened to me recently with the procedure for assigning an Excel chart series to a secondary axis, so that series covering widely different ranges can be displayed on the same graph. The chart below shows a plot of concrete and reinforcement stresses in a reinforced concrete section subject to increasing bending moment.

Concrete and reinforcement stresses - one Y axis

In order to make the variation in the concrete stress more readable I wanted to assign one of the ranges to a second Y axis, but I couldn’t remember the procedure for doing this, and searching through the “Chart Tools” ribbons didn’t provide anything that would do what I wanted. In particular, the “Axes” tab on the “Layout” sub-ribbon (which would surely be the logical place to put a tool for inserting a second axis) only referred to the primary axes.

As a last resort I looked up the on-line help, which told me I should select the range I wanted on the second axis, then click the “Format Selection” button in the “Current Selection” group on the Format sub-ribbon under Chart Tools. This provides a dialog box with the options of “Primary Axis” and “Secondary Axis”, and selecting Secondary Axis did what I wanted:

Steel Stress on Secondary Y Axis

The dialog box looked strangely familiar though. Then I realised that this is the same dialog you get by selecting a chart series and then selecting Format Data Series… from the right-click menu, an operation I probably do hundreds of times a week. The “secondary axis” option was something that I had become so accustomed to ignoring that I just didn’t see it any more!

Further investigation revealed that once the secondary axis has been installed it then appears under the “axes” button, and whats more you can now insert a secondary X axis by clicking on Axes – Secondary Horizontal Axis.

Whether a secondary horizontal axis is ever a good idea is another matter, but for some ideas on what you can do with secondary axes, have a look at the Jon Peltier Blog (see both the linked post and the “related posts” listed at the bottom for some useful ideas).

9 Responses to Secondary axes in Excel 2007

You need to take care that the relative behavior of the two lines doesn’t confuse the viewer. Your dual axis chart shows the two lines converging at the upper end of the chart, but changing the relative scales could make them cross anywhere along their lengths, and the crossing is entirely illusory.

You chart them, and they look identical, as expected. You change the second curve to have a secondary axis, and they still look identical (because the primary and secondary vertical scales are the same). So far, so good. If you change the X axis to logarithmic scale, then the log transformation works for the first curve, but not for the second. The second curve now seems to behave as a line graph, not as a semilog graph. I am sure this did not happen in Excel 2003.

Unfortunately, I cannot post the excel file or the graph, but I think you get the idea.

When you create a secondary Y axis it seems that a secondary X axis is set up as well. This has the same properties as the primary X axis when it is set up, but changes to the primary X axis do not affect the secondary X axis.

To make the secondary X axis a log scale you have to select the series allocated to the secondary Y axis, then select the Chart-Tools, Layout tab, then Axes – Secondary Horizontal axes – More Secondary Horizontal axis options. That should open a dialog box with the logarithmic scale option.