Hey there! I totally get this frustration—dynamic filtered data in pivot tables makes static axis settings useless real quick. Let’s walk through two solid solutions to get your horizontal axis cross point automatically syncing with the Y-axis midpoint whenever you use those slicers.

Method 1: No VBA (Using Named Ranges & SUBTOTAL) #

This is great if you want to avoid macros and stick to built-in Excel features:

1. Set up dynamic named ranges

   • Go to the Formulas tab → click Name Manager → New.
   • Create your first named range:
     • Name: Y_Axis_Max
     • Refers to: =SUBTOTAL(104, [YourPivotTableYValueRange])
       Replace [YourPivotTableYValueRange] with the actual range of your Y-values in the pivot table (e.g., PivotData!$C$2:$C$200—make sure to exclude headers and empty rows). The 104 tells SUBTOTAL to calculate the max value only for visible rows (perfect for slicer filters!).
   • Create a second named range for the midpoint:
     • Name: Y_Axis_Midpoint
     • Refers to: =Y_Axis_Max/2

2. Link the chart axes to these ranges

   • Right-click your Y-axis → select Format Axis.
   • Under Axis Options:
     • For Maximum, select Fixed then type =Y_Axis_Max (yes, you can directly reference the named range here!).
     • Scroll down to Horizontal Axis Crosses, select Axis value then type =Y_Axis_Midpoint.

Now every time you use a slicer to filter data, the SUBTOTAL function will recalculate the visible Y-axis max, the midpoint updates automatically, and your chart adjusts instantly.

Method 2: VBA for Fully Automated Updates #

If you want the process to run entirely in the background (no manual tweaks needed), use a macro:

1. Create the core macro
   • Press Alt + F11 to open the VBA Editor.
   • Right-click your workbook in the Project Explorer → Insert → Module.
   • Paste this code (adjust the chart name and pivot table references to match yours):

Sub UpdateAxisCrossPoint()
    Dim targetChart As ChartObject
    Dim yAxis As Axis
    Dim visibleMaxY As Double
    
    ' Replace "QuadrantBubbleChart" with your chart's actual name
    Set targetChart = ActiveSheet.ChartObjects("QuadrantBubbleChart")
    Set yAxis = targetChart.Chart.Axes(xlValue)
    
    ' Get max Y-value from visible data (ignores hidden rows from slicers)
    visibleMaxY = Application.WorksheetFunction.Subtotal(104, _
                  targetChart.Chart.SeriesCollection(1).Values)
    
    ' Update Y-axis max and cross point
    yAxis.MaximumScale = visibleMaxY
    yAxis.CrossesAt = visibleMaxY / 2
End Sub

2. Trigger the macro when slicers are used
   • Option A: Assign the macro directly to slicers
     Right-click a slicer → Assign Macro → select UpdateAxisCrossPoint → repeat for all 4 slicers.
   • Option B: Use a pivot table update event (more seamless)
     Double-click the worksheet with your pivot table in the Project Explorer, then paste this code (replace YourPivotTableName with your pivot table's name):

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target.Name = "YourPivotTableName" Then
        Call UpdateAxisCrossPoint
    End If
End Sub

This will run the macro automatically every time the pivot table updates (i.e., when you use a slicer).

Quick Notes #

• If your bubble chart has multiple data series, adjust the SUBTOTAL range in either method to include all Y-value ranges, or use MAX(SUBTOTAL(104, Range1), SUBTOTAL(104, Range2)) to grab the highest visible max across all series.
• When using SUBTOTAL, make sure your range doesn’t include empty cells or headers—this can throw off the calculation.

内容的提问来源于stack exchange，提问作者user402253