What are dynamic arrays in Excel? Definition and how spilling works

Microsoft's wording is narrower and worth keeping: "Formulas that can return arrays of variable size are called dynamic array formulas." Variable size is the load-bearing part, and everything else — the hash operator, the #SPILL! error, the @ character in old formulas — follows from it.

Spilling

Microsoft's word for the behaviour is deliberately physical: "Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells." Type =UNIQUE(A2:A500) into E5 and the list of distinct values appears down column E from E5. Exceljet describes the same thing from the user's side — in modern Excel, "formulas that return multiple values will 'spill' these values directly onto the worksheet."

Only the top cell holds a formula. Click E6 and the formula bar shows the formula greyed out; you cannot edit it there, and deleting E5 removes the whole block. Microsoft's term for a formula in this state is a spilled array formula. Spilling also needs empty cells, and when it does not get them the formula refuses rather than overwriting anything: "When spilling is blocked by other data, you'll see a #SPILL error. Once you make room for the spill range, the formula will automatically spill."

Referring to the whole result

Because the block resizes, its address is not stable — which is why there is an operator for it. Exceljet: "use a hash symbol (#) after the first cell in the range. For example, to reference the results from the UNIQUE function above use: =E5#". =COUNTA(E5#) counts however many values are there this month and next. A hard-coded E5:E40 does not survive the source growing to forty-one rows.

What replaced Ctrl+Shift+Enter

Before dynamic arrays, returning several values meant selecting the output range first and committing the formula with Ctrl+Shift+Enter — a CSE array formula. Microsoft keeps those working: "CSE array formulas are retained for compatibility reasons." But it is explicit that the ceremony is over: with dynamic arrays "There's no need to Press Ctrl+Shift+Enter."

The differences run deeper than the keystroke.

The functions built for this model are the ones Microsoft groups together: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE. They are why the feature changed how models are built rather than just how array formulas are typed — a filtered, sorted, de-duplicated list is one formula instead of a helper-column pipeline. Exceljet records the version boundary to check before sending such a workbook to someone else: "Dynamic arrays and the new functions below are only available Excel 365 and Excel 2021. Excel 2019 and earlier do not offer dynamic array formulas."

Implicit intersection and the @ character

Open an old workbook in modern Excel and you may find @ characters that nobody typed. Exceljet explains what they do: "The @ character enables a behavior known as 'implicit intersection'. Implicit intersection is a logical process where many values are reduced to one value." Excel inserts it to preserve the pre-dynamic-array behaviour of a formula that would otherwise start spilling — it is a compatibility marker, not a mistake, and removing one is how you deliberately let an old formula spill.

Excel tables and dynamic arrays

One boundary catches people who use both features. Microsoft: "Spilled array formulas are not supported in Excel tables themselves, so you should place them in the grid outside of the Table." A table column already has a rule for what every cell in it contains. Pointing a dynamic array formula at a table as its source is fine; putting the formula inside one is not.

The Google Sheets side

Sheets arrived at similar behaviour by a different route, and the vocabulary does not carry over. Arrays there are written with curly brackets — commas separating columns, semicolons starting new rows, as in ={"A",1;"B",2;"C",3} — and Ben Collins notes that "array literals create a formula that spills into the adjacent cell" without any special key combination. The other half of the story is ARRAYFORMULA, the wrapper that makes an ordinary function operate over a whole range at once, as in =ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE)).

The practical consequence is that a formula relying on Excel's implicit-array behaviour usually needs an explicit ARRAYFORMULA wrapper to do the same job in Sheets.

How Formula Foundry handles this

That last problem is the one Formula Foundry actually solves. Set the editor's dialect to Microsoft Excel, paste an Excel formula, and the Excel → Sheets translator rewrites it into working Sheets syntax: it strips the implicit-intersection @ that modern Excel added for compatibility, wraps implicit-array operations in ARRAYFORMULA(), collapses IF(ISERROR()) into IFERROR, remaps function names, and alerts you when a construct cannot be transferred rather than producing something that looks fine and returns the wrong shape. It is in beta, and that last alert is the honest part of it.

Everything else the add-on does for array formulas is about reading them. A FILTER wrapped in a SORT wrapped in a UNIQUE is three ranges and three sets of brackets on one line in the formula bar; in the sidebar it opens across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted. The visual builder renders the same formula as a form with every argument named, and the AI assistant explains an existing one step by step.

Be clear about the boundary: Formula Foundry does not evaluate your workbook. It parses, formats, translates and explains — it cannot show you the spill range, cannot tell you a formula will return #SPILL! because a cell three rows down is not empty, and does not know how many rows your source data has. Excel and Sheets own that. The Excel to Sheets translation walkthrough shows the conversion on a real formula.

FAQ