What You’ll Learn
- ✓How the Excel 365 spill engine works and why it changes everything
- ✓The syntax and real-world use of FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE
- ✓How to nest dynamic array functions for powerful live reporting
- ✓How to use the # spill range operator to build self-updating dropdowns
- ✓How to diagnose and fix the #SPILL! error step by step
Dynamic array functions in Excel are the biggest change to how Excel actually calculates things in over twenty years — and most people are still completely unaware they exist.
Here is a scenario you have probably lived through. You have a 1,500-row sales table. Your manager wants to see only the North region transactions, sorted by revenue, by 9am Monday. So you open the file, apply AutoFilter, copy the results to a new sheet, sort them, format the headers, and email it over. Then on Tuesday the data updates and you do the whole thing again. That cycle ends today.
Dynamic array functions in Excel let you write a single formula that filters, sorts, and deduplicates your data — and the output updates itself every time the source data changes. Before we go further, if you have been relying heavily on VLOOKUP for your data lookups, check out the XplorExcel lesson on XLOOKUP in Excel. A lot of what VLOOKUP used to handle can now be done more cleanly with dynamic arrays, and it helps to see both worlds side by side.
What Are Dynamic Array Functions in Excel?
To understand why dynamic array functions matter, you need to know what Excel used to do — and why it was painful. Before Excel 365, if you wanted a formula to return multiple results, you had to do something called a legacy array formula. You would select the exact output range, type your formula, and confirm it with CTRL+SHIFT+ENTER instead of just Enter. If you guessed the output range wrong, you got either blank cells or missing data. It was brittle, hard to explain to a colleague, and if anyone accidentally pressed Enter instead of CTRL+SHIFT+ENTER while editing it, the formula broke silently. Excel 365 scrapped all of that.
How Excel 365 Changed the Calculation Engine
Microsoft rebuilt the Excel calculation engine to support what is called implicit spill behavior. In plain terms: when a formula produces multiple results, Excel now figures out how many cells it needs and fills them automatically. You type the formula in one cell, press Enter like any normal formula, and Excel handles the rest.
This is the engine that powers every dynamic array function. It is always live. The output grows when your data grows. It shrinks when rows are removed. You never touch the formula again unless the logic needs to change.
Spill Behavior — What It Means and Why It Matters
Spill behavior is the foundation everything else is built on. Think of it like a projector. You press play once, and it fills the screen with whatever the source contains. You do not manually resize the screen for every frame. The content fills the space it needs.
The cell where you enter the formula is called the spill anchor. The cells that receive the output are called the spill range. Excel decides how big that range needs to be based on the data — not you. If anything is sitting in one of those output cells, Excel cannot write there and throws a #SPILL! error. We cover exactly how to fix that below.
Understanding the Excel Spill Range
What Is a Spill Range?
A spill range is the group of cells a dynamic array formula fills automatically. It always starts at the spill anchor — the top-left cell of the output — and expands from there. If your FILTER formula finds 23 matching rows today, the spill range is 23 rows tall. If tomorrow the data has 31 matches, the spill range becomes 31 rows. You do not update anything. It just happens.
Using the # Operator to Reference a Spill Range
Here’s the thing — most tutorials skip this entirely, and it is genuinely one of the most useful features in modern Excel. You can reference an entire spill range using the hash symbol — the spill range operator. If your formula is in cell D2 and it spills results into D2 through D20, you reference that whole dynamic range by typing D2# in any other formula. Excel knows to include however many rows the spill currently contains.
This is incredibly useful for Data Validation dropdowns. Instead of hardcoding a source range like D2:D50, you type D2# as the dropdown source — the dropdown grows and shrinks automatically as the underlying UNIQUE formula updates. The XplorExcel lesson on XLOOKUP in Excel also covers related dynamic referencing techniques worth pairing with this.
What Blocks a Spill Range and How to Fix It
Three things most commonly block a spill range:
- Another value or formula already exists in one of the cells Excel needs to write into.
- The spill range overlaps a merged cell — Excel cannot write into merged cells.
- The formula sits inside an Excel Table structure, which restricts certain spill behaviors.
To fix it: click the cell showing the #SPILL! error. Excel draws a dashed blue border showing the full intended spill range and highlights the blocking cell in red. Delete or relocate whatever is in that cell, and the formula resolves on its own. If you cannot see a red highlight, click the small warning triangle next to the error cell and choose Select Obstructing Cells.
The Core Dynamic Array Functions in Excel — One by One
Excel 365 introduced six dynamic array functions. Each one eliminates a task that used to require a workaround, a helper column, or a lot of manual effort. Here they are, one at a time.
Excel FILTER Function — Extract Rows That Match a Condition
FILTER is the one you will use most often. It extracts rows from a range that meet a condition you specify, and the output updates live whenever the source data changes.
Syntax
FILTER(array, include, [if_empty])-- Single criteria=FILTER(A2:C100, B2:B100="North")-- Multi-criteria (AND logic)=FILTER(A2:C100, (B2:B100="North")*(C2:C100>5000))-- With safe empty-result fallback=FILTER(A2:C100, B2:B100="North", "No results found")
💡 Pro Tip
Always include the third argument in FILTER. Without it, if no rows match your condition, Excel returns a #CALC! error. With it, you control what appears instead. Using "No results found" as the third argument gives you clean output every time, even when the filter comes up empty.
Excel SORT Function — Sort Any Range with a Formula
SORT returns a sorted version of any range. Your original data stays exactly where it is. You get a live sorted copy wherever the formula lives.
Syntax
SORT(array, [sort_index], [sort_order], [by_col])-- Sort column A alphabetically=SORT(A2:A100)-- Sort two-column table by column 2, highest first=SORT(A2:B100, 2, -1)
⚠️ Common Mistake
A lot of people type the sort_index as a column letter like B instead of a number. SORT does not accept column letters — only numbers. Column A of your array is 1, column B is 2, and so on. If you get an error, check this first.
Excel SORTBY Function — Sort by a Column Not in Your Output
SORTBY is SORT’s more flexible cousin. It lets you sort a range based on values in a separate column that does not even appear in the output. Trust me on this — once you have needed to sort a name list by a score without showing the score, you will appreciate this function immediately.
Syntax
SORTBY(array, by_array1, [sort_order1], ...)-- Show names sorted by scores, highest first (scores not shown)=SORTBY(A2:A100, B2:B100, -1)
Excel UNIQUE Function — Remove Duplicates Dynamically
UNIQUE returns a deduplicated list from any range. No Power Query. No Remove Duplicates button. No manual refresh when new values are added.
Syntax
UNIQUE(array, [by_col], [exactly_once])-- Unique customer list=UNIQUE(A2:A500)-- Names that appear exactly once only=UNIQUE(A2:A500, FALSE, TRUE)
SEQUENCE Function — Generate Number or Date Series Automatically
SEQUENCE generates a list of sequential numbers. On its own it is handy. Combined with DATE or EDATE, it becomes a tool for generating dynamic date headers that never need updating.
Syntax
SEQUENCE(rows, [columns], [start], [step])-- Numbers 1 to 10=SEQUENCE(10)-- 12 month-start dates for 2025 across columns=DATE(2025, SEQUENCE(1, 12), 1)
RANDARRAY — Dynamic Random Number Arrays
RANDARRAY fills a range with random numbers. You control the dimensions, the minimum and maximum values, and whether you want whole numbers or decimals.
Syntax
RANDARRAY([rows], [columns], [min], [max], [integer])-- 5x3 grid of whole numbers between 1 and 100=RANDARRAY(5, 3, 1, 100, TRUE)
Nesting Dynamic Array Functions for Real Power
This is where things get genuinely exciting. The real power of dynamic array functions in Excel is not any single function — it is what happens when you start combining them. Each function accepts another function as its input, and the whole chain stays live.
SORT + FILTER — Live Sorted Filtered List
-- North region, sorted by column 3 descending=SORT(FILTER(A2:C100, B2:B100="North"), 3, -1)FILTER runs first and returns only the rows matching “North”. SORT then sorts that result by column 3 in descending order. The entire thing updates the moment the source data changes.
UNIQUE + FILTER — Deduplicated Results from a Filtered Subset
-- Unique product names for North region only=UNIQUE(FILTER(C2:C100, B2:B100="North"))FILTER narrows the data to North rows first. UNIQUE then removes any duplicate product names from that filtered subset. Two functions, one clean result.
SEQUENCE + DATE — Auto-Generated Monthly Report Headers
-- Jan 1 through Dec 1, 2025 across 12 columns=DATE(2025, SEQUENCE(1, 12), 1)Format the cells as MMMM for month names. Your entire header row is now dynamic. Add FILTER formulas for each month’s figures below, and your report structure never breaks again.
Real-World Use Cases
Use Case 1 — Automated Regional Sales View
A sales manager has a 2,000-row transaction table. Every morning: apply AutoFilter, select region, copy to new sheet, sort by revenue, format, send. At least five manual steps, repeated every single day. With dynamic array functions in Excel, one formula replaces all of it:
=SORT(FILTER(Table1, Table1[Region]="North"), 3, -1)Use Case 2 — Self-Updating Dropdown Source List
An operations analyst needs a Data Validation dropdown that always reflects the current unique customer names. When a new customer is added to the source list, the dropdown updates automatically — no one has to touch it.
- In an empty column, enter:
=UNIQUE(A2:A500) - Click the cell where you want the dropdown and open Data Validation.
- Set the source to the spill anchor followed by #, for example
D2#.
Use Case 3 — Dynamic Monthly Finance Report
A finance analyst builds a 12-column report with one column per month. With SEQUENCE and FILTER working together, the headers generate themselves using =DATE(2025, SEQUENCE(1, 12), 1) and the figures pull automatically. The report structure is self-maintaining and never needs manual repair after data changes.
Troubleshooting the #SPILL! Error
Common Causes of #SPILL! in Dynamic Arrays
- A value or formula already exists in one or more of the cells Excel needs for the output.
- The spill range overlaps a merged cell.
- The formula is inside an Excel Table, which restricts certain spill behaviors.
- The spill range would extend beyond the edge of the worksheet.
Step-by-Step: How to Diagnose and Clear a #SPILL! Error
- Click the cell showing the #SPILL! error.
- Look for the dashed blue border — this outlines the full intended spill range.
- The blocking cell is highlighted in red. Note its location.
- Go to that cell and delete its contents, or move the data to a different location.
- Press Enter or click away. The formula resolves automatically.
Dynamic Array Excel 365 — Compatibility and Version Notes
Which Excel Versions Support Dynamic Arrays?
Available in: Microsoft 365 (all plans, Windows & Mac), Excel 2021, Excel for the web.
Not available in: Excel 2019, Excel 2016, Excel 2013 or earlier. If you share files with colleagues on older versions, they see results as static values and cannot recalculate them.
Alternatives for Excel 2019, 2016, and Google Sheets
- For FILTER: use INDEX and MATCH together with IFERROR, confirmed with CTRL+SHIFT+ENTER.
- For UNIQUE: use the Data tab’s Advanced Filter to extract unique values to another range.
- For SORT: use a helper column and the built-in sort dialog.
Google Sheets has its own FILTER, SORT, and UNIQUE functions that work similarly, with minor syntax differences for multi-criteria logic.
Dynamic Array Functions in Excel — Quick Reference Table
| Function | What It Does | Available In |
|---|---|---|
| FILTER | Returns rows matching one or more criteria | 365, 2021, Web |
| SORT | Sorts a range by a specified column | 365, 2021, Web |
| SORTBY | Sorts by a column outside the output range | 365, 2021, Web |
| UNIQUE | Removes duplicates from a list | 365, 2021, Web |
| SEQUENCE | Generates sequential numbers or dates | 365, 2021, Web |
| RANDARRAY | Fills a range with random numbers | 365, 2021, Web |
Try It Yourself
🧪 Practice Exercise
Create a worksheet with three columns: Name (A), Region (B), Sales Amount (C). Add at least 20 rows using three regions — North, South, East — and repeat some names.
- In cell E2, enter:
=SORT(FILTER(A2:C21, B2:B21="North"), 3, -1)
This returns all North rows sorted by sales amount, highest first. - In cell H2, enter:
=UNIQUE(FILTER(A2:A21, B2:B21="North"))
This returns unique names from North region rows only. - Change one South or East entry to North in your original data.
- Watch both formula outputs update instantly — no refresh, no copy-paste, no manual sort.
Frequently Asked Questions
Can I use dynamic array functions in Excel Tables?
Partially. You can use data inside an Excel Table as the source range for a dynamic array formula. What you cannot do is enter a dynamic array formula inside a Table cell and have it spill outside the Table boundaries. The fix: enter the formula in a regular cell outside the Table and reference the Table columns as your array argument. For example: =FILTER(Table1[Name], Table1[Region]="North")
Do dynamic arrays slow down Excel?
On very large datasets — hundreds of thousands of rows — yes, recalculation can slow down noticeably. For most everyday datasets under 50,000 rows, the performance impact is negligible. Using Excel Tables as your source range helps, as structured table references calculate more efficiently than plain cell ranges.
Why is my spill range returning only one value?
This almost always means the condition in your formula is written as a single cell reference rather than a range. If your array is A2:C100, your include argument must span the same number of rows — for example B2:B100="North" — not just B2="North". One cell evaluates to one result. A range evaluates to a column of results that FILTER can work with.
📚 Further Reading
The official Microsoft reference for dynamic array functions and spill range behavior in Excel 365.
Formula-by-formula examples with clear plain-English explanations and edge-case handling for FILTER.
Dynamic array functions in Excel are not just a shortcut — they represent a fundamentally different approach to building spreadsheets, one where formulas adapt to data rather than the other way around. Start with FILTER and SORT on data you already have. Get comfortable with those two, then layer in UNIQUE. Once nesting feels natural, you will find yourself reaching for these tools constantly. See you in Lesson 29.
← Previous Lesson