Peltier Technical Services, Inc.

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

Peltier Tech Chart Utility


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.


Peltier Tech Chart Utility

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile