Dynamic arrays let formulas return multiple values that automatically spill into adjacent cells. Available in Excel 365 and Excel 2021+.

Key Dynamic Array Functions

1. FILTER - Extract Matching Rows

=FILTER(A2:C100, B2:B100 > 1000)
Returns all rows where column B > 1000

=FILTER(A2:C100, (B2:B100 > 1000) * (C2:C100 = "Active"))
Multiple conditions (AND logic)

Result: Automatically shows all matching rows. Add/remove data, and results update instantly.

2. UNIQUE - Remove Duplicates

=UNIQUE(A2:A100)
Returns unique values from column A

=UNIQUE(A2:C100)
Returns unique rows considering all 3 columns

3. SORT - Automatic Sorting

=SORT(A2:C100, 2, -1)
Sort by column 2 (B), descending (-1)

=SORT(A2:C100, 3, 1)
Sort by column 3 (C), ascending (1)

4. SORTBY - Sort by Another Column

=SORTBY(A2:C100, B2:B100, -1)
Sort the range by column B values, descending

5. SEQUENCE - Generate Number Series

=SEQUENCE(10)
Generates 1, 2, 3, ... 10

=SEQUENCE(5, 3)
Generates 5 rows × 3 columns grid

=SEQUENCE(12, 1, 1, 1)
Months 1-12 for calendar building

6. RANDARRAY - Random Numbers

=RANDARRAY(10, 1, 1, 100, TRUE)
10 random integers between 1-100

Real-World Examples

Top 10 Sales by Region

=FILTER(
    SORT(A2:C100, 3, -1),
    B2:B100 = "West",
    "No results"
)
Then take first 10 rows manually or with other logic

Get Unique List of Active Customers

=SORT(UNIQUE(FILTER(A2:A100, B2:B100="Active")))

Dynamic Dropdown List

Cell A1: =UNIQUE(FILTER(Data!B:B, Data!B:B<>""))
Data Validation → List → =A1#

The # symbol references the entire spilled range!

The Magic of Spilling

Old way:

Write formula in A1
Drag formula down to A100
If data grows to 150 rows, manually drag again

Dynamic array way:

Write formula once in A1
Results automatically spill to A1:A100
Data grows to 150? Formula auto-expands to A150!

The # Reference Operator

Reference an entire spilled range:

A1 contains: =UNIQUE(Data!A:A)
This spills to A1:A50

In another cell:
=SUM(A1#)    Sums the entire spilled range
=COUNTA(A1#) Counts all spilled values

Common Gotchas

• #SPILL! error → Clear cells where results want to spill
• Not available in older Excel → Need Excel 365 or 2021+
• Performance: Complex nested dynamic arrays can be slow on huge datasets