Dynamic Arrays & Modern Formulas

Every formula in Excel Fundamentals returned exactly one value into exactly one cell. This chapter introduces a genuinely different category: dynamic array formulas, which can return many values from a single formula typed into a single cell — and Excel automatically "spills" the rest of the results into the empty cells around it. This one shift changes how several everyday tasks — getting a distinct list, sorting, filtering — actually get done.

What "Spilling" Actually Means

Type a dynamic array formula into one cell, press Enter, and if the result is more than one value, Excel automatically fills — spills — that result into as many cells below and to the right as it needs, entirely on its own. You only ever type the formula once, into the top-left cell of what becomes the spill range.

This is a genuinely different mechanic from Excel Fundamentals' own fill handle (Chapter 2): dragging a fill handle creates separate copies of a formula, one per cell, each independently referencing its own row. A spilled array is one single formula, living in one cell, whose result simply occupies more than one cell on the grid. Delete the source formula in D2, and the entire spill range D2:D10 disappears together — you cannot edit or delete just one cell inside someone else's spill range.

If you've ever seen an older-style "array formula" entered with Ctrl+Shift+Enter (sometimes called a CSE formula) in a much older workbook, dynamic arrays are Excel's modern replacement — the same underlying idea of "one formula, many results," but no special key combination needed, and results now spill visibly onto the grid instead of being locked invisibly into one cell.

UNIQUE — Extracting Distinct Values

UNIQUE returns every distinct value from a range, in the order it first appears, with no duplicates:

Before dynamic arrays, getting a distinct list meant either the destructive Remove Duplicates tool (which permanently deletes rows from your actual data — a real risk if that wasn't what you meant to do) or a convoluted COUNTIF-based helper-column trick. UNIQUE does neither: the source data (Chapter 7's Transactions table, for instance) is left completely untouched, and the distinct list live-updates automatically the moment the source data changes.

SORT — Sorting a Range via Formula

SORT returns a range's values reordered, without touching the range itself:

The key difference from Excel Fundamentals' Sort dialog (Chapter 7): that dialog physically reorders the actual rows in place, once, as a one-time action. SORT as a formula creates a live, re-sorting view of the data elsewhere on the sheet — change a value in the source range, and the sorted spill range updates itself automatically, with the source data's own row order left completely alone.

FILTER — Filtering a Range via Formula

FILTER returns only the rows matching a condition, spilled into a new location, leaving the source data's rows untouched and fully visible:

This is a meaningfully different tool from Excel Fundamentals' AutoFilter (Chapter 7): AutoFilter hides non-matching rows in place, on the same table, and every filter has to be manually reapplied or adjusted by hand. FILTER instead extracts matching rows to wherever you put the formula, live-updating automatically as the source data changes, while the original table stays completely intact and unfiltered.

Combining Them: Nesting Dynamic Array Functions

Exactly like Excel Fundamentals Chapter 4's nested IF formulas, dynamic array functions can nest inside one another — the inner function's spilled array becomes the outer function's input directly:

The # Spill Reference Operator

Once a formula has spilled, another formula can refer to the entire spill range — not just its top-left cell — by adding a # after the reference:

D2# always means "the whole spill range that starts at D2, however large it currently is" — genuinely different from a plain range like D2:D9, which is a fixed address that wouldn't automatically follow the spill range if it later grew to D2:D12 or shrank to D2:D5.

Hands-On Exercises