Excel Dynamic Arrays: FILTER, SORT, UNIQUE, and SEQUENCE Explained

Dynamic array functions, introduced in Microsoft 365 and Excel 2021, fundamentally changed how formulas work. Instead of returning a single value, these functions return a range of results that automatically "spill" into neighboring empty cells. No more Ctrl+Shift+Enter, no more dragging fill handles.

What Is a Spill Range?

When a dynamic array formula returns multiple values, they flow into adjacent cells automatically. This spill range is indicated by a blue border. The formula lives only in the top-left cell — the rest are read-only overflow.

To reference an entire spill range in another formula, use the spill operator #:

=SORT(A2:A20)       ← formula in D2, results spill into D3:D21
=D2#               ← references the entire spill range dynamically

FILTER: Extract Rows That Meet Conditions

FILTER is the dynamic replacement for complex combinations of INDEX/MATCH or helper columns.

=FILTER(array, include, [if_empty])

Example: Extract all rows from a sales table where Region is "North":

=FILTER(A2:D100, B2:B100="North", "No results found")

The if_empty argument prevents the #CALC! error when nothing matches.

Multiple Conditions with FILTER

AND logic (both conditions must be true — multiply conditions):

=FILTER(A2:D100, (B2:B100="North")*(C2:C100="Widget A"), "No results")

OR logic (either condition — add conditions):

=FILTER(A2:D100, (B2:B100="North")+(B2:B100="South"), "No results")

Combining FILTER with SORT

Extract the top 5 highest-revenue rows from the North region:

=SORT(FILTER(A2:D100, B2:B100="North"), 4, -1)

The 4 means sort by column 4 (Revenue), -1 means descending order.

SORT and SORTBY: Order Your Data Dynamically

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

Sort a product list alphabetically:

=SORT(A2:A50)

Sort a multi-column table by the 3rd column in descending order:

=SORT(A2:D50, 3, -1)

SORTBY for Multi-Level Sorting

SORTBY sorts one array based on the values in another — useful when the sort column is not part of the returned array:

=SORTBY(A2:B50, B2:B50, -1, A2:A50, 1)

This sorts by column B descending first, then by column A ascending as a tiebreaker.

UNIQUE: Deduplicate Lists Instantly

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

Extract a distinct list of products from a column:

=UNIQUE(C2:C100)

Return items that appear exactly once (not duplicated at all):

=UNIQUE(C2:C100, FALSE, TRUE)

Building a Dynamic Dropdown with UNIQUE

Use the spill range from UNIQUE as the source for a data validation dropdown:

1. Put =UNIQUE(C2:C100) in cell F2.
2. Select the dropdown target cell → Data → Data Validation → List.
3. In Source enter: =$F$2#

The dropdown now updates automatically whenever new products appear.

SEQUENCE: Generate Number or Date Series

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

Generate numbers 1 through 20:

=SEQUENCE(20)

Create a 4-row by 3-column multiplication table starting at 1, step 1:

=SEQUENCE(4, 3, 1, 1)

Generating a Date Series

Create a full calendar month starting from a date in cell A1:

=SEQUENCE(31, 1, A1, 1)

Format the results as dates. To limit to weekdays only, wrap in WORKDAY:

=WORKDAY(A1-1, SEQUENCE(23))

Practical Combined Example: Dynamic Leaderboard

Show the top 10 salespeople by revenue with their region, sorted highest first:

=TAKE(SORT(A2:D100, 4, -1), 10)

Where column A = Name, B = Region, C = Product, D = Revenue. TAKE (Excel 365) returns the first N rows of the sorted spill.

For Excel 2021 without TAKE:

=INDEX(SORT(A2:D100, 4, -1), SEQUENCE(10), {1,2,3,4})

Compatibility Notes

For Excel 2019 backward compatibility, use the classic INDEX / MATCH pattern or helper columns instead of dynamic array spills.