Forum Discussion

Frlaflamme's avatar
Frlaflamme
Copper Contributor
Apr 05, 2022

Graph: auto matching dual "y" axis with different units

Hi there,    I'm working on a graph resulting from a table and my objective is: when I modify the data in the table, I want both y axis (primary y axis is in millions and the secondary y axis is in...
Charting
excel
office 365
Jon_Peltier1300's avatar
Jon_Peltier1300
Copper Contributor
Apr 06, 2022

You need to set up your chart axes so the zero is the same percentage of min to max on both scales. I have a VBA procedure which can help, https://peltiertech.com/Excel/Charts/AlignXon2Ys.html.

JonPeltier's avatar
JonPeltier
MVP
May 26, 2022

Never mind, this doesn't really help. Don't know what I was thinking.

 

Here is a VBA procedure that will adjust the secondary axis maximum so the two series line up:

 

Sub AlignSecondaryToPrimary()
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "NO CHART SELECTED"
  Else
    With ActiveChart
      Dim PrimaryY As Variant
      PrimaryY = .SeriesCollection(1).Values
      Dim SecondaryY As Variant
      SecondaryY = .SeriesCollection(2).Values
      Dim MaxPrimaryY As Double
      MaxPrimaryY = WorksheetFunction.Max(PrimaryY)
      Dim MaxSecondaryY As Double
      MaxSecondaryY = WorksheetFunction.Max(SecondaryY)
      Dim Ratio As Double
      Ratio = MaxSecondaryY / MaxPrimaryY
      .Axes(xlValue, xlSecondary).MaximumScale = _
        Ratio * .Axes(xlValue, xlPrimary).MaximumScale
    End With
  End If
End Sub

 

Resources