Dynamic Arrays in Excel: FILTER, SORT, UNIQUE

What Is Dynamic Array in Excel? FILTER, SORT, and UNIQUE Explained

By Rinkesh Shah · Business Analyst, 8 years in data · Updated 2026-10-31

▶ Video coming soon

Dynamic array formulas return more than one value and "spill" the results into the cells below or beside them automatically.

What is spilling?

Type =UNIQUE(A2:A100) in one cell. Excel fills as many cells as it needs with the unique values. You do not copy the formula down. The blue border around the results is the spill range.

The main dynamic functions

FILTER: returns rows that meet a condition.
SORT and SORTBY: sort a list with a formula.
UNIQUE: removes duplicates from a list.
SEQUENCE: creates a list of numbers.
XLOOKUP: can return several columns at once.

Example: a sorted list of unique customers

=SORT(UNIQUE(A2:A500))

The # spill operator

If the formula in E2 spills, refer to the whole result with E2#. For example, =COUNTA(E2#) counts the spilled items, and the count updates when the list grows.

#SPILL! error

This means something is blocking the spill area. Clear the cells in the way and the formula works.

Who can use them?

Dynamic arrays are in Microsoft 365, Excel 2021 and later. Older versions show them as plain formulas with errors.

Worked example

Type =A2:A6*2 in one cell. In Microsoft 365 the answers spill into five cells automatically. Refer to the whole spill with =SUM(D2#).

Common mistakes

Typing in a cell with data below, giving #SPILL!.
Using old Ctrl+Shift+Enter habits.
Sending the file to someone with Excel 2019, where results show as {=...} arrays or errors.

Frequently asked questions

What is #SPILL!?

Something blocks the cells where the result wants to go.

What does the # sign mean?

D2# refers to the whole spilled range.

Which Excel has them?

Microsoft 365 and Excel 2021 onwards.

Practice this online, free

Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.

Practice these formulas: FILTER, SORT, UNIQUE, SEQUENCE.

Start Excel practice → Case studies