This trips up FP&A analysts constantly because the 2 features that make Excel 365 genuinely useful for multi-tab models - structured Table references and dynamic array formulas - can't coexist in the same cell.
Why Microsoft Blocks Spilled Array Formulas in Tables
Excel Tables are fixed-schema objects. Each column has a name, a formula, and a defined width. The Table engine assumes every cell in a column shares the same formula and auto-fills down to match the row count.
Dynamic array formulas work on the opposite principle. They need to spill results into adjacent cells - as many as needed at runtime. A FILTER that returns 47 rows today might return 83 rows tomorrow. The Table engine can't accommodate that: it doesn't know where the spilled range ends, and it can't safely shift column data to make room.
Microsoft introduced the dynamic array engine in Excel 365 in 2018. The two systems were built at different times with incompatible assumptions about how cells relate to one another. The limitation is fundamental to how both features are implemented internally, not an oversight waiting to be patched.
According to Microsoft's official support documentation on dynamic array formulas and spilled array behavior (support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531, verified June 2026): "Spilled array formulas are not supported in Excel tables."
Which Formulas Still Work (and Which Spilled Array Formulas Don't)
Not every formula causes the conflict. The issue is specifically formulas that can return variable-length results.
| Formula | Works Inside Table? | Notes |
|---|---|---|
| SUMIFS, COUNTIFS, AVERAGEIFS | Yes | Returns single value |
| VLOOKUP, INDEX/MATCH | Yes | Returns single value |
| IFERROR, IF, IFS | Yes | Returns single value |
| XLOOKUP (single result) | Yes | When match returns one row |
| FILTER | No | Spills variable row count |
| UNIQUE | No | Variable result count |
| SORT / SORTBY | No | Spills sorted array |
| SEQUENCE | No | Spills numeric array |
| XLOOKUP (multiple columns) | No | Spills across columns |
| LET (with spilling inner formula) | No | Inherits spill behavior |
For a board pack model pulling from 5,000 rows of transaction data, the most painful loss is FILTER. The pattern of filtering a full dataset down to a subset and having that subset auto-resize is exactly what dynamic arrays were designed for - and exactly what Tables block.
4 Workarounds for Finance Models
You have 4 real options. None are perfect.
1. Move the formula outside the Table. Put your FILTER or UNIQUE result in a range adjacent to, but outside of, the Table. You lose Table auto-expand on that output zone, but you get spill back. Reference the source with a full-column cross-tab formula:
=FILTER('Transactions'!$A:$F,
'Transactions'!$D:$D=Assumptions!$B$3,
"No data")
2. Use the @ operator to collapse to a single value. =@SORT(...) or =@FILTER(...) returns just the first match instead of spilling. Useful for lookup columns inside Tables, useless when you need the full result set.
3. Convert the output range to a plain range, not a Table. If the target range doesn't need to be a Table, remove the Table formatting. You lose Table1[Column] structured references but regain spill.
4. Pre-process with Power Query. Load and filter your source data before it hits the sheet, so the Table receives a clean, pre-filtered result that doesn't require dynamic formulas. Overkill for a quick analysis; the right call for a production model that runs every month.
For a $500K ARR company's quarterly board pack, option 1 is usually the fastest path: keep the structured Tables for input assumptions and calculation columns, and put dynamic summary outputs in a separate output zone on the same tab.
How Google Sheets Handles This Differently
Google Sheets doesn't have this conflict. Its native array behavior - ARRAYFORMULA, FILTER, UNIQUE - has always coexisted with whatever range contains it. There's no ListObject equivalent enforcing column-level schema constraints.
If you're building in Google Sheets and want to automate the filtering and summarization work across a multi-tab model, ModelMonkey wires up to your data sources and writes the formulas directly into your sheet.