Charts: make second axis series "always appear above"

[doug firr]

I have a combination column and line chart with the line series on the secondary axis. Is there a way that I can tell excel to scale the secondary axis so as the line always appears above the columns? As it is it appears above in most instances but sometimes appears below the columns.

 

[Jerry Sullivan]

Here is some VBA code that you could try.
It assumes that your chart is setup as you described and would need additional error-checking if it needed to validate the chart types for primary and secondary axes.

Sub Scale_Line_Chart_Over_Column_Chart()
'---Assumes the ActiveChart has a Column Chart on Primary Axis
'   --and a Line Chart on Secondary Axis.
'   ---Adjusts the MaximumScale Properties of both Axes to that
'   ---Minimum LineChart value is at or above Maximum ColChart value

    Dim dAxis1Min As Double, dY1Max As Double
    Dim dAxis2Min As Double, dY2Max As Double, dY2Min As Double
    Dim dRatio As Double, dAxis2Max As Double, dBuffer As Double
    Dim i As Long, lPrimary As Long, lSecondary As Long
 
    If ActiveChart Is Nothing Then
        MsgBox "No Chart Selected"
        Exit Sub
    End If
 
    With ActiveChart.SeriesCollection
        For i = 1 To .Count
            If .Item(i).AxisGroup = xlPrimary Then
                lPrimary = i
                Exit For
            End If
        Next i
        For i = 1 To .Count
            If .Item(i).AxisGroup = xlSecondary Then
                lSecondary = i
                Exit For
            End If
        Next i
        If lPrimary * lSecondary = 0 Then
            MsgBox "Must have both Primary and Secondary Axes"
            Exit Sub
        End If
        '--Read values needed to calc MaxScales
        dY1Max = Application.Max(.Item(lPrimary).Values)
        dY2Min = Application.Min(.Item(lSecondary).Values)
        dY2Max = Application.Max(.Item(lSecondary).Values)
    End With
    
    With ActiveChart.Axes(xlValue, xlPrimary)
        .MinimumScaleIsAuto = False
        .MaximumScaleIsAuto = False
         dAxis1Min = .MinimumScale
    End With
    
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScaleIsAuto = False
        .MaximumScaleIsAuto = False
        dAxis2Min = .MinimumScale
        '--Make Axis2 10% greater than needed for Max value
        dBuffer = 0.1 * (dY2Max - dAxis2Min)
        dAxis2Max = dY2Max + dBuffer
        .MaximumScale = dAxis2Max
    End With
    
    With ActiveChart.Axes(xlValue, xlPrimary)
        '--calc ratio of Axis 1/Axis 2
        dRatio = (dY1Max - dAxis1Min) / (dY2Min - dAxis2Min - (0.5 * dBuffer))
        .MaximumScale = dAxis2Max * dRatio + (dAxis1Min - dAxis2Min)
    End With
End Sub

 

[doug firr]

Thanks Jerry