2018 vs Today: The calculation engine rewrite that killed Ctrl+Shift+Enter
Before 2018, getting a unique list out of a column meant one of three things: a pivot table you'd have to refresh, a helper column full of COUNTIF logic you'd have to explain to everyone who opened the file, or a macro that someone else wrote and you weren't touching.
SORT was a manual step. You'd get your data, sort it in place, and then the moment the source changed, your sorted output was already wrong. FILTER didn't exist as a worksheet function at all — you used AutoFilter, which isn't a formula, it's a UI interaction, which meant you couldn't build anything on top of it.
The 2018 calculation engine rewrite changed all of this. And most tutorials about FILTER, SORT, and UNIQUE completely miss what actually changed.
What the 2018 rewrite actually did
The old Excel calculation engine was built around a single-cell-in, single-cell-out model. Every formula produced one value, which lived in one cell. Arrays existed (hence Ctrl+Shift+Enter), but they were awkward, fragile, and hidden from most users because the engine was fundamentally uncomfortable with them.
The rewrite introduced dynamic array awareness as a first-class concept. A formula can now return an array of values, and the engine automatically allocates the cells it needs — expanding down, across, or both. That range is called the spill range. You write the formula in one cell; Excel figures out how many cells the result needs and fills them in.
This isn't just FILTER, SORT, and UNIQUE getting new behaviour. It's the entire engine changing what a formula is allowed to be.
The consequence: every function in Excel now has to be understood in terms of what it returns — a single value, or an array. And when functions return arrays, you can pass those arrays directly to other functions. No more intermediate columns. No more helper ranges. The formula is the output.
The mental model: outputs, not cells
The instinct most people bring to Excel formulas is: a formula lives in a cell and produces a value for that cell.
The right instinct for dynamic arrays is: a formula produces a result set, and the spill range is just where Excel renders it.
That shift matters when you start stacking functions. FILTER returns an array. SORT takes an array. So:
=SORT(FILTER(A2:D500, C2:C500="Overdue"), 4, -1)
Read this right to left: FILTER produces a filtered array → SORT receives that array and sorts it → the result spills into as many rows as the filter found. No intermediate range. The whole thing updates the moment A2:D500 changes.
This is what most tutorials miss: they treat FILTER and SORT as replacements for AutoFilter and manual sorting. They're not. They're functions that operate on sets, and the magic is that those sets flow directly between functions.
Real example: a live aging debtors list
Say you have a table of invoices — columns for customer, invoice date, amount, and days outstanding. You want a live report showing only overdue invoices (>30 days), sorted by days outstanding descending, with duplicates in customer name collapsed into a unique list for the summary view.
Step 1 — Filter to overdue only:
=FILTER(InvoiceTable, InvoiceTable[Days Outstanding]>30, "No overdue invoices")
The third argument is what to return if nothing matches. Skip it and you get a #CALC! error when the list is empty. Put something sensible there.
Step 2 — Sort the filtered result:
=SORT( FILTER(InvoiceTable, InvoiceTable[Days Outstanding]>30, "No overdue invoices"), 4, -- sort by column 4 (Days Outstanding) -1 -- descending )
The result spills. It stays current. No refresh needed, no pivot table, no macro.
Step 3 — Unique customer list for the summary:
=UNIQUE(FILTER(InvoiceTable[Customer], InvoiceTable[Days Outstanding]>30))
UNIQUE deduplicates the filtered customer column. Pass that to SORT if you want alphabetical:
=SORT(UNIQUE(FILTER(InvoiceTable[Customer], InvoiceTable[Days Outstanding]>30)))
Evaluation sequence when stacking functions
Excel evaluates innermost-to-outermost, same as any nested formula. With dynamic arrays, each layer receives and returns an array:
- FILTER evaluates first — it scans the include array (your condition), returns all rows where the condition is TRUE
- SORT receives that array — it doesn't see the original table, only what FILTER gave it. Column indices in SORT are relative to FILTER's output, not the original range
- UNIQUE (if present) deduplicates whatever it receives before the outer function sees it
The common mistake: using column index 4 in SORT when FILTER returned only 3 columns. SORT's column index is relative to the array it received. If FILTER is returning a subset of columns, count from that subset.
Performance at scale: which approach calculates fastest on 100,000 rows?
At 100k rows, calculation time becomes noticeable. Here's what matters:
FILTER is faster than COUNTIFS-based extraction approaches because it operates as a single pass over the data. The legacy helper-column pattern (COUNTIF + IFERROR + INDEX) recalculates multiple times per row.
SORT inside FILTER adds overhead — sorting is O(n log n) and happens on every recalculation. If your sort column doesn't change often, consider whether SORT is doing useful work or just adding time.
UNIQUE is relatively cheap — it's essentially a deduplication pass over its input.
For 100k rows with a tight FILTER condition (few rows survive), stacking SORT(FILTER(...)) is fine. For 100k rows where most rows survive the filter, sorting a near-full table on every change will feel slow. In that case, sort the source table once and let FILTER preserve order.
Where dynamic arrays break down
Shared workbooks (legacy co-authoring mode): Spill ranges don't work in the old shared workbook format. You'll get errors. If you're in modern co-authoring via OneDrive/SharePoint with xlsx format, you're fine — that's a different code path. The legacy "Share Workbook" feature via Review tab is the one to avoid.
Structured table columns: You can't spill into a column that's already part of an Excel Table (ListObject). Spill ranges need free space. If the cell below or to the right of your formula is occupied, you get #SPILL!.
INDIRECT and volatile functions: Wrapping a dynamic array formula in INDIRECT makes it recalculate on every single change in the workbook. At scale, this becomes painful quickly.
Why does #SPILL! happen and how do you fix it instantly
#SPILL! means the cells the formula wants to expand into aren't empty. Excel can't evict content to make room for your output — it just errors instead.
Fix:
- Click the formula cell — Excel will show a dotted border around the spill range it wants to use
- Find what's in that range — often it's a space character, a zero, or leftover content from a deleted range
- Clear those cells
The fastest diagnostic: =IFERROR(FILTER(...), "blocked") won't help with #SPILL! — the error happens before the function even runs. You have to clear the space.
One scenario that catches people: a formula that used to return 10 rows now returns 50, but rows 11–50 have content. The formula worked before; now it errors. Check whether something was pasted into those rows.
Frequently Asked Questions
Can I reference just part of a spill range?
Yes — use the spill operator #. If your FILTER formula is in A2, A2# refers to the entire spill range dynamically. Pass it to another function and it adjusts as the spill range grows or shrinks.
Can FILTER return multiple separate columns that aren't adjacent?
Not directly. FILTER takes a contiguous range. If you need non-adjacent columns, CHOOSE can reorder: FILTER(CHOOSE({1,2,4}, A:A, B:B, D:D), ...) — though this gets messy fast and a structured table reference is usually cleaner.
Does UNIQUE work on multiple columns? Yes. Pass a multi-column range and it deduplicates on the combination of all columns, not just the first one.
What replaced Ctrl+Shift+Enter? Nothing. You don't need it anymore. Just press Enter. If you're maintaining old CSE formulas, they still work — Excel just evaluates them differently internally now. But for new work, stop using CSE.
Audit your legacy templates and delete the helper columns
If you built your debtors reports, sales summaries, or lookup tables before 2019, they almost certainly have helper columns. A COUNTIF to flag duplicates. An IF to mark rows for extraction. A sorted copy of a table maintained manually.
Open one. Find the helper columns. Ask whether a single FILTER or SORT formula replaces what they're doing. Nine times out of ten, it does — in one cell, without the maintenance overhead, and without the risk of the helper range going stale.
The templates built with the old mental model aren't wrong. They're just more expensive than they need to be now.