Bar-Line Combo Chart

[mjamromi]

This question is for Jon...

I found your explanation of this
(http://www.peltiertech.com/Excel/Charts/BarLineCombo.html) extremely
helpful! But I noticed that the chart only works on the range of 3 to
10 data points. With less than 3 or with more than 10 data points, the
lines get out of alignment with the bars (the bars and the vertical axis
labels remain properly aligned, but the lines no longer do). If you
know how to fix this problem, I would greatly appreciate your reply.
Thank you so much.

 

[Andy Pope]

Hi,

Sure Jon won't mind me jumping in.
The points don't line up as the secondary y axis has changed it's
maximum value so you need stop it being automatic and specify the value.
The best time to do that is when you change the 1st bar series into
xy-scatter, BUT do not change these 2 properties until you have set the
secondary y axis.
• Categories in reverse order
• Value (Y) axis crosses at maximum category

Then your points should align, as long as the maximum value you specify
is correct.

If you have already built your chart then you can unset those 2
properties. Then you the Chart Options to first remove the secondary y
axis. The again to apply it, when you re-apply the axis you should then
be able to select and set it.

Cheers
Andy

 

[mjamromi]

Andy,

Thanks for your reply. The one thing I seem to be unable to do is get
the secondary Y axis to display. I've turned it off and on again using
Chart Options, but I can't get it to show. How do I get it to work?

 

[Andy Pope]

Have you unchecked the crosses at maximum AND In reverse order flags for
the main Y axis?

 

[Jon Peltier]

My web page was written based on Excel 97. The secondary Y axis was
never explicitly scaled for the data. A maximum value between 3 and 8
for a typical chart size just happened to display properly with the
default scale settings.

In earlier version of Excel (97 and maybe 2000), when you add the
scatter series, only the secondary X axis appears automatically, not the
secondary Y axis. To display this axis, go to Chart Options on the Chart
menu, click on the Axes tab, and check the Secondary Y Axis box.

In Excel 2003 (and presumably 2002), changing the second series to a
Scatter type adds both secondary axes. This is among the very few
charting enhancements that has come along since Excel 97; another is the
expanded options for data labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______