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

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

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

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.

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, Align X Axis to Y=0 on Two Y Axes.

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