Dynamic Arrays

Before Microsoft 365, a formula always returned exactly one value into exactly one cell. If you wanted results in multiple cells, you wrote multiple formulas. Dynamic arrays changed that completely. A single formula can now return a whole range of results — automatically expanding to fill as many rows and columns as the data requires. This is called spilling, and it is the engine behind FILTER, SORT, UNIQUE, and several other modern Excel functions that we cover in the next few lessons.

What Spilling Means

When a dynamic array formula returns multiple values, Excel spills those values into the cells below (or beside) the cell where the formula lives. You only type the formula in one cell — Excel fills in the rest automatically. If the data changes and the result grows or shrinks, the spill range adjusts to match.

The Spill Range and the # Operator

When a dynamic array formula spills, Excel treats the entire filled area as a single unit called the spill range. You can refer to the entire spill range in other formulas using the # (hash) operator after the cell address of the formula cell.

Formula entered in E2:
=A2:A6*1.2

This spills results into E2:E6.

Refer to the entire spill range in another formula:
=SUM(E2#)           →  Sums all spilled values in E2:E6
=AVERAGE(E2#)       →  Average of the spill range
=SORT(E2#)          →  Sorts the spill range

If the source data in A2:A6 grows to A2:A8,
the spill range expands to E2:E8 automatically —
and =SUM(E2#) updates to include the new rows too.

The # operator is one of the most useful things to learn when working with dynamic arrays. Any formula that references a spill range using # will automatically include all spilled values — even if new rows are added later. This makes dashboards and summaries that reference dynamic output self-maintaining.

The #SPILL! Error

If something is already in the cells where a dynamic array formula wants to spill its results, Excel cannot complete the spill and shows a #SPILL! error. This is the most common dynamic array error and the fix is always the same — clear the cells in the spill range.

FILTER — Extract Rows That Match a Condition

FILTER is the dynamic array function you will reach for most often. It returns all rows from a table where a specified condition is true — automatically, in one formula, with no helper columns and no manual filtering. The output spills into as many rows as there are matches.

=FILTER(array, include, [if_empty])

  array      →  The range to return (can be multiple columns)
  include    →  A TRUE/FALSE condition — which rows to include
  if_empty   →  What to show if no rows match (optional)

Multiple conditions — North region AND sales over 100,000:
=FILTER(A2:C6, (B2:B6="North") * (C2:C6>100000), "No results")

OR condition — North OR East region:
=FILTER(A2:C6, (B2:B6="North") + (B2:B6="East"), "No results")

Multiply (*) for AND logic — both conditions must be true
Add (+) for OR logic — either condition can be true

The multiplication trick for AND, and addition for OR, is a powerful pattern that works across all dynamic array functions. It comes from the fact that TRUE = 1 and FALSE = 0 — multiplying two conditions means both must be 1, adding them means at least one must be non-zero.

SORT — Sort a Range With a Formula

SORT returns a sorted copy of a range as a spilled result. The original data is untouched. You can sort by any column, in either direction, without modifying the source table — ideal for dashboards where the raw data needs to stay in its original order.

=SORT(array, [sort_index], [sort_order], [by_col])

  sort_index   →  Column number to sort by (default: 1)
  sort_order   →  1 = ascending (default),  -1 = descending
  by_col       →  FALSE = sort rows (default),  TRUE = sort columns

=SORT(A2:C6)                 →  Sort by column 1, ascending
=SORT(A2:C6, 3, -1)          →  Sort by column 3 (Sales), descending
=SORT(A2:C6, 2)              →  Sort by column 2 (Region), A–Z

Notice the source data is unchanged — SORT just returns a sorted copy. This is exactly what you want for a dashboard leaderboard: the raw data stays in entry order, and the sorted view updates automatically whenever new figures come in.

UNIQUE — Extract a Deduplicated List

UNIQUE returns a list with duplicate values removed. Give it a column of region names, customer IDs, or product categories and it returns only the distinct values — automatically, as a spilled list. No manual deduplication, no Remove Duplicates button needed.

=UNIQUE(array, [by_col], [exactly_once])

  by_col        →  FALSE = deduplicate rows (default),  TRUE = deduplicate columns
  exactly_once  →  FALSE = one of each (default),  TRUE = only values appearing once

Regions column B2:B6: North, East, North, South, North

=UNIQUE(B2:B6)         →  North, East, South   (one of each)
=UNIQUE(B2:B6,,TRUE)   →  East, South          (only values appearing exactly once)

UNIQUE is most powerful when combined with SORT: =SORT(UNIQUE(B2:B6)) produces an alphabetically sorted list of unique values. This is the standard way to build dynamic dropdown lists and report category headers that update automatically as new data is added.

Combining Dynamic Array Functions

The real power emerges when you nest dynamic array functions together. Because each function returns an array, the output of one can feed directly into another — building complex filtered, sorted, and deduplicated results in a single formula.

Sort filtered results — North region, sorted by sales descending:
=SORT(FILTER(A2:C6, B2:B6="North"), 3, -1)

Unique sorted list of regions:
=SORT(UNIQUE(B2:B6))

Unique regions with sales over 80,000:
=UNIQUE(FILTER(B2:B6, C2:C6>80000))

Count unique values in a range:
=COUNTA(UNIQUE(B2:B6))

Dynamic Arrays and Excel Tables

Dynamic array formulas work best when the source data is formatted as an Excel Table (Insert → Table, or Ctrl+T). When the lookup array is a Table, adding new rows to the table automatically extends the ranges used in your dynamic array formulas — so FILTER, SORT, and UNIQUE results all update without any formula changes.

Source data formatted as Table named "SalesData"

=FILTER(SalesData, SalesData[Region]="North")
=SORT(UNIQUE(SalesData[Region]))
=SORT(SalesData, XMATCH("Sales", SalesData[#Headers]), -1)

When new rows are added to SalesData,
all three formulas update automatically — no range editing needed.

Next up — TEXTSPLIT, where you will learn how to split text into multiple cells using any delimiter, and how dynamic arrays make text splitting more powerful than ever.