Dynamic Arrays — the complete guide
In 2020, Microsoft shipped the biggest change to Excel's calculation engine in 30 years: dynamic arrays. One formula in one cell can now spill hundreds of results. Complex operations that used to need pivot tables, helper columns, or Ctrl+Shift+Enter arrays now fit in a single line. This guide covers all 15 dynamic array functions, the spill mechanism, nested patterns, and every error you'll encounter.
📑 What's in this guide
- What dynamic arrays actually changed
- The spill mechanism — anchors, ranges, and blue borders
- The 15 dynamic array functions
- FILTER — return rows matching criteria
- UNIQUE — distinct values in one formula
- SORT and SORTBY — arrays in any order
- SEQUENCE and RANDARRAY — generation
- TAKE, DROP, CHOOSEROWS, CHOOSECOLS — slice and pick
- WRAPROWS, WRAPCOLS, TOROW, TOCOL, EXPAND — reshape
- Nested patterns — combining DA functions
- #SPILL! error — causes and fixes
- #CALC! error — causes and fixes
- The @ operator and implicit intersection
- Legacy CSE arrays vs modern dynamic arrays
- Compatibility — Excel 365 vs 2021 vs older
- Frequently asked questions
What dynamic arrays actually changed
Before 2020, if you wanted a formula to return more than one value in Excel, you had to use "CSE arrays" — enter the formula with Ctrl+Shift+Enter, and Excel would show it in curly braces. You had to pre-select the exact number of cells the result would fill. Get the size wrong and results were truncated or padded with error codes. Most Excel users avoided array formulas entirely because of this friction.
Dynamic arrays replaced all of that. Now you write a formula in a single cell, press Enter, and Excel automatically spills the results into surrounding cells — as many or as few as needed. The formula lives in one cell (the anchor). All the neighboring cells are automatically populated by the spill. Change the source data and the spill resizes automatically.
This unlocks patterns that were previously impossible without pivot tables, VBA, or Power Query. Get all unique values from a column? One formula. Filter transactions to a specific region and sort by amount? One formula. Generate a 10-row multiplication table? One formula. This guide walks through every dynamic array function and the combinations that make them powerful.
You'll need Excel 365 (any subscription tier) or Excel 2021 perpetual license. Dynamic arrays don't work in Excel 2019 or older — you'll see #NAME? errors trying to use FILTER, UNIQUE, SORT, or any of the other DA functions.
The spill mechanism — anchors, ranges, and blue borders
Understanding spilling is the foundation for everything else. When you type =SEQUENCE(5) in cell A1, this happens:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Three important things happen:
1. The formula lives only in the anchor cell (A1). If you click A1, you see the formula. If you click A2 through A5, the formula bar shows the formula in gray italic — you can't edit it there.
2. A subtle blue border wraps the entire spill range. This appears when any cell in the range is selected. It's Excel's visual signal that these cells are all one dynamic array.
3. If any cell in the spill range has data, the whole spill fails. You get #SPILL! in the anchor and nothing spills. Excel refuses to overwrite existing data. Clear the blocking cells and the spill happens automatically.
The spill grows and shrinks automatically. Change =SEQUENCE(5) to =SEQUENCE(10) and the spill extends to A10. Change it to =SEQUENCE(3) and A4:A5 clear automatically. You never need to manually resize the range.
You can reference the entire spill range using the anchor cell followed by #. If =SEQUENCE(10) lives in A1, then =SUM(A1#) sums the entire spill, whatever size it currently is. This is called "spill reference" and it's incredibly powerful for building formulas that adapt automatically as data grows.
The 15 dynamic array functions
Modern Excel has 15 functions that specifically use the dynamic array mechanism. They fall into four categories:
| Category | Functions | Purpose |
|---|---|---|
| Filter & Sort | FILTER, UNIQUE, SORT, SORTBY | Transform an existing array — filter rows, deduplicate, reorder |
| Generation | SEQUENCE, RANDARRAY | Build a new array from scratch — sequences of numbers, random values |
| Slice & Pick | TAKE, DROP, CHOOSEROWS, CHOOSECOLS | Select portions of an existing array — top N, skip rows, pick columns |
| Reshape | WRAPROWS, WRAPCOLS, TOROW, TOCOL, EXPAND | Change the shape of an array — 1D↔2D, flatten, pad |
Note that XLOOKUP also spills when configured to return multiple columns. Technically it's not classified as a "dynamic array function" the way FILTER is, but it uses the same spill mechanism. The same applies to array-returning functions like FREQUENCY, TRANSPOSE, and MMULT.
FILTER — return rows matching criteria
Return only the rows where a condition is TRUE
The most-used dynamic array function. Filter a range to matching rows, all in one formula that updates automatically when source data changes.
With a sales table where column A holds regions and columns B and C hold amounts and dates, return all rows where region equals "APAC":
The result spills — as many rows as match. Add a new APAC row to source data and the FILTER result grows automatically. The 3rd argument replaces the #CALC! error when no rows match.
For AND logic, multiply the criteria (both must be true = both are 1):
For OR logic, add them (either being true makes the sum ≥ 1):
Full deep dive with 10+ examples: FILTER function page.
UNIQUE — distinct values in one formula
Return distinct values from an array — Excel's answer to SQL's SELECT DISTINCT
Replaces the old Remove Duplicates dialog. UNIQUE is a live formula that updates as source data changes.
List every distinct region from a column:
The 3rd argument (exactly_once) flips the behavior: instead of "list each distinct value once", it becomes "list only values that appear exactly once":
Useful for finding data entry errors — values that should appear multiple times but only appear once.
UNIQUE also works on multi-column arrays, treating each row as a whole:
Returns unique combinations of the 3 columns. Perfect for building a dimension table from a fact table. Full details: UNIQUE function page.
SORT and SORTBY — arrays in any order
Sort an array by one or more of its own columns
Sort a 3-column range by the 3rd column, descending:
The 3 means "sort by column 3 of the array", and -1 means descending. Use 1 for ascending (default).
Multi-key sorting uses array constants — sort by column 1 ascending, then column 3 descending:
Sort an array by columns NOT in the result
Use when the sort key is in a different column than what you want returned.
Return names (column A) sorted by their salaries (column C, not returned):
Full details: SORT function page.
SEQUENCE and RANDARRAY — generation
Generate an array of sequential numbers
The numbers 1 through 10 down a column:
A 5×5 multiplication table (using an array trick — multiply row headers by column headers):
The next 30 dates starting today:
Generate random numbers as an array
Ten random integers between 1 and 100:
Useful for simulations, random sampling, or test data. Unlike the old RAND function, RANDARRAY lets you specify range and count in one call.
TAKE, DROP, CHOOSEROWS, CHOOSECOLS — slice and pick
Return the first (or last) N rows or columns
First 5 rows of a range:
Last 5 rows (negative number takes from the end):
First 5 rows AND first 2 columns:
Skip the first (or last) N rows or columns
Skip the header row and return everything below:
DROP is the inverse of TAKE. Together they let you slice any portion of an array. Both were added in Excel 365 in 2022 — perpetual Excel 2021 doesn't have them.
Pick specific rows or columns by number
Return rows 1, 3, 5, and 7 from a range:
Return columns 1, 3, and 6 from a wide range:
Great for pulling non-contiguous columns without repositioning source data.
WRAPROWS, WRAPCOLS, TOROW, TOCOL, EXPAND — reshape
Convert a 1D array into a 2D grid
Take 12 values in a column and wrap into a 3-row × 4-column grid:
WRAPCOLS does the same but wraps into columns instead. Useful for reshaping list data into printable grids or dashboards.
Flatten a 2D array to a single row or column
Take a 3×4 grid and flatten to a single 12-cell column:
The 2nd argument controls what to do with blanks and errors: 0 keeps them, 1 skips blanks, 2 skips errors, 3 skips both. The 3rd argument (TRUE/FALSE) controls scan order — by row (default) or by column.
Grow an array to a target size, padding with a value
Expand a 2×2 range to 5×4, padding with empty strings:
Rare use case but essential when combining arrays of different sizes — EXPAND lets you align dimensions.
Nested patterns — combining DA functions
The real power comes from nesting. Each DA function returns an array, and any DA function can take an array as input — so you can build sophisticated pipelines in single formulas. Six patterns worth memorizing:
Pattern 1 — Sorted unique list
Get every distinct region from a column, alphabetized:
UNIQUE strips duplicates, SORT alphabetizes the result. Perfect for populating dropdown lists that stay clean automatically.
Pattern 2 — Top N by value
Top 5 highest sales amounts:
SORT descending, TAKE first 5. Instant top-N leaderboard without ranking helper columns.
Pattern 3 — Filter then sort
All APAC transactions, sorted by amount descending:
FILTER first to narrow rows, SORT the result. Classic pipeline pattern.
Pattern 4 — Count unique in filtered subset
How many distinct products did APAC buy?
FILTER to APAC rows, UNIQUE for distinct products, ROWS to count them. Three functions, one meaningful KPI.
Pattern 5 — Group and aggregate
Build a mini pivot: region + total sales, without a pivot table:
CHOOSE builds a 2-column output: UNIQUE regions in column 1, SUMIF totals per region in column 2. Refreshes live as source data changes — pivot tables don't.
Pattern 6 — Random sampling
Pick 3 random rows from a range for spot-checking:
RANDARRAY generates 3 random row numbers, CHOOSEROWS returns those rows. Instant random sample for QA workflows.
Once you're comfortable, you'll design formulas like pipelines: raw data → filter → sort → take → done. Each stage transforms the array in one specific way. It reads like functional programming — because dynamic arrays essentially added functional programming to Excel.
#SPILL! error — causes and fixes
The #SPILL! error means Excel can't fill the target cells. Almost always one of three causes:
Cause 1 — Data is blocking the spill zone
Your formula wants to spill down 10 rows but row 5 in that range has a value. Excel refuses to overwrite existing data.
Fix: click the anchor cell. Excel highlights the desired spill zone with a dashed outline. Clear any cells inside that outline — the spill happens automatically.
Cause 2 — Merged cells in the spill zone
Dynamic arrays and merged cells are incompatible. Even a single merged cell anywhere in the target zone breaks the entire spill.
Fix: unmerge cells in the spill area. Select the range, right-click, Format Cells, Alignment tab, uncheck Merge cells.
Cause 3 — Formula inside or near an Excel Table
Excel Tables and dynamic array spilling conflict. If your DA formula is inside a Table structure or right at its edge, the Table blocks the spill.
Fix: move the formula to an empty area of the sheet, outside any Table.
If you need only the first value from a formula that would spill, prefix it with @: =@FILTER(...). This forces implicit intersection — Excel returns a single value and the formula never tries to spill. See the full #SPILL! error guide.
#CALC! error — causes and fixes
The #CALC! error means a dynamic array function returned something unusable — typically an empty array from FILTER, or a recursive LAMBDA that hit a base-case issue.
Cause 1 — FILTER returned no rows
Nothing matches, empty array returned, #CALC! shows in the anchor cell.
Fix: always provide the 3rd argument (if_empty):
Cause 2 — Nested array of arrays
Some formulas produce arrays containing other arrays as elements. Excel can't display those.
Fix: restructure the formula to flatten. Often TOROW or TOCOL can help.
Cause 3 — Recursive LAMBDA problem
A custom LAMBDA using recursion may return something unusable if the base case doesn't fire correctly.
Fix: test the LAMBDA with the simplest possible input first. Verify the base case returns a concrete value, not another recursive call. See the full #CALC! error guide.
The @ operator and implicit intersection
Before dynamic arrays, Excel used a concept called "implicit intersection" — a formula in a cell would return only the single value aligning with that cell's row/column. Modern Excel replaced this with spilling, but backward compatibility required a way to force the old behavior. That's the @ operator.
Compare these two:
This spills — returns all matching rows.
This returns only the FIRST matching value. Just one cell, no spilling.
When to use @
Three main use cases:
- You want only one value from a DA function. Rare but sometimes needed.
- Backward compatibility with old workbooks. When someone opens a modern-Excel workbook in Excel 2019, formulas that would have spilled show
@to indicate "return single value here". - Avoiding #SPILL! errors quickly. If a spill zone can't be cleared,
@lets the formula work by returning just the first value.
Legacy CSE arrays vs modern dynamic arrays
If you've used Excel for a long time, you may remember Ctrl+Shift+Enter (CSE) array formulas. Modern Excel still supports them for backward compatibility, but there's no reason to write new ones. Comparison:
| Aspect | Legacy CSE arrays | Modern dynamic arrays |
|---|---|---|
| How to enter | Ctrl+Shift+Enter | Just Enter |
| Visual indicator | Curly braces {like this} | Blue border around spill |
| Sizing | Pre-select target cells; wrong size = errors | Automatic; grows and shrinks with data |
| Editing | Edit any cell in the array separately | Only the anchor cell is editable |
| Deletion | Delete individual cells to break the array | Delete the anchor cell to clear the whole spill |
| Modern equivalent | Most CSE patterns are simpler with modern DA functions | Use FILTER, UNIQUE, SORT, etc. instead |
You'll still encounter legacy CSE arrays in older workbooks. Modern Excel evaluates them the same way as before, so nothing breaks. When updating an old workbook, consider replacing CSE patterns with dynamic array equivalents — the code is simpler and easier to maintain.
Compatibility — Excel 365 vs 2021 vs older
| Function | Excel 365 | Excel 2021 perpetual | Excel 2019 & older |
|---|---|---|---|
| FILTER | ✅ | ✅ | ❌ |
| UNIQUE | ✅ | ✅ | ❌ |
| SORT | ✅ | ✅ | ❌ |
| SORTBY | ✅ | ✅ | ❌ |
| SEQUENCE | ✅ | ✅ | ❌ |
| RANDARRAY | ✅ | ✅ | ❌ |
| TAKE, DROP | ✅ | ❌ | ❌ |
| CHOOSEROWS, CHOOSECOLS | ✅ | ❌ | ❌ |
| WRAPROWS, WRAPCOLS | ✅ | ❌ | ❌ |
| TOROW, TOCOL | ✅ | ❌ | ❌ |
| EXPAND | ✅ | ❌ | ❌ |
Excel 2021 perpetual has the first 6 dynamic array functions (from 2020) but NOT the 5 newer ones added in 2022. If you're on 2021 perpetual, FILTER and UNIQUE work but TAKE and WRAPROWS return #NAME?. Only Excel 365 subscription gets everything.
Google Sheets supports FILTER, UNIQUE, SORT, and SEQUENCE with nearly identical syntax. Google has its own equivalents for the newer functions. LibreOffice Calc does not support any dynamic array functions as of 2026 — all show #NAME?.
Related functions and error pages
📥 Download the practice workbook
All 15 DA functions with syntax + examples · 6 nested patterns · sample sales transactions
Frequently asked questions
What are dynamic arrays in Excel?
Dynamic arrays are formulas that return multiple values which automatically spill into surrounding cells. Introduced in Excel 365 in 2020, they replaced the old Ctrl+Shift+Enter (CSE) array formulas with a much cleaner mechanism. One formula in one cell can produce hundreds of results — Excel handles the placement automatically.
Which Excel versions support dynamic arrays?
Excel 365 (all subscription tiers) and Excel 2021 perpetual license. Not available in Excel 2019 or older. Some functions (TAKE, DROP, WRAPROWS, TOROW, etc.) were added in 2022 and require Excel 365 specifically — the 2021 perpetual version does not have them.
What is a spill range?
The spill range is the group of cells that a dynamic array formula fills. The formula lives in the anchor cell (top-left), and Excel automatically fills the neighboring cells with the rest of the results. You can only edit the anchor cell — the spilled cells are read-only and show a subtle blue border when you click any of them.
What causes the #SPILL! error?
The #SPILL! error appears when a dynamic array can't fill its target cells. Three common causes: (1) cells in the spill zone already contain data, (2) merged cells are blocking the spill area, (3) the formula is inside or too close to an Excel Table structure. Fix by clearing blocking cells or unmerging.
How is FILTER different from Excel's built-in AutoFilter?
AutoFilter is a UI feature that hides rows in place. FILTER is a formula that returns matching rows as a new spilling array without touching the original data. FILTER updates automatically when source data changes, works with cell references for the criteria, and can be nested inside other functions — none of which AutoFilter can do.
Can I use dynamic arrays inside Excel Tables?
Not directly. Excel Tables and dynamic array spilling don't play well together — the table structure blocks the spill. Workaround: keep dynamic array formulas outside any Table, or use the @ (implicit intersection) operator to return only the first value. Microsoft has announced better integration is coming but as of 2026 it's not yet available.
What is the @ operator in Excel?
The @ prefix forces implicit intersection — meaning a formula that would normally spill instead returns only a single value (the one that aligns with the current row). Useful when you need only the first result of a dynamic array function, or for backward compatibility with older workbooks. Example: =@FILTER(A:A, B:B="APAC") returns just the first APAC match instead of spilling all matches.
Do dynamic arrays work in Google Sheets?
Most do, but with different syntax. Google Sheets uses ARRAYFORMULA as a wrapper for many array operations. FILTER, UNIQUE, and SORT work in Google Sheets with nearly identical syntax to Excel. Newer functions like TAKE, DROP, WRAPROWS are Google-specific in their own way but similar concepts.
Are dynamic array formulas slower than regular formulas?
Not meaningfully. Modern Excel's calculation engine handles dynamic arrays efficiently. If anything, they're often FASTER than the alternative — a single FILTER formula is typically faster than 100 individual IF-based helper formulas doing the same work. The speed difference is only noticeable on very large ranges (100,000+ rows).
Master this and you master modern Excel. Dynamic arrays are the single most important skill for the next decade of spreadsheet work.