Excel 2003 - Data Plot Disappears in Chart - Why & How

[powerjak]

I'm building a 2-Axes Chart with 5 data Series (4 against the left axis and 1 against the right axis) from the data below. I select the Chart Type as Custom Type | Lines on 2 Axes. The resulting Chart defaults to the same scale on both Left & Right. All five of the data Series chart OK until I change the Right Axis Max/Major/Minor Scale values on Format Axes to Max=1600; Major=200; Minor=100. When I click OK to this Scale change, I get the correct Scale, but data Series #4 disappears from the chart even though it is correctly defined in the data Series.

A secondary quirk is that if I change the Left Axis to the 0-1600 Max/Major/Minor values, only data Series 4 & 5 display and Series 1-3 disappear. Just plain wacky!

What's causing the problem ... and how do I fix it? I'm stumped! My desired outcome is to display data Series 1-4 against the Left Y-Axis (0-4,500,000 values) and data Series 5 against the Right Axis (0-1600 values) to show the correlation ... just the way it does after the Right Axis Scale change ... but including data Series 4 on the Chart.


Data Table:

Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Total
CYBASE 3,532,636 3,642,969 3,725,035 3,153,938 2,765,536 2,534,340 2,742,544 2,703,565 2,580,390 2,946,061 2,785,638 3,271,551 36,384,203
CY03-04 3,576,483 3,707,779 3,809,016 3,377,474 2,874,900 2,784,966 2,839,001 2,897,543 2,681,435 3,054,208 3,239,808 3,346,287 38,188,900
CY04-05 3,534,112 3,694,437 3,970,632 3,507,425 2,965,094 2,739,341 2,898,962 2,751,727 2,850,523 3,299,059 2,915,556 3,277,693 38,404,561
CY05-06 3,736,863 4,202,719 4,251,364 3,856,372 3,234,363 2,944,398 3,103,357 3,015,456 2,986,545 31,331,437
CDD 832 948 710 263 171 10 0 0 27 315 631 1236 5,143

 

[SydneyGeek]

Try right-clicking each data series and picking Format Data Series. Under the Axes tab, select whether the series maps to the Primary or Secondary axis. You'll want the first 4 series on Primary, and the 5th (it will be hugging the X-axis at this stage) on the Secondary.

Denis

 

[powerjak]

Right on the money! Thanks for the assist. A bit strange how Excel automatically assumes that Series 1-3 are associated with the Primary Axis and 4&5 are associated with the Secondary Axis if you manually Scale the Secondary Axis to 0-1600 as in my example (which is why #4 disappears since it's way above the Max of the Scale set). Regardless, when the axes are individually set for each data series as you suggest, all is "made well" and the Secondary Axis automatically Scales 1-1400 as desired.

Thanks again Denis. I raise my Foster to "the man down under" ... apparently, in Sydney.

 

[SydneyGeek]

Yeah, Excel's helpful sometimes isn't it?

Glad to help

Denis