Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 22, 2026

Chart from dynamic array challenge

Hi

(Excel 365 v2601 b19628.20132 Current Channel / Windows 11 25H2)

Initial post edited (& cross posted here on Jan 29, 2026) after further investigations

In B6 below an array that dynamically resizes according to the 'START Year' & 'TOPN Cat' variables. The Chart is setup as follow:

  • Select an empty cell > Insert 2-D Line chart
  • Right-click > Select Data… > Chart data range > Select the Serie names & Values (C6:G12)
  • Click Edit under Horizontal (Category) Axis Labels > Select the range with the Years (B7:B12)

Check of the Chart data range:

Changing 'START Year' works no problem: the Chart data range & Horizonal Axis Label range are properly updated

Changing 'TOPN Cat' (the array resizes horizontally) screws up the chart:

The Chart data range is properly updated but the Series & Axis Label ranges don't update accordingly

Q: Am I doing something wrong, facing a limitation or is this something else?
Tried to attach the sample file 3 times... it's available at: Dynamic_Chart_Challenge.xlsx

Thanks & any question let me know
Lz.

 

 

 

 

Charting
excel
office 365

2 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 01, 2026

    Pivot & Chart Categories sorted as in the TOPN array:

    • Don't unpivot the TOPN array
    • Get its headers (Categories) instead
    • Match them in the Source Table
    • Filter the Source Table with the above array & the Year filter array

    The PivotTable is the same (Sum of Values) + Show Values As: % of Row Total

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 01, 2026

    Possible workaround, once the TOPN array has been calc.:

    • Unpivot it
    • Insert a PivotTable where the data source is the above array
    • Insert a PivotChart based on the PivotTable

    Notes

    1. Until the Pivot Table Auto Refresh functionality is generally available a Data > Refresh All is required to update the Chart
    2. On this simplified scenario - with the above variables - we can see the Categories on the Chart Legend are not ordered the same as on TOPN array. Reason:
    • The %ages are calc. with PIVOTBY/PERCENTOF relative to Row Totals - as asked by the user
    • Using these %ages as PivotTable data source, the only thing we can do to reflect the appropriate numbers is to Sum the Values
    • The sorting of the PivotTable is then relative to Column Totals. And unfortunately, the More Sort Options > Sort By > Values in selected row option requires that the row belongs to be PivotTable

    3. To get Pivot & Chart Categories sorted as in the TOPN array the PivotTable data source values must be the Source values, not the %ages - covered in next post...

    Call @ Insiders

    Could a couple of you already having the Pivot Table Auto Refresh functionality confirm this works w/o a Refresh All please? + Thanks

Resources