Excel Array Formulas & Dynamic Arrays: The Complete Guide
Dynamic arrays changed how Excel formulas work. One formula in one cell now fills multiple cells automatically — no dragging, no Ctrl+Shift+Enter, no worry about how many rows you need. This guide covers the new dynamic array functions (FILTER, SORT, UNIQUE, SEQUENCE, and more), the spill operator, spill errors, and when the older CSE array formulas still matter.
What's in this guide
- Array formulas, then vs now
- Spill ranges and the # operator
- FILTER
- SORT and SORTBY
- UNIQUE
- SEQUENCE
- RANDARRAY
- TEXTSPLIT, TEXTBEFORE, TEXTAFTER
- TOCOL, TOROW, and their pair
- LAMBDA and helper functions
- The #SPILL! error
- Common patterns
- Legacy CSE array formulas
- Compatibility & when to use which
- Pitfalls
Array formulas, then vs now
Before 2020: CSE array formulas
For decades, Excel had "array formulas" — special formulas confirmed with Ctrl+Shift+Enter (CSE) instead of just Enter. They processed ranges as arrays and returned multiple values, but the workflow was awkward: you had to pre-select the output range, hit CSE, and could never edit a single cell in the result.
Example (legacy CSE, still works in every Excel):
Sum of products, without a helper column
{=SUM(A2:A10 * B2:B10)}
2020 and after: dynamic arrays
Excel 365 (and later Excel 2021) introduced dynamic arrays. Now any formula that returns multiple values automatically "spills" into neighboring cells. Press Enter — not CSE — and Excel fills as many cells as the result requires.
Same example, modern approach:
SUMPRODUCT still works, no CSE needed
=SUMPRODUCT(A2:A10, B2:B10)
Or with the newer array-aware built-ins, you can do things the old approach couldn't cleanly express — like returning a filtered list without helper columns.
Modern Excel opens old CSE formulas as-is, and both approaches coexist in the same workbook. New formulas should almost always use the modern (spill) approach — cleaner syntax, easier to edit, and no pre-selection needed.
Spill ranges and the # operator
What "spill" means
Type =SEQUENCE(5) in cell A1. Excel fills A1:A5 with 1, 2, 3, 4, 5. The formula lives only in A1 — cells A2:A5 show values that come from A1's spill. Click A2 and you see the number 2 with no formula in the bar; click A1 and you see =SEQUENCE(5).
This is the spill range. It has a subtle blue outline around it in modern Excel to show what belongs to the spill.
The # operator
Once a formula spills, you can reference the whole spill range with the # operator applied to the top-left cell.
Reference an entire spill
=SUM(A1#) → sums the whole spill from A1
=COUNTA(A1#) → counts non-empty cells in the spill
=ROWS(A1#) → how many rows the spill takes up
The magic: as the source spill grows or shrinks, formulas that reference it with # automatically adjust. No manual range updates.
Implicit intersection with @
Sometimes you want a formula to return just one value even though it could return many. Prefix a range or function with @:
Return only one value instead of spilling
=@FILTER(A2:A100, B2:B100 = "Yes")
FILTER
The star of dynamic arrays. Returns rows from a range that match a condition — a data-cleaning powerhouse that replaces hours of manual work with one formula.
Syntax
=FILTER(array, include, [if_empty])
| Argument | What it does |
|---|---|
| array | The range to filter from |
| include | A boolean array (same rows as array) — TRUE keeps the row, FALSE drops it |
| if_empty | Optional. What to return if no rows match. Default is #CALC! error. |
Examples
All rows where region is "West"
=FILTER(A2:D100, B2:B100 = "West")
Filter with multiple conditions (AND)
=FILTER(A2:D100, (B2:B100 = "West") * (C2:C100 > 1000))
Filter with OR
=FILTER(A2:D100, (B2:B100 = "West") + (B2:B100 = "East"))
Handle no matches gracefully
=FILTER(A2:D100, B2:B100 = "West", "No matches found")
Return only specific columns
FILTER returns all columns of the source range. Combine with CHOOSECOLS to slice:
Only name and email of Western customers
=CHOOSECOLS(FILTER(A2:D100, B2:B100 = "West"), 1, 4)
SORT and SORTBY
SORT
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
| Argument | What it does |
|---|---|
| array | The range to sort |
| sort_index | Column (or row) to sort by. Default 1. |
| sort_order | 1 = ascending (default), -1 = descending |
| by_col | FALSE (default) sorts rows; TRUE sorts columns |
Sort by column 3 descending
=SORT(A2:D100, 3, -1)
SORTBY (more flexible)
Sorts a range by one or more separate ranges. Great when the "sort key" isn't part of the output.
Syntax
=SORTBY(array, by_array1, [order1], [by_array2, order2], ...)
Sort names by score descending
=SORTBY(A2:A100, B2:B100, -1)
Sort by region ascending, then revenue descending
=SORTBY(A2:D100, B2:B100, 1, C2:C100, -1)
UNIQUE
Returns the distinct values from a range. Perfect for building dropdown source lists, category lists, and deduplicated exports.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| Argument | What it does |
|---|---|
| array | Source range |
| by_col | FALSE (default) returns unique rows; TRUE returns unique columns |
| exactly_once | FALSE (default) returns each distinct value once; TRUE returns only values that appear exactly once (removing duplicates entirely) |
List of unique regions
=UNIQUE(B2:B100)
Values that appear only once
=UNIQUE(A2:A100, FALSE, TRUE)
Sorted unique list
=SORT(UNIQUE(B2:B100))
Unique combinations of two columns
=UNIQUE(A2:B100)
SEQUENCE
Generates a sequence of numbers as a spilled array. Replaces helper columns of manually typed numbers.
Syntax
=SEQUENCE(rows, [columns], [start], [step])
Numbers 1 to 10 down a column
=SEQUENCE(10)
Multiplication table 10×10
=SEQUENCE(10) * SEQUENCE(1, 10)
Dates for the next 30 days
=TODAY() + SEQUENCE(30) - 1
Even numbers up to 100
=SEQUENCE(50, 1, 2, 2)
RANDARRAY
Generates random numbers as a spilled array. Perfect for sample data, simulations, and randomized sorting.
Syntax
=RANDARRAY([rows], [columns], [min], [max], [whole_number])
100 random decimals between 0 and 1
=RANDARRAY(100)
Random integers 1-100 in a 10×5 grid
=RANDARRAY(10, 5, 1, 100, TRUE)
Shuffle a list randomly
=SORTBY(A2:A100, RANDARRAY(99))
TEXTSPLIT, TEXTBEFORE, TEXTAFTER
Text handling got a huge upgrade with these array-aware functions.
TEXTSPLIT
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Split "Zain, Khan, Islamabad" into three cells
=TEXTSPLIT("Zain, Khan, Islamabad", ", ")
Split into a grid (rows and columns)
=TEXTSPLIT(A1, ",", ";")
TEXTBEFORE and TEXTAFTER
Extract text before or after a delimiter — no more nested LEFT/RIGHT/FIND gymnastics.
Everything before the first "@"
=TEXTBEFORE(A2, "@")
Everything after the last "/"
=TEXTAFTER(A2, "/", -1)
TOCOL, TOROW, and their pair (WRAPROWS, WRAPCOLS)
Reshape ranges — flatten grids into single columns/rows, or split a long list into a grid.
TOCOL / TOROW
Flatten a 2D range into a single column
=TOCOL(A2:E20)
Ignore blanks while flattening
=TOCOL(A2:E20, 1)
WRAPROWS / WRAPCOLS
Turn a 20-item list into a 4-column grid
=WRAPROWS(A2:A21, 4)
LAMBDA and helper functions
LAMBDA lets you define reusable custom functions in the sheet itself — no VBA required. Combined with helpers like MAP, REDUCE, and BYROW, it turns Excel into a compact functional-programming environment.
A simple named LAMBDA
Formulas → Name Manager → New Name → Celsius:
Reusable Fahrenheit → Celsius
=LAMBDA(f, (f - 32) * 5 / 9)
=Celsius(98.6) returns 37. Distributed across the workbook without VBA.MAP — apply a LAMBDA to every value
Convert a column of Fahrenheit temps to Celsius
=MAP(A2:A20, LAMBDA(x, (x - 32) * 5 / 9))
REDUCE — accumulate across a range
Chain a series of values together as a delimited string
=REDUCE("", A2:A10, LAMBDA(acc, v, acc & "|" & v))
BYROW / BYCOL — apply a LAMBDA to each row or column
Sum each row into a spilled column
=BYROW(A2:D100, LAMBDA(row, SUM(row)))
Once you've built five or six reusable LAMBDAs (Celsius, USD-to-EUR, format a phone number, calculate age, extract email domain), you can drop them into any workbook and use them like built-in functions. LAMBDA + MAP + REDUCE covers most cases you'd otherwise reach for VBA for.
The #SPILL! error
What causes it
A dynamic array formula tried to spill into cells that weren't empty. Excel refuses to overwrite existing content.
How to fix
Click the yellow warning icon next to the formula — Excel offers to select the obstructing cells for you. Then:
- If the blockers are data, clear them or move the formula elsewhere
- If they're other formulas, decide which one wins and delete the other
- If they're merged cells, unmerge them (merged cells always block spills)
- If the formula is inside an Excel Table, spill is limited — dynamic arrays don't spill outside a Table's boundary. Move the formula outside the Table or restructure.
Less obvious causes
- Conditional formatting on the target cells — usually harmless, but if the formula spills a variant type Excel can't format, you may see the error
- Volatile range too large —
=SEQUENCE(1000000)may hit an out-of-memory error before spill - Circular reference — if the formula references its own spill range, Excel refuses
Common patterns
1. Auto-updating unique dropdown source list
Sorted unique categories
=SORT(UNIQUE(FILTER(Data[Category], Data[Category] <> "")))
=Sheet1!$G$2#. Adding new categories to the Data table auto-updates the dropdown.2. Top N with ties
Top 5 salespeople by revenue
=SORT(FILTER(A2:B100, B2:B100 >= LARGE(B2:B100, 5)), 2, -1)
3. Rolling calendar
Next 90 workdays, skipping weekends
=FILTER(TODAY() + SEQUENCE(120) - 1, WEEKDAY(TODAY() + SEQUENCE(120) - 1, 2) <= 5)
4. Split full name into first and last
Two-column spill from a single formula
=TEXTSPLIT(A2, " ")
5. Count unique per category
Unique customers per region
=LET(regions, UNIQUE(B2:B100),
counts, BYROW(regions, LAMBDA(r, COUNTA(UNIQUE(FILTER(A2:A100, B2:B100 = r))))),
HSTACK(regions, counts))
6. Frequency table
Value and count, sorted by frequency
=LET(v, UNIQUE(A2:A100),
c, BYROW(v, LAMBDA(x, COUNTIF(A2:A100, x))),
SORT(HSTACK(v, c), 2, -1))
7. Cross join (Cartesian product)
Every combination of A and B
=LET(a, A2:A5, b, B2:B4,
ra, TOCOL(IF(SEQUENCE(1, ROWS(b)), a)),
rb, TOCOL(IF(SEQUENCE(ROWS(a), 1), TRANSPOSE(b))),
HSTACK(ra, rb))
Legacy CSE array formulas
You'll still encounter Ctrl+Shift+Enter formulas in older workbooks, and modern Excel understands them for backward compatibility.
Common legacy patterns
Sum with a condition (pre-SUMIFS)
{=SUM((B2:B100="West") * C2:C100)}
=SUMIFS(C2:C100, B2:B100, "West") — same result, no CSE needed.Array of conditions
{=MAX((A2:A100="Product X") * B2:B100)}
=MAXIFS(B2:B100, A2:A100, "Product X")When you might still write CSE
- Workbooks that need to open in Excel 2019 or earlier
- Environments that share files with users on legacy Excel
- Complex array logic where a helper function has no direct equivalent (rare)
You must select the entire array before editing, and you must press Ctrl+Shift+Enter again to confirm. Forget the CSE and Excel splits the formula into individual cells — usually losing data in the process. Prefer modern spilled formulas for any new work.
Compatibility & when to use which
| Excel version | Dynamic arrays? | What works |
|---|---|---|
| Excel 365 (Win/Mac) | Yes — everything | All dynamic array functions, LAMBDA, LET |
| Excel 2024 (Win/Mac) | Yes — everything | All dynamic array functions |
| Excel 2021 (Win/Mac) | Yes — most | FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, LET |
| Excel 2019 & earlier | No | Only legacy CSE formulas; dynamic array functions show #NAME? |
| Excel on the web | Yes | Full support |
| Excel mobile | Read only | Displays spilled results but limited editing |
How to check
File → Account → About Excel. If it says "Microsoft 365" or "Excel 2021" or newer, you have dynamic arrays. Excel 2019 and older don't.
Sharing workbooks with older Excel users
Dynamic array formulas in shared workbooks show as CSE formulas on older Excel — Excel converts them for backward compatibility, but the results become static. If the source data changes, the older-Excel user won't see updated results. For mixed-audience workbooks, use SUMIFS/COUNTIFS/INDEX-MATCH instead of the newer functions.
Pitfalls
Dynamic array formulas inside an Excel Table don't spill beyond the Table row. If you type FILTER inside a Table column, you get #SPILL! or one-value output. Put dynamic array formulas outside Tables.
Recent Excel updates fixed this, but older versions of Excel 365 didn't automatically extend chart source ranges when a spill grew. If you build a chart on a spill, verify the chart data range uses A1# notation to reference the whole spill, not a fixed range.
RANDARRAY recalculates whenever any cell in the workbook changes. That means shuffle-based sorting or random samples change every time. For a stable random sample: enter the formula, then copy-paste values to freeze.
A cell containing 5 (number) and a cell containing "5" (text) are the same for UNIQUE — both return "5" in the output. If type matters, add a helper like =UNIQUE(A2:A100&"|"&TYPE(A2:A100)).
When a spilled result reaches an empty cell, that value is 0 (not blank). Downstream formulas summing the spill might include unexpected zeros. Filter with <>"" in the FILTER step if this matters.
Named ranges pointing to a spill via =Sheet1!$A$2# work great — but Name Manager sometimes displays them oddly, and copying them across workbooks can lose the # reference. Verify after any copy operation.
Dynamic array formulas without the fiddling
FILTER, UNIQUE, SORT nested into a single formula, plus LAMBDAs for reuse — dynamic arrays are powerful but can get syntactically dense. Excel Wizard generates the exact formula from plain-English intent ("top 5 products by revenue in Q4, only Western region") — with proper spill setup and error handling.
Install Excel Wizard →Frequently asked questions
What is a dynamic array in Excel?
A formula result that automatically fills multiple cells from a single formula. Introduced in Excel 365 and 2021, dynamic arrays let functions like FILTER, SORT, and UNIQUE spill their output into neighboring cells.
What is the # symbol in Excel formulas?
The spill operator. A1# references the entire spilled range that starts at A1. Downstream formulas using # adjust automatically when the source spill changes size.
What causes the #SPILL! error?
A dynamic array formula tried to spill into cells that aren't empty. Clear the blocking cells, or move the formula. Merged cells and Table columns can also block spills.
Do I still need Ctrl+Shift+Enter in modern Excel?
Rarely. Modern Excel treats formulas as array-aware automatically. CSE formulas remain for backward compatibility, but new work almost always uses plain Enter.
Are dynamic arrays available in Excel for Mac?
Yes, in Excel 365 and Excel 2021 on Mac. Excel 2019 and earlier for Mac don't support them.