Dynamic chart with two Y axis - trying to keep both axis at same scale

[Beezkneez]

I have a personnel resourcing workbook that is used by a number of different departments.
Each department is only interested in their own view of the data. Only a couple of senior managers care about global views of the data.

I have created a chart linked to a cell with a List data validation that users can select their view from, with the intention that the chart will dynamically populate based on the selection in that cell.

The chart is a stacked column chart on the first Y axis representing demand with four series (confirmed, estimated, pipeline, other), and a line chart on the second Y axis representing supply, with three series (permanent, perm+contractor, and perm+contract+outsourced).

I have the dynamic data feeding through fine, including a dynamic title that feeds from the list box.

My problem is that when the two Y axis have any significant variation in their values, the automatic options for format axis / maximum value causes the first Y axis to plot in a different scale to the second Y axis.

Okay, it isn't brutally difficult to manually correct your scale on the second Y axis, but I was wondering if there is a way to force both be on the same scale, ideally selecting whichever auto scale is larger. The stakeholders of the workbook are generally opposed to using VB unless I can sell them on the fact that there is no other way.

Some department views may only be 5 head count, while others could be 40+, so forcing the scales generally causes reading issues.

The previous version of the workbook had individual charts for every department that needed updating every time IT restructures. I was trying to create a solution that was not such a strain on the recalculations.

Any assistance is appreciated. I have moved away from this kind of dev work in to a more analytical role in recent years where the most sophisticated thing I have needed to do is a subtotal formula. I thought this "little job" would be a good opportunity to dust off the Excel abilities.
Thanks again in advance.

 

[pbornemeier]

I don't believe that is possible without VBA.

If the chart is on a Chart sheet then this code on the chart code page will automatically adjust the maximum scales whenever the chart becomes active:

Option Explicit

Private Sub Chart_Activate()

    Dim sngPrimaryAutoMax As Single
    Dim sngSecondaryAutoMax As Single
    Dim sngMaxManualScale As Single

    ActiveChart.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
    
    sngPrimaryAutoMax = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
    sngSecondaryAutoMax = ActiveChart.Axes(xlValue, xlSecondary).MaximumScale
    
    If sngPrimaryAutoMax > sngSecondaryAutoMax Then
        sngMaxManualScale = sngPrimaryAutoMax
    Else
        sngMaxManualScale = sngSecondaryAutoMax
    End If
    
    ActiveChart.Axes(xlValue, xlPrimary).MaximumScale = sngMaxManualScale
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = sngMaxManualScale
    
End Sub

If the chart is embedded on a worksheet then this code could be called by a worksheet event or triggered with a command button:

Sub EqualVerticalMaxScales()

    Dim sngPrimaryAutoMax As Single
    Dim sngSecondaryAutoMax As Single
    Dim sngMaxManualScale As Single
    
    With ChartObjects(1).Chart  'Modify if there are multiple charts on a single page

        .Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
        
        sngPrimaryAutoMax = .Axes(xlValue, xlPrimary).MaximumScale
        sngSecondaryAutoMax = .Axes(xlValue, xlSecondary).MaximumScale
        
        If sngPrimaryAutoMax > sngSecondaryAutoMax Then
            sngMaxManualScale = sngPrimaryAutoMax
        Else
            sngMaxManualScale = sngSecondaryAutoMax
        End If
        
        .Axes(xlValue, xlPrimary).MaximumScale = sngMaxManualScale
        .Axes(xlValue, xlSecondary).MaximumScale = sngMaxManualScale
        
    End With
    
End Sub

 

[Jon Peltier]

1. Do you really need a secondary axis, if the scales will be the same?

2. a.In an unused 2-cell range, enter =Max(<primary data>,<secondary data>) and =Min(<primary data>,<secondary data>).
b. Add this data to the chart, change to XY or Line type as appropriate, and assign it to the primary axis.
c. Add this data again to the chart, change to XY or Line type as appropriate, and assign it to the secondary axis. Now both axes will use the same min and max to autocalculate their scales.
d. Hide these two added series by formatting to show no markers and no lines.

 

[Beezkneez]

I appreciate the time for these replies. I will work through them and see how I go.

Cheers

 

[meileetan]

Hi Jon,
Will this work on a pivot table/chart?

 

[Jon Peltier]

Will this work on a pivot table/chart?

Unfortunately, a pivot chart does not allow you to add data. This includes adding data from outside the pivot table, as well as duplicating a series already in the pivot chart.