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 %) to perfectly align every time I change information in the table.

 

The table has 3 columns:

1 - Percentages that start from 0% and go up to 100 % (X Axis)

2 - Amounts $ (Primary Y Axis)

3 - Percentages resulting from a division of the amounts with a fixed total (Secondary Y Axis)

 

When I generated the graph, I had to manually adjust the axis bounds (in axis options) in order for it to overlap the two lines (image 1). When I keep the axis bounds modified and then I change information in the table, the lines don't overlap correctly anymore and I have to adjust them again (Image 2). Also, when I select the "automatic" axis bounds, the alignment simply doesn't work (image 3).

 

Image 1

 

Its hard to see, but two lines overlap (and this is what I want to happen, except I don't want to manually adjust the axis bounds everytime the data changes)

 

Image 2

See how the lines don't overlap and the horizontal line doesn't show an alignment of the units ?

The horizontal line should point to ex: 3,8% on the secondary axis and 3,2 $ on the primary axis.

I would want both lines to automatically overlap everytime I change information in the table.

 

Image 3

This is when I check the Axis bounds for them to be "automatic" - It doesn't work.

 

I've tried this (setting a Maximum as an imput in the graph - I.E. Video), but my problem is I want to align two different units ($ and %) : https://www.youtube.com/watch?v=pY9XgFL3DNY

 

Opened to any suggestions !

 

Thanks a lot for your time

Charting
excel
office 365

4 Replies

Sort By
  • Qaiser_j's avatar
    Qaiser_j
    Copper Contributor
    May 26, 2022

    Hi Frlaflamme 

     

    I attached a simple image with 2 different vertical scales (The left one with the amount and the right one with age). Data refresh in the chart automatically as you change your data selection.

     

    You can check here for Microsoft Excel & Office 365.

     

    And if you are comfortable with Google Sheets, you can find it here.

     

    Thanks

     

     

    • JonPeltier's avatar
      JonPeltier
      MVP
      May 26, 2022

      Qaiser_j 

       

      How does this help? The OP states that he doesn't want to manually recalculate and adjust the axis scales when the data changes.

    • 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