# Aligning the horizon on 2 y axis

#### aselldurn

##### New Member
I have a line chart with 2 y axis on it. the data for both the lines has both negative and positive values. The scales on both axis automatically update, but because of this, 0 on both axis aren't aligned. because the data will vary regularly, I can't manually set the values of the axis.

Is there a way to align the 0 on both y axis, without writing a macro to do it?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Create two additional series. One with the max and min for data on the Y-1 axis and another for max and min of data on Y-2 axis. Add these to the chart, assigning max-min Y-2 to the Y-1 axis and vice versa for the other. Then set both series to "invisible" - line & fill to "none". If your chart has a legend, you can click on the legend label for the two faux series and just hit your delete key to remove them from the legend.
book1
ABCDE
120042005Max/Minof2004Max/Minof2005
2North5121534
3South1022-512
4East1534
5West-518
Sheet1

HTH

Thanks for this but it doesn't work in my case. The trouble is that the primary axis contains some big numbers, the secondary one is percentages that are generally no more than 10%. So, using the method you suggested basically sets the secondary axis to the same scale as the primary one - defeating the object of having two in the first place!

Thanks for your help - I don't think there's going to be a quick fix for this one.....

aselldurn said:
...So, using the method you suggested basically sets the secondary axis to the same scale as the primary one - defeating the object of having two in the first place!...
Good point... I played with this a bit and did come up with a formula that works -- somewhat.

Essentially it's the same concept as before, add a 2nd series to the y2 axis and hide it (leave y1 alone). This is based on the assumption that a portion of y1 is above zero and a portion is below. What the formula does is compare the portion of the y1 axis that is negative against the max-min of y2 and then either raise the max of Y2 or lower the min of Y2 in order to create the same ratio negative portion of y2 versus max-min of Y2 as found on Y1. Sometimes it's right on the money and sometimes it's close. What cannot be known for a certainty without using VBA is what Excel will assign for the major units on Y1 or Y2. So there's some "rounding" on both axis which sometimes misaligns them a bit.

All in all, an interesting problem. Hope this helps...
test y axis synch.xls
ABCD
12004%ofTotHiddenSeries
2North17511.458333
3South10-0.250
4East-50
5West-30-0.25
Sheet1

The formula in D2 is a bit of a humdinger...
=IF(((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))>((0-MIN(C2:C5))/(MAX(C2:C5)-MIN(C2:C5))),(MAX(C2:C5)*((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5))/((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5))-1))),((MIN(C2:C5)*(((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))-1))/((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))))

I named the ranges and then applied the names to the formula so hopefully it's a little easier to read...

=IF(((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))>((0-MIN(Y2Series))/(MAX(Y2Series)-MIN(Y2Series))),(MAX(Y2Series)*((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series))/((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series))-1))),((MIN(Y2Series)*(((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))-1))/((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))))

Replies
3
Views
324
Replies
1
Views
264
Replies
0
Views
959
Replies
1
Views
646
Replies
7
Views
637

1,221,153
Messages
6,158,235
Members
451,477
Latest member
CWebbers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back