PDA

View Full Version : Solved: Procedure for Creating a Chart With 2 Y Axis'



Cyberdude
08-27-2006, 03:30 PM
Most of my XYScatter charts have two Y-Axes, each with its own independent scale of values. I created my first 2-axis chart many years ago with a great deal of trial & error, and have copied the first one ever since to create a new one. Every so often I try to create one from scratch, and eventually give up.

My question: can anyone give me a link to a tutorial that tells what procedure to follow to create a 2-axis chart?

Aaron Blood
08-28-2006, 06:39 AM
It shouldn't be that difficult...

If you start with an XY chart (or any chart) that has two series on the same Y axis, all you have to do is right-click one of the series and select:

Format Data Series...|Axis|Secondary axis|OK

Cyberdude
08-28-2006, 02:05 PM
Hi, Aaron! Thanks for the reply.

OK, I finally got one created, but it was anything but easy.

The ultimate problem was that I need the axis scale to be on the opposite side of the default way it is created. I could move the primary axis from the left side to the right side using the "High" option. One would think that I could then move the secondary axis from the right to the left by using "High", but that doesn't work. The secondary axis labels disappear! I struggled with that, then I used the "Low" option instead of the "High" option, and ... Voila! ... it worked.

Also, after plotting the first two Series, I changed the "Chart Type" to "Lines on 2 Axes", which helped a lot because the second Series couldn't be seen due to the scale differences. So after getting the scale values to be what I wanted, I tackled the switch of axes from one side to the other.

I've never been able to use the chart wizard very well. I usually wind up doing a lot manually. For my next trick, I will go through the whole process manually and let VBA generate the code for me.

Incidentally, where you choose the "tick mark labels" position using "Next to Axis", "High", "Low" or "None", what is the "Low" option supposed to do? It never seemed to do much for me until I used it to move the secondary scale from the right side to the left side. One would think that "High" should be used for that. Weird.

Aaron Blood
08-30-2006, 08:30 AM
Incidentally, where you choose the "tick mark labels" position using "Next to Axis", "High", "Low" or "None", what is the "Low" option supposed to do? It never seemed to do much for me until I used it to move the secondary scale from the right side to the left side. One would think that "High" should be used for that. Weird.

The low/high settings are also useful when your values include positive and negative values. The axis labels in that case would be at zero by default which would have them running down the middle of your chart. Setting them Hi-Lo puts em back in the margin area. If your data doesn't cross zero then you're right, you wouldn't see too much difference.

Cyberdude
08-30-2006, 01:14 PM
Thanks, Aaron. Some day I'll know most of this stuff (I hope). :friends:

JonPeltier
09-02-2006, 12:08 PM
The ultimate problem was that I need the axis scale to be on the opposite side of the default way it is created. I could move the primary axis from the left side to the right side using the "High" option. One would think that I could then move the secondary axis from the right to the left by using "High", but that doesn't work. The secondary axis labels disappear! I struggled with that, then I used the "Low" option instead of the "High" option, and ... Voila! ... it worked.

What you were moving was the axis labels, not the axis. By default, Excel puts the primary axes on the left or bottom and the secondary axes on the right or top of the chart. You could have gotten the axis and albels in the appropriate place by choosing the other series to move to the secondary axis. You can move a Y axis by formatting the X axis, and adjusting the Crosses At parameters on the Scale tab. This would have helped you move the primary axis (labels and tickmarks and all) to the right. To move the secondary Y axis, you need a secondary X axis. Since Excel often doesn't draw this, go to Chart menu > Chart Tools > Chart Options > Axes tab, and select a secondary X axis (you can return to the dialog to remove it alter). Now format this axis to move the secondary Y axis.

The High and Low items on the Patterns tab move the labels only. The axis itself with its tick marks stays where it was, since you didn't change where it crosses the other axis. High means toward the highest numbers on the corresponding perpendicular axis, low means toward the lowest. This is why you have to put some labels on the high end and others on the low end.

Cyberdude
09-02-2006, 02:46 PM
Nice explanation of a complex topic, Jon. Thank you once again. I'm slowly getting it together.

JonPeltier
09-02-2006, 02:53 PM
Glad to help. Charting simply adds about three more layers of complexity on top of Excel, and a lot of it isn't intuitive until you've spent lots of time with it. That makes it hard to ask the right question or even to describe an issue clearly.