Excel chart does not plot entire data series

[Hoozits]

I have a chart with two vertical axis that charts up to four data series. There is one horizontal axis, but the ranges of each data series may be longer or shorter than the others.

Here is an example function for one of the series

=SERIES("GOOGL P/E ratio relative to an index (right axis)",'DATA-Index'!$A$154:$A$256,'DATA-Internet'!$BH$154:$BH$256,3)

Notice that this series is 256 - 154 + 1 = 103 data elements long for both the series itself and the points used for the horizontal axis (which happen to be dates). However, the chart that gets generated displays only 30 of the points. When I right-click on the series, and choose Select Data and click Edit for both the Series and the horizontal axis labels, all 103 points get highlighted. So what gives?

 

[Hoozits]

Is there a way, using vba, to select which x axis a series should use? For that matter, how do you change the x axis for a series to be either primary or secondary without the use of vba?

 

[Andrew Poulsom]

Any data plotted on the secondary y axis will use the secondary x axis if there is one.

 

[Hoozits]

OK, so that is a restriction in excel, then? So it's possible that I have plotted data on the secondary y axis (which now must use the secondary x axis, if I understand you correctly), but if my x values range doesn't fall in the range of the secondary x axis, then I have a problem. Correct?

Btw, I am really grateful for your help on this.

 

[Andrew Poulsom]

For a Line chart the x axis scale is determined by setting for the first series. For an XY Scatter chart you can have different x axis ranges for each series and the axis will scale to fit both.

 

[Hoozits]

So if I want to see all of the data in each series of different lengths and on different x axis, I need to limit myself to a xy scatter. Is that safe to say?

 

[Andrew Poulsom]

Yes I think that's right.

 

[Hoozits]

Thank you for your help on this. Your explanation led me to write code to set the secondary axis equal in range to the primary one and hide the secondary axis. This seems to have done the trick. Thanks again.