Chart & Visualization Issues

Why Dynamic Arrays May Not Integrate Directly with Excel Charts

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 869 views

Question details

The user wants to understand why Microsoft 365 dynamic array formulas do not always appear as a dedicated chart integration feature in every Excel build or update channel, and how to make charts respond to spilled array results.

Why Dynamic Arrays May Not Integrate Directly with Excel Charts
Product
Microsoft Excel (Microsoft 365)
Device & OS
not provided
Scenario
A user is working with dynamic array formulas in Microsoft 365 and wants to connect the spilled results to an Excel chart, but the direct chart integration option is not visible in their current build or update channel.
Observed behavior
The user does not see a specific new dynamic array chart integration option; charts do not automatically reference the spilled array unless a dynamic named range or helper range is used.
Before you start

Before you create the chart, confirm your Excel version and update channel under File > Account > Update Options, and make sure the dynamic array formula has finished spilling into a visible range. For example, =SORT(A2:A10) returns a spill range that you can reference with the # operator.

Solution 1Recommended

Create a Named Range That Refers to the Spilled Array with the Spill Operator

Use this when your dynamic array formula is already spilling results and you want the chart to update automatically as the array expands or contracts.

Excel does not expose a separate 'dynamic chart' toggle in every build or update channel. Dynamic chart behavior is achieved by pointing the chart at a dynamic range, and a named range that uses the spill operator (#) is the most direct method.

1
Open Name Manager from the Formulas tab

Click any cell inside the spilled array, go to Formulas > Name Manager, and select New to create a named range.

2
Define the name with the spill operator

In the New Name dialog, enter a name such as DynamicChartRange, set Scope to the workbook or sheet, and in the Refers to box type =Sheet1!$A$2#. Replace Sheet1!$A$2 with the top-left cell of your spill range, then click OK.

3
Point the chart to the named range

Select the chart, right-click and choose Select Data. Edit the series values to =Sheet1!DynamicChartRange (or ='Sheet1'!DynamicChartRange if needed), then click OK. The chart will now use the spilled array.

Create a Named Range That Refers to the Spilled Array with the Spill Operator
Dynamic chart range created: The chart updates automatically whenever the spilled array grows or shrinks because the named range references the full spill range.
Free Microsoft Office alternative

Use WPS Office Spreadsheets for Dynamic Array and Chart Work

WPS Office is a free, lightweight Office alternative that opens and saves .xlsx workbooks, offers a familiar ribbon interface, and supports named ranges and chart data sources in WPS Spreadsheets, so you can manage Excel-compatible dynamic array work without Microsoft 365 update-channel limitations.

Create named ranges and connect them to charts to manage dynamic data without update-channel dependencyOpens and saves .xlsx workbooks to keep Excel formulas, charts, and formatting compatibleFree, lightweight desktop app with a familiar ribbon UI for a seamless migration from ExcelFast startup and low system resource use compared with Microsoft 365 desktop apps
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I find a Dynamic Arrays with Charts option in my Excel build?

Microsoft 365 delivers dynamic arrays through functions and spill ranges, not always as a separate chart feature. Use a named range with the spill operator (#) in Name Manager to make the chart dynamic, and check File > Account > Update Options to ensure Excel is updated.

How do I reference a spilled array in a chart?

Create a named range in Formulas > Name Manager with a Refers to value like =Sheet1!$A$2#, then use that name as the chart's series values in Select Data.

Will the chart update automatically when the dynamic array expands?

Yes, if the chart uses a named range that refers to the spill range with the # operator, it updates when the spilled array grows or shrinks.

Can I use an Excel Table with dynamic array results instead of a named range?

Yes. Put the dynamic array formula in a helper range and convert it to an Excel Table via Insert > Table, then use the table as the chart data source, or reference the spill range from helper cells.

Does WPS Office support Excel dynamic arrays and chart ranges?

WPS Office Spreadsheets opens .xlsx dynamic array workbooks and supports named ranges and chart data sources, making it a free alternative for Excel-compatible spreadsheet work. Check your WPS version for the latest dynamic array functions.