17 functions · Excel 365 / 2021+

Dynamic Array Functions

The 2020 revolution in Excel — arrays that spill automatically, no Ctrl+Shift+Enter needed. If you're on modern Excel, these functions replace half of what you used to do with lookup+helper+filter combinations.

💡 What is a "dynamic array"?

Before 2020, most Excel formulas returned one value into one cell. Dynamic arrays return many values that spill into surrounding cells automatically. Type =SORT(A1:A100) in cell C1 and 100 sorted values appear in C1:C100 — no dragging, no Ctrl+Shift+Enter, no messy helper columns.

The spilled range is a single connected result. Change the source, the spill updates automatically. Delete the source, the spill disappears. This is why they're called "dynamic".

Requires: Excel 365, Excel 2021, or Excel Online. Not available in Excel 2019 or earlier.

The essential four

Game changer

FILTER

=FILTER(range, condition, [if_empty])

Return only rows matching a condition. Replaces complex INDEX+SMALL+ROW array formulas that used to require Ctrl+Shift+Enter. The single most useful dynamic array function.

FILTER Complete Guide →

SORT / SORTBY

=SORT(range, [col], [order])

Sort a range without using the Sort dialog. Values update automatically as source data changes. SORTBY sorts by a different column than the one displayed.

SORT / SORTBY Guide →

UNIQUE

=UNIQUE(range)

Extract unique values from a range. Replaces the old "Remove Duplicates" wizard for formula-driven use. Combined with SORT, gives you a live unique-and-sorted list.

UNIQUE Complete Guide →

SEQUENCE

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

Generate a range of sequential numbers. Foundational for creating dynamic date arrays, indexed lookups, and mathematical models. Powers many advanced patterns.

SEQUENCE Complete Guide →

All 17 dynamic array functions

Powerful patterns to know

  • Unique sorted list: =SORT(UNIQUE(A2:A1000)) — one formula replaces a full workflow
  • Top 10 by revenue: =TAKE(SORT(FILTER(A2:C1000, C2:C1000>0), 3, -1), 10)
  • Filter with multiple conditions: =FILTER(data, (region="West")*(revenue>1000)) — multiply conditions for AND
  • Filter with either condition: =FILTER(data, (region="West")+(region="East")) — add conditions for OR
  • Dynamic date list: =SEQUENCE(30,,TODAY(),1) — next 30 days from today
  • Cascading dropdowns: =UNIQUE(FILTER(products, category=A1)) — data validation source

Frequently asked questions

Why do I see #SPILL! when I write a dynamic array formula?

Something's blocking the spill range — usually a value in one of the cells the formula wants to spill into. Delete anything in the spill area. Read our full #SPILL! error guide.

How do I reference a spilled range in another formula?

Use the spill range operator — a hash mark after the anchor cell: =SUM(A1#) refers to the entire spilled range from A1. This lets other formulas react as the spill grows or shrinks.

Are dynamic arrays available in Google Sheets?

FILTER, SORT, UNIQUE, and SEQUENCE all work in Google Sheets with identical syntax. Google Sheets was actually the first to popularize FILTER — Excel copied the feature. VSTACK, TAKE, DROP, CHOOSEROWS are Excel-only.

Do dynamic arrays slow down my workbook?

Rarely. A single spilled formula is faster than 100 individual cell formulas doing the same thing. If you notice slowness, it's usually because the source range is oversized (e.g., referencing entire columns like A:A instead of A1:A10000).

Can I still use "Ctrl+Shift+Enter" array formulas?

Yes — legacy CSE array formulas still work in modern Excel for backward compatibility. But there's no reason to write new ones. Dynamic array functions are more powerful, easier to write, and easier to maintain.

Get comfortable with dynamic arrays fast.

Ask the Add-in "convert this INDEX/SMALL/ROW formula to a dynamic array" — it rewrites legacy formulas to the modern equivalent.

Get the Add-in →