Excel Array Formulas & Dynamic Arrays: The Complete Guide (2026)
Home›Excel›Array Formulas & Dynamic Arrays
ExcelPillar Guide⏱ 24 min read

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.

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)}
Curly braces are added by Excel when you press Ctrl+Shift+Enter. Don't type them yourself.

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.

The transition is smooth

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")
Returns the first match instead of spilling all matches. Rarely needed in new formulas, but sometimes shows up when older files open in modern Excel.

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])
ArgumentWhat it does
arrayThe range to filter from
includeA boolean array (same rows as array) — TRUE keeps the row, FALSE drops it
if_emptyOptional. What to return if no rows match. Default is #CALC! error.

Examples

All rows where region is "West"

=FILTER(A2:D100, B2:B100 = "West")
Returns matching rows with all four columns. Spills as far down and across as needed.

Filter with multiple conditions (AND)

=FILTER(A2:D100, (B2:B100 = "West") * (C2:C100 > 1000))
Multiplication of boolean arrays acts as AND. Both conditions must be true.

Filter with OR

=FILTER(A2:D100, (B2:B100 = "West") + (B2:B100 = "East"))
Addition acts as OR — either condition true keeps the row. Wrap the sum in a comparison if values could exceed 1.

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])
ArgumentWhat it does
arrayThe range to sort
sort_indexColumn (or row) to sort by. Default 1.
sort_order1 = ascending (default), -1 = descending
by_colFALSE (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)
Returns just the names (column A), ordered by scores in column B, highest first.

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])
ArgumentWhat it does
arraySource range
by_colFALSE (default) returns unique rows; TRUE returns unique columns
exactly_onceFALSE (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)
Any value appearing more than once is dropped entirely.

Sorted unique list

=SORT(UNIQUE(B2:B100))

Unique combinations of two columns

=UNIQUE(A2:B100)
Each distinct row (region + product) shows once. Great for building pivot-style breakdowns without a pivot table.

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)
Column times row — outer product. Spills a 10×10 grid.

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))
Randomly reorders the source list. Recalculates every time the sheet does — use Copy → Paste Values to freeze a specific shuffle.

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", ", ")
Spills across three columns.

Split into a grid (rows and columns)

=TEXTSPLIT(A1, ",", ";")
Commas separate columns; semicolons separate rows. Perfect for parsing structured strings.

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)
Negative instance counts from the end. Extract filenames from paths, TLDs from URLs, etc.

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)
Reads left-to-right, top-to-bottom, spilling as a single column.

Ignore blanks while flattening

=TOCOL(A2:E20, 1)
Second argument: 0 (default) = keep blanks, 1 = ignore blanks, 2 = ignore errors, 3 = ignore both.

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)
In any cell: =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)))
LAMBDA is a rabbit hole worth going down

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] <> "")))
Reference this from Data Validation with =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, " ")
Applied to each row of a name column (drag or use BYROW): full name splits into first + last cleanly.

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)}
Modern equivalent: =SUMIFS(C2:C100, B2:B100, "West") — same result, no CSE needed.

Array of conditions

{=MAX((A2:A100="Product X") * B2:B100)}
Modern equivalent: =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)
Editing CSE formulas is fiddly

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 versionDynamic arrays?What works
Excel 365 (Win/Mac)Yes — everythingAll dynamic array functions, LAMBDA, LET
Excel 2024 (Win/Mac)Yes — everythingAll dynamic array functions
Excel 2021 (Win/Mac)Yes — mostFILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, LET
Excel 2019 & earlierNoOnly legacy CSE formulas; dynamic array functions show #NAME?
Excel on the webYesFull support
Excel mobileRead onlyDisplays 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

Spilled ranges inside Tables

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.

Charts don't auto-track spill ranges (in older versions)

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.

Volatile RANDARRAY

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.

UNIQUE treats types loosely

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)).

Empty cells become zero in some contexts

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 at spills

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.

Excel Wizard

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.