Dynamic Arrays

🟢 Think back to the old way of working in Excel. You wrote one formula in the top cell, then dragged it down — copying it into ten, a hundred, a thousand cells by hand. Every cell held its own little copy. Tiring, and easy to get wrong.

A dynamic array turns that on its head. You plant one formula in one cell — a single seed — and it grows on its own to fill exactly as many cells as the answer needs. If the answer is five names, five cells fill. If it is fifty, fifty fill. Excel calls this spilling: the result “spills” out of the seed cell into the neighbours below and beside it. You never drag anything.

This is the biggest upgrade to Excel in decades, and it arrived with Microsoft 365 (and Excel 2021). Three new helpers do most of the magic, and each has a plain-English job:

• FILTER is a sieve — pour your whole table in, and only the rows that match your rule fall through. “Show me just the North orders.”
• UNIQUE is a de-duplicator — feed it a long column full of repeats and it hands back each value once. “List every salesperson, no repeats.”
• SORT is an auto-arranger — it lines your values up biggest-to-smallest (or A–Z) and keeps them that way.

Because the answer is alive, it updates itself. Add a new North order to the table and your FILTER result grows by a row on its own — no re-dragging, no broken ranges. One seed, always fresh. That single idea — a formula that produces a whole block of answers — is what makes the modern Excel feel effortless.

Two quick maps: first the whole dynamic-array family at a glance, then a decision tree for picking the right spill function.

Every dynamic array works the same way: you write one formula, press Enter, and the answer spills from that single cell into a block called the spill range, ringed by a thin blue border. The top-left cell holds the real formula; the rest are its overflow. Delete that top-left cell and the whole spill vanishes. This one rule sits under everything below.

The # operator — the spill reference. Because a spill can grow or shrink, you need a way to say “the whole spilled block, however big it is right now”. That is the hash sign. If a list spills from A2 downwards, then A2# means the entire spill. Write =COUNTA(A2#) and it always counts the live list, even after it grows by ten rows.

FILTER — the sieve. Its shape is FILTER(array, include, [if_empty]). The array is the block you want back, include is a TRUE/FALSE test the same height as the array, and the optional if_empty is what to show when nothing matches. Build your first one step by step:

Combine tests with * for AND and + for OR: =FILTER(A2:C99,(B2:B99="North")*(C2:C99>500)) keeps only the North orders worth over ₹500.

SORT and SORTBY — the auto-arrangers. SORT(array, [sort_index], [sort_order], [by_col]) re-orders a block; sort_order is 1 for ascending and −1 for descending. =SORT(A2:B20,2,-1) sorts by the 2nd column, largest first — an instant leaderboard. SORTBY(array, by_array1, [order1], …) sorts one block by another without counting columns: =SORTBY(A2:A20,B2:B20,-1) lines the names up by their scores, highest first.

UNIQUE — the de-duplicator. UNIQUE(array, [by_col], [exactly_once]) returns each value once. =UNIQUE(A2:A99) spills a clean list of salespeople. To count how many distinct people there are, wrap it in COUNTA: =COUNTA(UNIQUE(A2:A99)) — UNIQUE spills the distinct names and COUNTA tallies them. Set the third argument to TRUE and UNIQUE returns only the values that appear exactly once, quietly hiding every repeat.

SEQUENCE and RANDARRAY — grid builders. SEQUENCE(rows, [cols], [start], [step]) spills a ready-made ladder of numbers: =SEQUENCE(12,1,1,1) fills 1…12 down a column — perfect for month numbers or serials — and =SUM(SEQUENCE(10)) quietly totals 1 to 10. RANDARRAY([rows], [cols], [min], [max], [whole]) spills random numbers, handy for quick test data: =RANDARRAY(5,1,1,100,TRUE).

XLOOKUP and XMATCH — spill-aware lookups. XLOOKUP(lookup, lookup_array, return_array, [if_not_found]) replaces VLOOKUP and never breaks when columns move. Feed it a whole column of lookup values and it spills one answer for each row. XMATCH(lookup, lookup_array) returns the position of a match — the modern MATCH, also spill-aware.

Stacking them up. The real power is nesting, read inside-out: =SORT(UNIQUE(FILTER(B2:B99,A2:A99="North"))) — FILTER keeps the North rows, UNIQUE drops the repeats, and SORT alphabetises what is left. One formula, a finished report that refreshes itself.

LET — name your working. LET(name1, value1, [name2, value2, …], calculation) lets you name a result once and reuse it, so long formulas read like sentences and run faster. =LET(n, FILTER(C2:C99,A2:A99="North"), SUM(n)/COUNT(n)) names the North figures n, then averages them — FILTER runs just once, not twice.

LAMBDA — build your own function. LAMBDA(parameter, …, calculation) turns a formula into a reusable function you name in the Name Manager. Define GST as =LAMBDA(x, x*0.18) and thereafter =GST(A2#) spills 18% of every amount in the list — your first taste of making your own tools.

Modern text splitters. TEXTSPLIT(text, col_delimiter, [row_delimiter]) spills one cell into many by a separator: =TEXTSPLIT("Amit,North,500",",") spills three cells across. TEXTBEFORE(text, delimiter) and TEXTAFTER(text, delimiter) grab the part before or after a marker — ideal for splitting name@site into the name and the site.

Keep this quick map of the family beside you:

These functions are new, so which ones you have depends on your version of Excel:

Here is the whole idea in one picture — a single seed formula spilling into the block of cells its answer needs:

Dynamic arrays feel magical until a formula refuses to spill. Ninety per cent of the pain comes from a handful of causes — here they are, with the fixes the pros reach for.

The sandbox engine runs classic Excel, so here you reach the SAME answers a dynamic array would spill — using functions every version understands. Each drill names its spill-formula twin.