Dynamic arrays are one of those features that quietly change how you work in Excel, especially if you spend a lot of time filtering and sorting data by hand.
Instead of copying formulas down a column, you enter a single formula and Excel spills the results into as many cells as needed.
In this tutorial we will walk through three core dynamic array functions: FILTER, SORT and UNIQUE, using a simple sales table as an example.
Sample data we will use
Imagine a table named Sales with these columns: Date, Region, Product, Amount.
The examples below assume your data is formatted as an Excel Table called Sales, which makes the formulas easier to read and automatically expandable.
Date Region Product Amount
2026-01-02 East Apples 120
2026-01-03 West Oranges 85
2026-01-04 East Bananas 140
...
FILTER: pull out only the rows you care about
The FILTER function returns only the rows that meet your criteria and spills the matching rows into the cells below the formula.
The basic syntax is:
=FILTER(array, include, [if_empty])To show only sales from the East region, place your cursor in an empty cell (for example H3) and enter:
=FILTER(Sales, Sales[Region]="East", "No East sales found")Excel will spill all East rows into the cells below, and if no rows match, you will see the friendly message instead of a #CALC! error.
Filtering with multiple conditions
You can combine conditions using multiplication (for AND) or addition (for OR) because FILTER evaluates a Boolean include array.
For example, East region and Amount greater than 100:
=FILTER(
Sales,
(Sales[Region]="East") * (Sales[Amount]>100),
"No matching rows"
)The parentheses make it easier to read, and the formula automatically expands when you add more rows to the Sales table.
UNIQUE: get a clean list of distinct values
The UNIQUE function takes a range and returns a list of distinct values, again as a spilled dynamic array.
It is perfect when you need a list of regions, product names, or categories to feed a drop‑down list or a dashboard.
=UNIQUE(Sales[Product])This one formula gives you every product that appears in the Sales table, without duplicates, and it updates as soon as you add a new product to the data.
Horizontal and vertical lists
By default, UNIQUE returns results in the same orientation as the source, but you can wrap it in TRANSPOSE if you prefer a horizontal list for headings or scenarios.
=TRANSPOSE(UNIQUE(Sales[Product]))SORT: keep your outputs automatically ordered
The SORT function rearranges an array into ascending or descending order without touching the original data.
Its syntax is:
=SORT(array, [sort_index], [sort_order], [by_col])To sort the spilled Sales rows in descending order by Amount, you can wrap the previous FILTER formula:
=SORT(
FILTER(Sales, Sales[Region]="East", "No East sales"),
4, -1
)Here 4 refers to the fourth column in the filtered array (Amount), and -1 means descending order.
Combining FILTER, UNIQUE and SORT
The real power comes when you start nesting these functions to build small “engines” inside your workbook.
A very common pattern is to extract a sorted list of unique products that have at least one sale:
=SORT(
UNIQUE(
FILTER(Sales[Product], Sales[Amount]>0)
)
)This gives you a clean, alphabetically sorted list of products that actually appear in the data, which you can then use for data validation, slicers, or dashboard controls.
Dynamic array functions like FILTER, SORT and UNIQUE are available in Excel for Microsoft 365 and Excel 2021+, so if you do not see them, you are probably on an older version.
