### Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Excel Dashboards

Books at Amazon.com

# Align X Axis to Y=0 on Two Y Axes

A common question I'm asked (twice on one recent day) is:

I have a chart with data on the primary and secondary Y axes, and both axes range from negative to positive numbers. How do I make the X axis cross each Y axis at X=0?

 Misaligned Axes Aligned Axes

The trick is knowing what must line up. It all boils down to 7th grade algebra, but try finding a 7th grader when you need one. In order for the X axis to split both Y axes at the same point, the relative amounts above and below must be the same. In terms of the arithmetic:

Ymin(pri)/Ymax(pri) = Ymin(sec)/Ymax(sec)

Knowing this, you can easily compute the needed axis scale parameters to make the X axis line up with zero on both Y axes.

But that's boring. You can write macros to do this automatically. The following code includes four small procedures that invoke a larger one, depending which of the four relevant axis parameters you will allow to vary to make it work.

 ``` ' Helper Macros Sub AlignY_PrimaryMinimum() AlignY 1 End Sub Sub AlignY_PrimaryMaximum() AlignY 2 End Sub Sub AlignY_SecondaryMinimum() AlignY 3 End Sub Sub AlignY_SecondaryMaximum() AlignY 4 End Sub ' The Main Event Sub AlignY(FreeParam As Integer) '' FreeParam: AXIS ALLOWED TO VARY '' 1: Y1 (PRI) MIN '' 2: Y1 (PRI) MAX '' 3: Y2 (SEC) MIN '' 4: Y2 (SEC) MAX Dim Y1min As Double Dim Y1max As Double Dim Y2min As Double Dim Y2max As Double With ActiveChart With .Axes(2, 1) Y1min = .MinimumScale Y1max = .MaximumScale .MinimumScaleIsAuto = False .MaximumScaleIsAuto = False End With With .Axes(2, 2) Y2min = .MinimumScale Y2max = .MaximumScale .MinimumScaleIsAuto = False .MaximumScaleIsAuto = False End With Select Case FreeParam Case 1 If Y2max <> 0 Then _ .Axes(2, 1).MinimumScale = Y2min * Y1max / Y2max Case 2 If Y2min <> 0 Then _ .Axes(2, 1).MaximumScale = Y1min * Y2max / Y2min Case 3 If Y1max <> 0 Then _ .Axes(2, 2).MinimumScale = Y1min * Y2max / Y1max Case 4 If Y1min <> 0 Then _ .Axes(2, 2).MaximumScale = Y2min * Y1max / Y1min End Select End With End Sub```

This makes the axes line up at zero, but it doesn't always produce a nice chart: a more rigorous procedure would adjust the major tick spacing of both Y axes. Sometimes the adjusted axis starts at a silly number, like -3.33333333, because of the fractions. You can then manually adjust the minimum scale or major tick spacing of one of the axes and rerun the macro, changing a different scale parameter.

Let's see how the macro works, starting with a simple chart. For clarity I've moved the X axis tick labels to the Low position.

The following charts illustrate the results of running AlignY_PrimaryMaximum (left) and AlignY_SecondaryMaximum (right). In both cases, the axes have aligned nicely with no further adjustments needed. In the real world this is likely to be rare.

The following charts illustrate the results of running AlignY_PrimaryMinimum (left) and AlignY_SecondaryMinimum (right). In both cases, the axes have aligned their respective Y=0 with the X axis, but the division has resulted in incompatible tick spacings.

The macro above could be refined to adjust tick spacings as well as the axis limits. But in simple cases like this, you can adjust the charts by inspection. For the left-hand chart above, I imposed a primary axis major tick spacing of 3, and let Excel automatically scale the axis endpoints (see the dialog box below). The result was a chart that was nicely aligned without further adjustments (left, below the dialog box).

For the right-hand chart above, I imposed a secondary axis tick spacing of 25, and let Excel scale the maximum and minimum values itself. This disrupted the alignment, so I ran AlignY_SecondaryMinimum again, and the result (below right) is another nice chart.