May 29 2024 06:49 PM
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!
May 29 2024 09:21 PM
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.
May 29 2024 11:36 PM
May 30 2024 12:42 AM
@BenSaw You can use the Feedback button on the Help ribbon to send a note to MS.
May 29 2024 09:21 PM
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.