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).
Newton Excel Bach, not (just) an Excel Blog 
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.
I wrote about this in Secondary Axes in Charts.
LikeLike
Jon – just rescued your post from my spam folder. WordPress must think you look suspicious 🙂
The people using these charts know (or should know) that the intersection has no particular significance, and I think there is something to be said for having both sets of data on the same chart.
But it occurs to me that it would be possible to scale the axes so that the maximum permissible stress in both materials was at the same offset.
alternatively I could plot % of permissible stress, rather than the actual stress.
LikeLike
Pingback: 3 year report | Newton Excel Bach, not (just) an Excel Blog
Is there any way i can assign name to both the axis?
LikeLike
Vikram – once you have assigned a range to a second vertical axis, you can then use the Axis Titles Icon on the Chart Tools – Layout tab to assign a title to both vertical axes (in version 2007/2010).
Does that answer your question?
LikeLike
BTW, secondary axes seem to be broken in Excel 2010. Has anyone noticed or found a fix for it.
LikeLike
Gabriel – can you provide more details please. I am using Excel 2010 and the secondary axes seem to work in the same way as in 2007.
LikeLike
Here is an example with 2 curves:
x y1 y2
0.01 1 1
10 2 2
20 3 3
30 4 4
40 5 5
(Y1 and Y2 are identical)
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.
LikeLike
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.
LikeLike