SOLVED

Getting x axis to cross both y axes at zero

Copper Contributor

Hi. First time caller to community...

I can't work out on an excel chart with 2 y axes how to get the x axis to cross at zero for both axes. I can do it for the left y axis but not the right y axis.

Many thanks!

3 Replies
best response confirmed by BenSaw (Copper Contributor)
Solution

@BenSaw Let's say you want to plot two lines. One with values between 100 and -30 on the primary axis. The other series has values ranging from 7 to -5, on the secondary axis.

 

Excel will set the boundaries for both axes automatically. The primary from 120 to -40, crossing the X.axis at zero and the secondary from 8 to -6 and the zeroes indeed do not line-up. Even though the 2nd axis setting has an option to have the horizontal axis cross at a particular value, it doesn't seem to do anything. At least not on my Excel (currently on Excel for MS365 on a Mac).

 

To fix that, you need to set the max and min boundaries of the 2nd axis manually, so that the ratio between them is the same as for the primary axis.

For the first axis the ratio is -3 (i.e. 120 / -40 ). Now set the 2nd axis to have a minimum of -5 and the max at 15. That results in the same ratio ( 15 / -5 = -3 ). Or perhaps you would prefer 18 and -6. As long as you keep the ratio at -3, the zeroes will line up. You may have to play with the major and minor units as well to get the labels right.

See attached file for an example.

 

 

Great, thanks a lot, that works. Good bug fix opportunity for MS here! Do you know how I can let MS know about this problem?

@BenSaw You can use the Feedback button on the Help ribbon to send a note to MS.

1 best response

Accepted Solutions
best response confirmed by BenSaw (Copper Contributor)
Solution

@BenSaw Let's say you want to plot two lines. One with values between 100 and -30 on the primary axis. The other series has values ranging from 7 to -5, on the secondary axis.

 

Excel will set the boundaries for both axes automatically. The primary from 120 to -40, crossing the X.axis at zero and the secondary from 8 to -6 and the zeroes indeed do not line-up. Even though the 2nd axis setting has an option to have the horizontal axis cross at a particular value, it doesn't seem to do anything. At least not on my Excel (currently on Excel for MS365 on a Mac).

 

To fix that, you need to set the max and min boundaries of the 2nd axis manually, so that the ratio between them is the same as for the primary axis.

For the first axis the ratio is -3 (i.e. 120 / -40 ). Now set the 2nd axis to have a minimum of -5 and the max at 15. That results in the same ratio ( 15 / -5 = -3 ). Or perhaps you would prefer 18 and -6. As long as you keep the ratio at -3, the zeroes will line up. You may have to play with the major and minor units as well to get the labels right.

See attached file for an example.

 

 

View solution in original post