Excel Dynamic Arrays

Dynamic arrays are a modern Excel feature that lets a single formula produce multiple results that automatically spill into neighbouring cells. Before dynamic arrays, one formula produced one result in one cell. Now, one formula can fill an entire column or table on its own.

Think of dynamic arrays like a garden hose connected to a sprinkler — one input (the water source) spreads its output across many spots automatically, adjusting when conditions change.

What Is a Spill Range?

When a dynamic array formula runs, its results "spill" into adjacent empty cells. Excel calls this the spill range. If any cell in the spill range contains data, Excel shows a #SPILL! error until you clear that cell.

A1: =SEQUENCE(5)    ← one formula in A1

Result:
A1: 1  ← formula cell
A2: 2  ← spilled
A3: 3  ← spilled
A4: 4  ← spilled
A5: 5  ← spilled

All five cells update automatically when you change the formula.

To reference the entire spill range from another cell, use the spill operator (#):

=SUM(A1#)    ← adds all values in the spill range starting at A1

FILTER — Extract Rows That Match a Condition

FILTER extracts rows from a table based on a condition you specify. The results spill automatically into as many rows as match.

=FILTER(array, include, [if_empty])

Data table (A1:C6):
Name      Region    Sales
Alice     North     5000
Bob       South     3000
Carol     North     7000
David     East      4000
Eva       North     6000

=FILTER(A2:C6, B2:B6="North")

Result (spills into 3 rows):
Alice     North     5000
Carol     North     7000
Eva       North     6000

Add a third argument to handle the case when no rows match:

=FILTER(A2:C6, B2:B6="West", "No data found")

SORT — Sort a Range Without Moving Source Data

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

sort_index  → which column to sort by (1 = first column)
sort_order  → 1 for ascending, -1 for descending

=SORT(A2:B10, 2, -1)
→ Returns the data sorted by column 2 (B), highest to lowest
→ The original data in A2:B10 stays untouched

SORT Diagram

Source data (A2:B5):        Spilled result of =SORT(A2:B5, 2, -1):
Name     Score               Name     Score
Raj       70                 Priya     95
Priya     95                 Sam       85
Sam       85                 Raj       70

SORTBY — Sort by a Column Not in the Result

=SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2], ...)

=SORTBY(A2:A10, B2:B10, 1)
→ Returns names from column A sorted by values in column B (ascending)
→ Column B is not included in the output

UNIQUE — List of Unique Values

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

=UNIQUE(A2:A10)
→ Returns a list of all unique entries from A2:A10, no duplicates

Source: Apple, Banana, Apple, Orange, Banana, Grape
Result: Apple, Banana, Orange, Grape

SEQUENCE — Generate a Number Series

=SEQUENCE(rows, [cols], [start], [step])

=SEQUENCE(5)            → 1, 2, 3, 4, 5 (in one column)
=SEQUENCE(3, 4)         → 3 rows × 4 columns grid of numbers 1–12
=SEQUENCE(5, 1, 10, 10) → 10, 20, 30, 40, 50
=SEQUENCE(4, 1, 0, 5)   → 0, 5, 10, 15

SEQUENCE Grid Diagram

=SEQUENCE(3, 4)

 1   2   3   4
 5   6   7   8
 9  10  11  12

RANDARRAY — Generate Random Numbers

=RANDARRAY(rows, [cols], [min], [max], [whole_number])

=RANDARRAY(5, 1, 1, 100, TRUE)
→ 5 random whole numbers between 1 and 100 in column
→ Recalculates every time the sheet recalculates

Combining Dynamic Array Functions

Dynamic array functions become very powerful when nested.

Get a sorted unique list of regions:
=SORT(UNIQUE(B2:B10))

Filter data and then sort the result:
=SORT(FILTER(A2:C10, C2:C10>5000), 3, -1)
→ Shows only rows where Sales > 5000, sorted by Sales descending

The #SPILL! Error

A #SPILL! error means one or more cells in the intended spill range already contain data. Click the cell showing #SPILL! — Excel highlights the blocked cells with a dashed border. Clear those cells and the formula spills correctly.

Availability

Dynamic arrays replace many complex formulas that previously required Ctrl+Shift+Enter array entry or helper columns. FILTER alone replaces advanced filter tools, UNIQUE replaces manual duplicate removal, and SORT replaces the need to re-sort source data every time it changes. These functions make data processing faster and far less error-prone.