How can I change the z-order of data series on the secondary y-axis relative to those on the primary y-axis in Excel 2003?

Question

In Excel 2003, it's possible to change the z-order (i.e. what plots on top) of data series plotted on an axis by right-clicking a point of one of the series, choosing "Format Data Series", and then using the "Series Order" tab.

However, I've got a plot with series on both primary and secondary y-axes. While I can change the relative z-order of series plotting on the same axis, I'd like to alter the z-order so that my series plotted on the secondary axis plot "below" those on the primary axis.

Excel's default behavior appears to put secondary-axis series highest in z-order. Is there a way I can alter this? Excel, VBA, or any other solution welcome!

Clarification: by z-order I mean this---imagine that each data series is drawn on a transparency, and the transparencies are overlaid to produce the complete chart. Z-order is then the order in which the transparencies are stacked. In other words, when two series intersect, the appropriate color for the series with the higher z-order should be drawn at the intersection point, and the other should be suppressed.

I am not referring to the vertical (i.e. y-axis) placement of the series; I'm completely aware that this depends upon the selected scale.

Z-order may not be a commonly-used term outside the 3-D graphics/rendering community. My apologies for any confusion.

Answer 1

Well, I found what seems to be authoritative information about how series order is handled in Excel 2003 (http://peltiertech.com/WordPress/order-of-legend-entries-in-excel-charts/). It appears that there's really nothing I'll be able to do to get my secondary axis series to plot with lower z-order than my primary axis series.

I'm going to post and accept this answer to serve as future reference for anyone else who encounters this problem.

Answer 2

My answer is not related to Excel 2003, but to help those who arrive at this page wondering about the Z-order of axes in Excel 2007+.

From what I can see, the secondary axis will ALWAYS be on top of the primary axis if the data series overlap at all. The trick then, is to place all series you want on top on the secondary axis. And vice versa with the primary axis series. But what if you want to show secondary axis tick labels along the left side of the chart, where the Primary axis usually is? You can do it by right clicking on an axis and changing this setting:

"Low" will cause the axis to jump to the other side of the chart. If you set the primary axis to "High" and the secondary axis to "Low" the axes will effectively switch sides.

I hope this helps someone. Feel free to edit this answer to improve it.

Answer 3

Plot each set of data to a different chart, make each chart transparent, then overlay them