Advanced Excel · Interactive lesson 2 of 12
Dynamic array formulas
One formula, many cells — a dynamic array "spills" its whole result across as many cells as it needs, and keeps them updated as the source data changes. No more copying a formula down and hoping the range grew with it.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialDynamic Array Formulas in Excel: FILTER, SORT and UNIQUESkill check
Try these first:
- Explain what "spill" means, and what the
#next to a cell reference likeD2#does. - Return a deduplicated list of values from a column with one formula.
- Filter a list down to only the rows matching a condition, without a helper column.
- Generate a numbered list of five rows without typing 1 through 5.
All familiar? Skip ahead to A3 — Formula Engineering with LET.
Concept teach
What you're learning
- Spill ranges
- A formula that returns more than one value automatically fills — "spills" into — the empty cells below or beside it. Type it once, in one cell.
UNIQUE=UNIQUE(B2:B7)— every distinct value in a range, in the order it first appears, with no duplicates.SORT/SORTBY=SORT(range,[1|-1])sorts a range or array directly;=SORTBY(range,keyrange)sorts one range by the values in another — handy when the sort key isn't the column you're returning.FILTER=FILTER(A2:A7,C2:C7>30)— returns only the rows of A where the parallel condition on C is true. The condition is itself a spilled array of TRUE/FALSE.SEQUENCE=SEQUENCE(6)generates 1 through 6 down a column — useful for a Row ID column that never needs re-typing.TAKE/DROP=TAKE(array,3)keeps the first 3 items (or last 3, with-3);DROPdoes the opposite — useful chained onto aSORTfor a quick "top N."- The spill operator
# D2#refers to a spilled array's entire current size, wherever a formula inD2spilled it — so=COUNT(D2#)always reflects how many values are there right now, even after the source data changes.
Where to find it in Excel
Formulas›Lookup & Reference›FILTER · SORT · UNIQUE
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Regions | Total | |||||
| 2 | Ana | North | East | 3,250 | |||||
| 3 | Ben | South | North | 6,900 | |||||
| 4 | Chris | West | South | 5,400 | |||||
| 5 | Dana | North | West | 4,880 | |||||
| 6 | Eli | East | |||||||
| 7 | Fay | South | |||||||
| 8 | Gus | West | |||||||
| 9 | Hana | North |
Swipe sideways to see the whole window.
- 1Formulas tab.
- 2FILTER, SORT, SORTBY, UNIQUE, TAKE and DROP are under Lookup & Reference.
- 3SEQUENCE is under Math & Trig.
- 4Type the formula once in D2; the results spill into the cells below (blue border).
- 5Refer to the whole spill with D2#, for example =SUMIFS(…, B2:B9, D2#).
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Six transactions below. Write the array formula for each highlighted cell — every one spills instead of being copied down.
Independent extension
A live customer list
Using a customer or sales list of your own, build a live, sorted list of only the active (or above-threshold) records with SORT(FILTER(...)) in one formula. Add a row to the source data and confirm the spill grows on its own — that's the whole point of a dynamic array.
Spot a mistake or have an idea for this lesson? Send feedback