Dynamic Arrays & LAMBDA

Excel Mastery Track · Yellow belt · Node EX1.03 · ~6–8 hours · Microsoft 365 assumed

For thirty years the deal you struck with Excel was one formula, one cell. If you wanted the same calculation for a thousand rows you copied it a thousand times, and your workbook filled with helper columns (a scratch column to concatenate a key, another to flag a condition, a third to rank a value) each one a place a formula could be dragged one row short, overwritten, or left stale. The 2020 calculation engine tore that deal up. A single formula can now spill: you write it once in the top-left cell and it pours its answers across as many cells as the result needs, resizing itself automatically when the source data grows or shrinks. One formula, many answers, no dragging, no helper columns. This is not a cosmetic upgrade, it is a different way of thinking about a spreadsheet, closer to writing a query than to filling in a ledger.

Here you'll get fluent in that new grammar. First comes the spill itself, and the # operator that lets one formula's output feed the next. Then the six workhorse array functions (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY), which replace the pivot-table-and-helper-column dance for most day-to-day shaping. Then the iterators BYROW, BYCOL, MAP, SCAN and REDUCE, which run a mini-calculation over every row, column or element without a loop. Last come the two ideas that turn a competent analyst into a formula engineer. LET names the intermediate steps of a long formula so it reads like prose. LAMBDA lets you package a formula into your own reusable named function, a CAGR or an XNPV you define once and call everywhere.

One version note, and then we'll leave the subject alone. Everything here assumes Microsoft 365. Excel 2021 is enough for FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY, XLOOKUP, XMATCH and LET. LAMBDA and its iterators, CHOOSECOLS, TAKE, DROP, VSTACK, HSTACK and TEXTSPLIT need Excel 2024 or Microsoft 365, and GROUPBY and PIVOTBY need Microsoft 365 alone. On these builds dynamic arrays are native. You press Enter and the result spills, and the old Ctrl+Shift+Enter isn't needed. On Excel 2019 and earlier none of this exists, and you're back to the legacy array formulas and helper columns you're about to retire. Where a function is younger than the rest, the text flags it in a line.

Learning objectives

By the end you can:

1. Explain the spill from first principles, why one formula now returns a whole array, where the "real" formula lives, and how the spill range resizes itself when data changes.
2. Reference a spilled range with the # spilled-range operator (A2#) so that one dynamic formula feeds another and the whole chain stays live.
3. Diagnose and clear a #SPILL! error, naming the three things that block a spill and the exact UI step that finds the obstruction.
4. Write FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE from memory, including multi-condition FILTER (AND with *, OR with +) and the if_empty argument.
5. Use RANDARRAY correctly and state precisely why its volatility makes it a tool to reach for deliberately, not by habit.
6. Run a per-row, per-column, per-element, or accumulating calculation with BYROW, BYCOL, MAP, SCAN, and REDUCE, and say when each is the right iterator.
7. Refactor a long, unreadable formula with LET by naming its intermediate results, gaining both readability and speed (each name computes once).
8. Author a reusable LAMBDA, register it in Name Manager, and call it like a built-in function, building a CAGR helper as the worked case.
9. Replace helper columns and legacy Ctrl+Shift+Enter (CSE) array formulas with single spilling formulas, and build a refreshable report block that updates itself when its source table changes.

Prerequisites & connections

Builds on. EX1.01 (Formulas II) planted the array mindset with SUMIFS/COUNTIFS and the idea that a formula can act on a whole range at once. Now that array gets to escape the single cell and spill. EX1.02 (Lookups Mastery) is the direct parent. You met XLOOKUP returning an entire record there, with CHOOSECOLS/TAKE/DROP shaping it. Those were dynamic arrays in miniature, and here they become the main event. You also need the reference habits of EX0.02, meaning $ anchoring and especially LET and Name Manager, which we take further here. And you need the Table habit of EX0.01. A spilling formula that reads from a real Table (Ctrl+T) grows and shrinks with the Table automatically, and that's half the magic.

Feeds forward. Pass the mastery check below and EX2.01 (Data Tools & Integrity) opens up. EX2.01 uses UNIQUE and SORT to build self-maintaining data-validation lists, including the sorted dropdown source you'll meet in the UNIQUE section, and FILTER behind dynamic dashboards. EX2.02's sensitivity tables are SEQUENCE-driven grids at heart. On the analyst track, dynamic arrays are how a modern model stays refreshable. M2.05's comparable-company screens are one big FILTER+SORT, and M3.08's scenario blocks name their assumptions with LET. A firm-wide library of LAMBDA helpers (a house CAGR, XNPV, XIRR, or day-count function) is how a team stops re-deriving the same formula in every workbook. The habit you build here, never a helper column where a spill will do and never a magic formula where a named LAMBDA belongs, is the spine of professional model design (EX7.01).

Core teaching content

Spill: one formula, many answers

A traditional Excel formula is a function from its inputs to one value, deposited in the one cell that holds it. A dynamic-array formula is a function from its inputs to an array of values, and Excel needs somewhere to put them. Its rule is simple and worth memorising. The formula lives in one cell, the anchor, which is always the top-left. It computes an entire array and then spills that array down and to the right into the cells the result needs. The filled cells hold no formulas of their own. Everything in them is overflow from the single formula in the anchor. Click any spilled cell and the formula bar shows the formula greyed out. Click the anchor and it shows in black, editable. Delete the anchor and the whole spill vanishes, and you can't delete just one spilled cell.

Think of the anchor as a tap and the spill range as the water it pours. You don't fill each cup by hand. You open one tap and it fills as many cups as there is water. When the source table gains a row, the tap pours one cup further and the spill resizes itself.

That's the property that kills the dragged helper column. A dragged formula is a fixed number of cups you filled once and must remember to refill, while a spill is a tap that always matches the data.

Type a formula that returns many values and press Enter. You don't need Ctrl+Shift+Enter. The simplest is =A2:A9, which just echoes a range. With the anchor selected, Excel draws a faint blue border around the whole spilled result, and that border marks the spill range. Now a richer example. With customer names in A2:A9, =SORT(A2:A9) in C2 spills the eight names, sorted, down C2:C9, all from the one formula in C2. Add a ninth customer to the source and the sorted list becomes nine names with no edit. So the visual tell of a dynamic array is that blue border, plus the greyed formula in the overflow cells.

Everything you'll meet from here on spills. A FILTER returns as many rows as match, a UNIQUE as many distinct values as exist, and a SEQUENCE(12) twelve numbers. You stop asking "how many cells will this fill?" and start asking "what array do I want?", and Excel sizes the output. Moving from managing cells to declaring results is the jump from white belt to yellow belt.

The # spilled-range reference

If one formula's output is a live, resizing array, then any formula that consumes it can't point at a fixed range like C2:C9. Tomorrow the spill might be C2:C10, and a fixed reference would miss the new row or, worse, include a stale one. Excel's answer is the spilled-range operator. Append # to the anchor cell and you get a reference to the entire current spill, however big it is right now. If a SORT spills from C2, then C2# means "all of it", and it stretches and shrinks as the spill does.

Suppose C2 holds =SORT(FILTER(A2:D9, C2:C9="Active"), 4, -1) and spills a block. Then:

• =ROWS(C2#) counts the rows the spill currently occupies, a live count of matches.
• =SUM(CHOOSECOLS(C2#, 4)) sums the fourth column of the spilled block, whatever its height.
• =C2# in another location re-spills a live copy of the whole result.

You can also build a # reference with the mouse. Start a formula, click the anchor cell, and Excel offers to insert C2# (the "spill" reference). Or just type the # yourself after the anchor address. This operator is what lets you chain dynamic arrays. One formula produces a block, the next reads that block by its # reference, and a change at the source ripples through the whole chain with nothing to re-drag. Naming the spill helps too. Select the anchor, press Ctrl+F3, and define a name whose Refers to is =Sheet1!$C$2#. Now the whole live block has a readable name.

BYROW, BYCOL, MAP, SCAN

The five functions above shape arrays. This next family iterates over them. Each one runs a small calculation once per row, per column or per element, and that calculation is written as a LAMBDA (you get a preview here and the full treatment below). Together they replace the classic "add a helper column, write a formula, drag it down" pattern with a single spilling call.

BYROW(array, LAMBDA(row, calc)) collapses each row to one value. Take a 3×3 block of numbers in A1:C3, with rows 10,20,30 / 40,50,60 / 70,80,90. The row sums are one formula:

=BYROW(A1:C3, LAMBDA(r, SUM(r)))     → spills {60; 150; 240}

BYCOL is the column-wise twin: =BYCOL(A1:C3, LAMBDA(c, SUM(c))) spills {120, 150, 180} across a row. Swap SUM for MAX and BYROW returns each row's maximum, {30; 60; 90}.

MAP(array1, [array2, …], LAMBDA(a, [b,] calc)) transforms every element one-to-one, optionally combining several equal-shaped arrays. Squaring each element of {2,3,4,5}:

=MAP({2,3,4,5}, LAMBDA(x, x^2))     → {4, 9, 16, 25}

Reach for MAP when a per-cell calculation needs an IF, a lookup or a LET, logic too rich for a plain arithmetic array formula, applied element by element.

SCAN(initial, array, LAMBDA(acc, value, calc)) carries an accumulator through the array and returns every intermediate result. That's how you get a running total, and a running maximum or a compounding index works the same way. Here's a running total of {120, 90, 150, 60, 180} starting from 0:

=SCAN(0, {120,90,150,60,180}, LAMBDA(acc, v, acc+v))     → {120, 210, 360, 420, 600}

REDUCE(initial, array, LAMBDA(acc, value, calc)) is SCAN's sibling that keeps only the final accumulator. Give REDUCE a multiplying LAMBDA over {1.05, 1.08, 1.12} and it returns the single compounded factor 1.27008, a 27.008% cumulative gain. SCAN would have returned the running factors along the way. The rule of thumb is SCAN when you want the path and REDUCE when you want the destination. Between them, these iterators mean you'll almost never again write a formula in one cell and drag it down a column. You declare the per-element logic once and let it spill.

LET: name your intermediate steps

A long formula is unreadable for two reasons. The same sub-expression appears several times, so you have to check that each copy is identical. And the intermediate quantities have no names, so you have to infer what SUM(FILTER(D2:D9,C2:C9="Active")) means. LET fixes both. It lets you assign names to values inside a formula, then use those names in a final calculation:

=LET(name1, value1, name2, value2, …, final_calculation)

Read it as pairs of a name and what it equals, ending in one expression that uses the names. You get a real performance gain too. Each name is computed once, even if you use it five times, whereas repeating the sub-expression makes Excel evaluate it five times.

Say you want the average revenue of active customers. Name the filtered array once and reuse it for both the total and the count:

=LET(
   active, FILTER(D2:D9, C2:C9="Active"),
   total,  SUM(active),
   n,      ROWS(active),
   total / n
)

Here active is the six active revenues and total is their sum (₹16,035,000). n is their count (6), and the result is their average, ₹2,672,500. FILTER ran a single time. Without LET you'd repeat the whole FILTER(...) inside both SUM(...) and ROWS(...), computing it twice and daring the reader to check the two copies match. Naming intermediates is how a formula becomes auditable. Indent the pairs and a fifteen-function formula reads like a short paragraph. Press Alt+Enter inside the formula bar to add line breaks, which don't affect the result.

LAMBDA: your own reusable functions

Every function you've used, such as SUM, XLOOKUP and FILTER, is a named piece of logic that takes arguments and returns a result. LAMBDA lets you write one. Its form mirrors LET, a list of parameter names followed by the calculation that uses them:

=LAMBDA(param1, param2, …, calculation)

A LAMBDA on its own, sitting in a cell, does nothing. It's a definition, and a definition doesn't run until something calls it. You make it reusable by giving it a name in Name Manager (Ctrl+F3 → New), putting the parameter names in your head and the LAMBDA in the Refers to box. Once it's named CAGR, you call it like a built-in: =CAGR(4200000, 9800000, 5).

Take a CAGR function as the worked case. Compound annual growth rate asks at what steady annual rate begin would have to grow, over years years, to reach end. Its formula is (end/begin)^(1/years) - 1. Package it:

1. Ctrl+F3 to open Name Manager, click New.
2. Name: CAGR.
3. Refers to: =LAMBDA(begin, end, years, (end/begin)^(1/years) - 1)
4. OK, then Close.

Now anywhere in the workbook, =CAGR(4200000, 9800000, 5) returns 0.184664… (18.47%). Define it once, and every model that needs a growth rate calls the same audited function. Fix a bug in the definition and you've fixed it everywhere. That's how firms build a house library of LAMBDAs (CAGR, XNPV, a DAYCOUNT, a GST splitter), kept in a shared workbook or, in newer builds, a linked LAMBDA library add-in. LAMBDA is also what you pass to the iterators from the previous section. The LAMBDA(r, SUM(r)) inside BYROW is an anonymous lambda, used once and never named. So name a lambda when you'll reuse it, and leave it anonymous for a one-off.

VSTACK, HSTACK, GROUPBY and PIVOTBY

Four younger functions finish off the helper column. VSTACK stacks ranges or arrays on top of one another and HSTACK sets them side by side, so =VSTACK(Q1!A2:C40, Q2!A2:C40) builds one list from two sheets without copying anything. GROUPBY aggregates a value column by one or more key columns in a single spilled formula, =GROUPBY(Sales[Region], Sales[Revenue], SUM). PIVOTBY does the same across rows and columns at once. It's a formula pivot that recalculates like any other formula instead of waiting for a refresh. Treat them as the formula-side partner of PivotTables, which get their own treatment later. A PivotTable is still the quicker tool for exploring, and a GROUPBY is the better one for a fixed summary that feeds a model.

On versions, VSTACK and HSTACK are in Excel 2024 and Microsoft 365. GROUPBY and PIVOTBY are in Microsoft 365 only, and so are the REGEXTEST, REGEXEXTRACT and REGEXREPLACE text functions and Python in Excel, which runs pandas code inside a cell (as of October 2026, check on Microsoft's function pages). The quant-code track teaches the Python itself.

Killing helper columns and CSE arrays

A helper column is a column of dragged formulas whose only job is to hold an intermediate result for another formula: a concatenated key, a TRUE/FALSE flag, a rank. Every helper column is a maintenance liability. It can be sorted out of alignment, dragged one row short of the data, overwritten, or left stale when rows are added. Dynamic arrays let the intermediate result live inside one formula (via LET) or spill from one formula (via FILTER/SORT/MAP). The helper column disappears, and a whole class of silent errors goes with it.

The other thing being retired is the legacy CSE (Ctrl+Shift+Enter) array formula. Before 2020, to make a formula work over a whole array you committed it with Ctrl+Shift+Enter, and Excel wrapped it in curly braces {=…}. Forget the chord and the formula returned a single wrong value. In Microsoft 365 this is obsolete. Array formulas spill natively on a plain Enter, and typing Ctrl+Shift+Enter is just an old reflex. It still works, but it shows you're thinking in the pre-2020 model.

A related leftover is the implicit intersection operator @. When you open an old workbook in M365, Excel may put @ in front of formulas, as in =@A2:A9, to force the old single-value behaviour and stop them spilling into cells that used to be blank. Delete the @ and the formula spills the modern way. Read @ as "this formula is being kept non-spilling on purpose", and migrated workbooks will make sense.

Fluency shows up at a particular moment. You catch yourself about to drag a formula down a column, or about to add a scratch column, and you stop. Could one spilling formula (a FILTER, a MAP, a BYROW, a LET) do the whole job from the anchor cell? Nine times in ten it can. The workbook you end up with has fewer cells and no drag-length bugs, and it updates itself when the data changes. The mini-project asks you to build a workbook like that, refreshable by construction.

Common mistakes & how experts think differently

1. #SPILL! because the spill range is not empty, the signature error of dynamic arrays. A dynamic formula needs clear cells to spill into. Even a stray number or a leftover label inside the range the result wants is enough to stop it, and so is a merged cell. Excel refuses and returns #SPILL! in the anchor. Beginners stare at it. The expert clicks the yellow warning triangle beside the cell and chooses Select Obstructing Cells, which highlights whatever is in the way, then clears or moves it. The three classic blockers are a value typed into the spill zone, a merged cell overlapping it, and a spill that would run off the edge of the worksheet. So build the reflex. Before you write a spilling formula, make sure the space below and to the right of the anchor is empty.
2. Referencing a spill by a fixed range instead of A2#. You write =SORT(FILTER(...)) in C2, it spills to C2:C7, and you sum it with =SUM(C2:C7). Tomorrow a new match makes the spill C2:C8, and your SUM misses the eighth row without a word of warning. The expert never hardcodes the extent of a spill. They write =SUM(C2#), which always covers the whole current spill however it resizes. Hardcode the range and you've thrown away the best property dynamic arrays have, which is that they resize themselves.
3. Overusing volatile RANDARRAY (and RAND/RANDBETWEEN). RANDARRAY recalculates on every worksheet change, meaning every keystroke that commits a cell, every sort, every F9. Sprinkle a few across a live model and every edit re-rolls all of them and drags recalculation with it. The expert uses RANDARRAY to generate test or scenario data, then freezes it straight away with a copy and Paste Special ▸ Values (Ctrl+Alt+V, then V), so the numbers stop moving. Treat volatility as a feature you switch off the moment you have what you need. Don't leave it running as a background hum.
4. Forgetting that a LAMBDA must be named to be reusable. Type a LAMBDA straight into a cell (=LAMBDA(b,e,y,(e/b)^(1/y)-1)) and you get the unhelpful text #CALC! or the lambda's own signature, because you've defined a function without calling it. Either call it on the spot by appending the arguments (=LAMBDA(b,e,y,(e/b)^(1/y)-1)(4200000,9800000,5)) or, for anything you'll reuse, register it in Name Manager and call it by name. Beginners decide "LAMBDA is broken". The expert knows a definition doesn't run until it's called.
5. Still typing Ctrl+Shift+Enter. In M365 the chord is unnecessary, because arrays spill on plain Enter, and reaching for CSE is a tell that you're still thinking in the pre-2020 single-cell model. Worse, a habit of wrapping things in the old {} array-entry can hide cases where a modern spill would have shown you a #SPILL! you needed to see. Press Enter, let it spill, and read the result.
6. Treating a spilled result as if it were typed-in values. You can't edit, delete or overwrite a single spilled cell. Only the anchor can be changed. Beginners try to "fix" one cell of a SORT output and run into the #SPILL!-adjacent block-locked behaviour, or they delete the anchor and lose the lot. The expert edits the formula and leaves its overflow alone. If they need static values, they convert on purpose with Paste Special ▸ Values.
7. Nesting instead of naming. A formula that repeats FILTER(D2:D9,C2:C9="Active") three times is slower, because it runs three evaluations. It's also harder to read, with three copies to reconcile. The expert wraps it in a LET and names it once. As a rule, if a sub-expression appears twice it wants a LET name, and if the whole formula will be reused across cells or workbooks it wants a LAMBDA.

Behind every one of these mistakes sits the same habit. An expert declares a result and lets Excel size and refresh it, naming every intermediate that repeats. Beginners manage cells and drag formulas. Professionals write one spilling, named, self-resizing expression and move on.

Worked examples

Worked example 1: Top customers with SORT + FILTER, spilled in one formula (India)

The task. A sales lead keeps a customer master with eight accounts, each tagged Active or Dormant, with annual revenue in rupees. She wants a live "Top 3 active customers by revenue" block that re-ranks itself whenever a revenue figure is updated or an account's status flips. She doesn't want helper columns or manual sorting. One formula should do it.

Step 1, the source table (a real Table, Ctrl+T, occupying A1:D9):

     A                 B        C          D
1    Customer          Region   Status     Revenue
2    Aarav Traders     West     Active     1850000
3    Bhavna Exports    North    Active     2340000
4    Chetan & Co       West     Dormant    1120000
5    Divya Retail      South    Active     3275000
6    Esha Foods        North    Active     2980000
7    Farhan Textiles   West     Dormant    3410000
8    Gita Motors       South    Active     2050000
9    Hari Steel        North    Active     3540000

There's a trap planted here on purpose. Farhan Textiles has ₹34,10,000, the second-highest revenue in the whole list, but it's Dormant. A naive "sort by revenue, take top 3" would wrongly promote it, so the FILTER has to run before the SORT.

Step 2: filter to active accounts. FILTER keeps only the rows whose status is Active, preserving source order:

=FILTER(A2:D9, C2:C9="Active")

This spills the six active rows: Aarav, Bhavna, Divya, Esha, Gita, Hari. Chetan and Farhan, both Dormant, are gone.

Step 3, sort the survivors by revenue, descending. Wrap the FILTER in SORT, sorting by the 4th column (Revenue) with order -1:

=SORT(FILTER(A2:D9, C2:C9="Active"), 4, -1)

Now the six active rows are ordered Hari (₹35,40,000), Divya (₹32,75,000), Esha (₹29,80,000), Bhavna (₹23,40,000), Gita (₹20,50,000), Aarav (₹18,50,000).

Step 4: take the top three. TAKE(array, 3) keeps the first three rows of the sorted block. The complete one-formula answer:

=TAKE(SORT(FILTER(A2:D9, C2:C9="Active"), 4, -1), 3)

This spills a 3-row × 4-column block. If you only want the name and the revenue, carve those two columns out with CHOOSECOLS:

=CHOOSECOLS(TAKE(SORT(FILTER(A2:D9, C2:C9="Active"), 4, -1), 3), 1, 4)

Step 5, the verified result (every figure recomputed):

Check the sum longhand. 35,40,000 + 32,75,000 = 68,15,000, and adding 29,80,000 gives ₹97,95,000. Now look at what ordering the operations correctly bought you. Farhan's ₹34,10,000, larger than every entry except Hari's, is absent, because it was filtered out before the sort ever ran. Flip Farhan's status to Active in the source and the block re-ranks it into second place straight away, with no formula change. That self-refresh is what you were after all along.

Step 6, read it. That's one formula built from four functions, with zero helper columns. The result resizes and re-sorts itself on any edit to the source. The old way took four manual steps: a RANK helper column, an IF flag column, a manual Data ▸ Sort, then copying the top three. All of it went stale the moment revenue changed. =TAKE(SORT(FILTER(...),4,-1),3) does the whole job and stays live.

Worked example 2: A CAGR LAMBDA and a SEQUENCE year axis (US)

The task. A US SaaS company reported six years of revenue. Build a reusable CAGR function to measure its compound annual growth, and lay out the year axis with SEQUENCE. Then use the CAGR to project a smooth revenue path, and prove that the projection lands back on the actual final year.

Step 1, the reported revenue series (dollars), in A1:B7:

     A       B
1    Year    Revenue
2    2019    4200000
3    2020    5100000
4    2021    5950000
5    2022    7300000
6    2023    8450000
7    2024    9800000

Step 2, build the CAGR LAMBDA in Name Manager. Press Ctrl+F3 → New. Name it CAGR, and in Refers to enter:

=LAMBDA(begin, end, years, (end/begin)^(1/years) - 1)

CAGR asks what constant annual rate would carry the first year's revenue to the last. From 2019 to 2024 that's 5 growth intervals. There are six data points and five steps between them, a classic off-by-one to get right.

Step 3: call it. With begin in B2 and end in B7:

=CAGR(B2, B7, 5)

Check it by hand. 9,800,000 / 4,200,000 gives 2.33333…, and raising that to the 1/5 power and subtracting 1 gives 0.184664…, or 18.47% compound annual growth. Revenue grew 2.333× over five years, which works out to 18.47% a year compounded.

Step 4, lay out the year axis and the exponent row with SEQUENCE. A horizontal year header from 2019, six wide:

=SEQUENCE(1, 6, 2019, 1)      → 2019, 2020, 2021, 2022, 2023, 2024

For a quick check that SEQUENCE did what you meant, sum it. =SUM(SEQUENCE(1,6,2019,1)) = 2019+2020+2021+2022+2023+2024 = 12,129. Next comes a period-index row, 0,1,2,3,4,5, to drive the projection:

=SEQUENCE(1, 6, 0, 1)         → 0, 1, 2, 3, 4, 5

Step 5, project a smooth path with the CAGR. Put the CAGR in H1 with =CAGR(B2,B7,5). The projected revenue for period n is begin × (1 + CAGR)^n. Feed the whole exponent row in at once and it spills across six cells:

=B2 * (1 + $H$1) ^ SEQUENCE(1, 6, 0, 1)

Step 6, the verified projection (every figure recomputed):

The smooth path starts exactly on the actual 2019 figure and lands exactly on the actual 2024 figure of $9,800,000. CAGR guarantees that arithmetically, so reproducing it end to end is your check that the rate is right. Between the endpoints the smooth path drifts away from the bumpy actuals, and that's expected. CAGR is the average compounding rate, and it doesn't describe any single year.

Step 7, read it. Two ideas did the work. LAMBDA turned a formula the analyst would otherwise retype, and mistype, into a named, audited CAGR she calls like SUM. SEQUENCE generated both the year labels and the exponent vector without a single dragged cell, and the projection spilled across the row from one formula. Change 2019's revenue and the CAGR, the projection and the check all update together. It's a small growth model, and every part of it is live.

Practice set

Work each problem before you read its solution. Assume every source range is a real Table where sensible, and that you're on Microsoft 365 so arrays spill on Enter. Formulas are given in full.

P1 (guided). Filter to a subset. From the first worked example's customer table, A2:D9, write the formula that returns all columns of every account whose Region (column B) is North. Then give a version that returns a readable message if none match.

Solution. =FILTER(A2:D9, B2:B9="North") returns the three North rows (Bhavna, Esha, Hari) with all four columns, in source order. To guard the empty case, add the third argument: =FILTER(A2:D9, B2:B9="North", "No customers in region"). Without if_empty, a no-match returns #CALC!, and the third argument swaps that for a message a colleague can read. FILTER picks rows and keeps them in their original order. It doesn't sort them.

P2 (guided). Top-3 in one formula. Write the single spilling formula that returns the top three active customers of A2:D9 by revenue (column D), highest first, as name-and-revenue only.

Solution. =CHOOSECOLS(TAKE(SORT(FILTER(A2:D9, C2:C9="Active"), 4, -1), 3), 1, 4). Read it from the inside out. FILTER keeps the six active rows. SORT(…, 4, -1) orders them by revenue, descending. TAKE(…, 3) keeps the top three, and CHOOSECOLS(…, 1, 4) carves out the Customer and Revenue columns. Result: Hari Steel ₹35,40,000, Divya Retail ₹32,75,000, Esha Foods ₹29,80,000. Because the FILTER runs before the SORT, the Dormant Farhan Textiles never gets in, even with its higher revenue.

P3 (guided). Distinct, sorted list. From the same customer table, build the self-maintaining sorted list of distinct regions you would feed a data-validation dropdown.

Solution. =SORT(UNIQUE(B2:B9)). UNIQUE(B2:B9) returns the three distinct regions in first-appearance order: West, North, South. Wrapping it in SORT puts them in alphabetical order, North, South, West. Both functions spill and read a whole range, so adding a customer in a new region grows the list automatically, which is what you want from a dropdown source. UNIQUE counts 3 distinct values here.

P4. Live count and sum of a spill. A FILTER of active customers spills from cell F2. Without hardcoding its height, write (a) a live count of how many rows it currently occupies and (b) the total revenue in its 4th column.

Solution. (a) =ROWS(F2#). The # spilled-range operator makes F2# mean "the whole current spill", so ROWS returns its live height, 6 for the active set. (b) =SUM(CHOOSECOLS(F2#, 4)) sums the fourth column of the spilled block whatever its height. That's the total active revenue, ₹1,60,35,000. Write =SUM(F2:F7) instead, and the day a seventh match appears your total misses it without any warning. F2# never goes stale.

P5. Sort by an outside key. Return just the customer names from A2:A9, ordered by revenue D2:D9 from highest to lowest, using a single function.

Solution. =SORTBY(A2:A9, D2:D9, -1). SORTBY sorts the name array by the separate revenue array, descending. The sort key doesn't have to be part of the array being sorted, and that's something SORT can't do. Result order: Hari, Farhan, Divya, Esha, Bhavna, Gita, Aarav, Chetan. Farhan shows up this time because this problem does not filter by status. Compare P2.

P6. SEQUENCE axis and its sum. Write the formula for a one-row, five-wide year header starting at 2020, and state the sum SUM would return over it.

Solution. =SEQUENCE(1, 5, 2020, 1) spills 2020, 2021, 2022, 2023, 2024 across a row (rows =1, cols =5, start =2020, step =1). Its sum is 2020+2021+2022+2023+2024 = 10,110. Change cols to make it wider, or set step to -1 to count backwards. SEQUENCE is the general "n evenly spaced values" generator, and it's the backbone of period axes and amortisation schedules.

P7. A CAGR LAMBDA. Define a reusable CAGR function, then use it to find the compound annual growth rate of revenue that rises from $500,000 to $800,000 over 3 years. Give the exact steps and the numeric answer to two decimals.

Solution. In Name Manager (Ctrl+F3 → New), Name CAGR, Refers to =LAMBDA(begin, end, years, (end/begin)^(1/years) - 1). Then =CAGR(500000, 800000, 3) = (800000/500000)^(1/3) − 1 = 1.6^(1/3) − 1 = 0.1696, or 16.96%. A LAMBDA has to be named, or called inline with trailing arguments, before it does any work. Typed bare into a cell, it only returns its own definition.

P8. Running total with SCAN. Using SCAN, write the formula that turns monthly cash flows {120, 90, 150, 60, 180} (in ₹ '000) into a running cumulative-cash row, and state the final cumulative value.

Solution. =SCAN(0, {120,90,150,60,180}, LAMBDA(acc, v, acc + v)) spills {120, 210, 360, 420, 600}, and each element is the cumulative total up to that month. The final cumulative cash is ₹6,00,000 (in '000, that's ₹6 lakh). Use SCAN when you want the whole running path. If you only wanted the final ₹600, you'd use REDUCE with the same lambda, or just SUM.

P9. Diagnose a #SPILL!. An analyst writes =SORT(A2:A9) in C2 and gets #SPILL!. C5 contains a stray label "check". Explain what happened and the exact UI fix.

Solution. The SORT needs to spill eight names down C2:C9, but C5 holds a stray "check" that blocks the spill. So Excel refuses and shows #SPILL! in the anchor, C2. To fix it, click the yellow warning triangle beside C2 and choose Select Obstructing Cells, which highlights C5. Clear or move it and the spill completes. The habit is to make sure the spill zone below and right of the anchor is empty before you enter a dynamic formula. A merged cell, or a spill that runs off the sheet edge, causes the same error.

P10. LET for readability. Rewrite the average-active-revenue calculation so the FILTER is evaluated only once, and state the result for the first worked example's data.

Solution. =LET(active, FILTER(D2:D9, C2:C9="Active"), total, SUM(active), n, ROWS(active), total/n). Naming active computes the FILTER a single time and reuses it for both total (₹1,60,35,000) and n (6), giving an average of ₹26,72,500. Written without LET, the FILTER would appear inside both SUM and ROWS, evaluated twice and harder to audit. If a sub-expression appears more than once, it wants a LET name.

P11. Multi-condition FILTER. From A2:D9, return every account that is Active and has revenue of at least ₹30,00,000. Then say why you use * and not the word AND.

Solution. =FILTER(A2:D9, (C2:C9="Active") * (D2:D9>=3000000)). Each condition is a Boolean array. Multiplying them (TRUE*TRUE=1) gives a 1 only where both hold, so FILTER keeps just those rows: Divya Retail (₹32,75,000) and Hari Steel (₹35,40,000), in source order. You can't use the AND function, because AND collapses an array to a single TRUE/FALSE. Element-wise * keeps the array that FILTER needs. Use + for OR.

P12 (timed: 6 min). Choose the right tool. For each mini-task, name the single best function or idiom and justify it in one line. (a) List the distinct product categories in a column. (b) Show only orders above ₹1,00,000. (c) Rank all sales reps by revenue, highest first. (d) Turn a column of prices into a running cumulative total. (e) Reference a filtered block that changes size, in a downstream SUM.

Solution. (a) UNIQUE, which returns distinct values and spills and resizes itself. (b) FILTER, which keeps the rows that meet a condition. (c) SORT with order -1, or SORTBY if you're ranking by an outside key. (d) SCAN with an adding LAMBDA. It returns the running accumulator, where SUM returns only the final total. (e) The # spilled-range operator. SUM(anchor#) always covers the live spill, and a fixed range would go stale. The skill underneath all five is asking "distinct? subset? reordered? running? live-sized?" before you type.

Applied mini-project

Your job is to build a self-refreshing sales report block: a one-sheet report that turns a raw customer-orders Table into a live management summary using nothing but dynamic arrays. You can't use helper columns or dragged formulas, and you can't sort by hand. When someone adds an order or edits a revenue figure, every block below must update itself.

Set up the source (a real Table, Ctrl+T, named Orders):

1. At least 12 rows, with columns Customer, Region, Status (Active/Dormant), and Revenue. Include at least one high-revenue Dormant account so your FILTER has something real to exclude, and at least three regions so UNIQUE has work to do.

Build the report blocks, each a single spilling formula:

1. Distinct regions. =SORT(UNIQUE(Orders[Region])), a self-maintaining sorted list.
2. Top 5 active customers. =CHOOSECOLS(TAKE(SORT(FILTER(Orders[[Customer]:[Revenue]], Orders[Status]="Active"), 4, -1), 5), 1, 4), name and revenue of the five biggest active accounts, re-ranking on any edit.
3. Live totals with #. Take the top-5 block's anchor, say F2, and report =ROWS(F2#) matches and =SUM(CHOOSECOLS(F2#, 2)) revenue, so the totals track the block's true size.
4. Average active revenue with LET. =LET(a, FILTER(Orders[Revenue], Orders[Status]="Active"), SUM(a)/ROWS(a)). The FILTER is evaluated once and reused under its name.
5. A CAGR LAMBDA. Register CAGR in Name Manager (=LAMBDA(begin,end,years,(end/begin)^(1/years)-1)) and use it on any two-period revenue pair in your data; lay the period axis out with SEQUENCE.
6. Robustness pass. Add a new order row to Orders and confirm every block grows or re-ranks with no edit. Type a stray value into a spill zone and practise clearing the resulting #SPILL! via Select Obstructing Cells. Then flip a top account to Dormant and watch it drop out of the top-5 block.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its FILTER→SORT→TAKE block and its named CAGR become patterns you'll paste into every model that needs a live ranking or a growth rate.

Lab: open the Dynamic arrays & LAMBDA workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function references (reach for these first):

• Microsoft Learn, the official pages for FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY, LET, LAMBDA, and the BYROW/BYCOL/MAP/SCAN/REDUCE iterators (search "FILTER function Microsoft support"): authoritative argument definitions and the spill/#SPILL! explainer. [Free] [Beginner]
• Microsoft Learn. "Dynamic array formulas and spilled array behavior" and "Guidelines and examples of array formulas": the definitive account of spilling, the # operator, and the @ implicit-intersection operator. [Free] [Beginner]

Deeper practice and pattern libraries:

• ExcelJet (exceljet.net), concise, example-driven entries for every dynamic-array function, plus its "Dynamic array formulas" overview and LET/LAMBDA explainers. [Free] [Beginner]
• Chandoo (chandoo.org), dynamic-array tutorials with downloadable practice files, and a strong LAMBDA series. [Free] [Beginner]
• Leila Gharani (YouTube), whose FILTER/SORT/UNIQUE and LAMBDA/LET deep-dives are the best moving-picture treatment of everything covered here. [Free] [Beginner]

For the formula engineer (LAMBDA and iterators, expanded):

• Microsoft's LAMBDA and "advanced" formula content, and any reputable write-up on building a house library of LAMBDA functions (search "LAMBDA function library Excel"): how teams share audited custom functions. [Free] [Intermediate]

Bridge forward: the self-maintaining UNIQUE/SORT lists here become data-validation sources in EX2.01 (passing the mastery check unlocks EX2.01), the SEQUENCE-driven grid is the skeleton of the sensitivity tables in EX2.02, and GROUPBY and PIVOTBY pair with the PivotTables of EX3.01.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Composing dynamic-array and LAMBDA formulas -- LET, named LAMBDA functions, and the iterator family (MAP, BYROW, BYCOL, SCAN, REDUCE)",
  "use": "Describe a transformation in plain English and ask an assistant to draft the LAMBDA -- then run it against a known case you can verify by hand (or one the module already worked out) before naming it in Name Manager and reusing it anywhere.",
  "tools": ["Microsoft Copilot in Excel (formula generation)", "a general-purpose LLM chat (Claude or ChatGPT) for a first-draft LAMBDA from a described transformation", "Excel's Name Manager, to inspect and test a candidate LAMBDA in isolation before deploying it across a workbook"],
  "prompt": "Write an Excel LAMBDA named PROJECT that takes a starting revenue, a CAGR, and a number of future periods, and returns the smooth compounding path as a spilled array starting at period 0 (the starting revenue itself). Show the LAMBDA definition only -- I will test it myself.",
  "verify": "Run the generated LAMBDA on a known case first: period 0 must return exactly the starting revenue, and if you also know the ending value (as WE2's CAGR guarantees), the last period must land on it exactly. An off-by-one in the exponent -- starting at period 1 instead of period 0 -- passes a casual glance and fails both checks.",
  "diy": "You must be able to write a LAMBDA with LET, and correctly reason about inclusive/exclusive interval counts (five growth intervals across six data points) with no assistant in the room."
}

Modern Data Analysis

By hand first. The second worked example built CAGR as a named LAMBDA and checked it by hand at 18.47%. Then it projected a smooth six-point revenue path that starts and ends exactly on the 2019 and 2024 actuals, which is the "arithmetic guarantee" check you met there.

Today's workflow. Drafting a LAMBDA is the job an assistant does best here. Generating a first-draft function from a plain-English description is fast. The known case you already have, starting exactly on the anchor and landing exactly on the end, is a free, mechanical way to catch its most common defect before the formula ever reaches a real model. That defect is the "classic off-by-one" flagged in the worked example.

Tools & sources (IN + US). Use Excel's Name Manager to deploy the audited LAMBDA. In India, screener.in publishes this same figure for any listed company as "Compounded Sales Growth," 5-year and 10-year, computed the same way as the worked example's CAGR. Take a company's own multi-year revenue from its annual report, run it through the same LAMBDA, and you have a direct check against screener.in's published number. In the US, SEC EDGAR's companyfacts API returns the multi-year revenue series that the identical LAMBDA can run against (the Revenues or RevenueFromContractWithCustomerExcludingAssessedTax XBRL tag).

# recomputed in-session — R3
begin, end, years = 4200000, 9800000, 5
cagr = (end / begin) ** (1 / years) - 1
print(f"CAGR = {cagr:.4f} ({cagr*100:.2f}%)")   # module states 0.1847 / 18.47%
assert round(cagr, 4) == 0.1847

# module's own WE2 smooth path, recomputed -- the "known case" any AI-generated LAMBDA must match
correct_path = [round(begin * (1 + cagr) ** n, 2) for n in range(6)]
print("correct (period 0..5):", correct_path)      # must match WE2's table to the cent

# a plausible AI first draft for a natural follow-on ask, "project revenue forward" -- makes
# exactly the off-by-one the module itself warns about ("a classic off-by-one to get right")
buggy_path = [round(begin * (1 + cagr) ** n, 2) for n in range(1, 7)]
print(f"Known-case check -- period 0 must equal the starting revenue {begin:,}:")
print(f"  correct: {correct_path[0]:,.2f}  (passes)")
print(f"  buggy:   {buggy_path[0]:,.2f}  (fails -- overstated by {(buggy_path[0]-begin)/begin:.2%})")

Verify. The recomputed CAGR matches the 18.47% worked out earlier, and the recomputed smooth path matches the six-point table to the cent. That confirms the stated figures, where a copy would only repeat them. The buggy LAMBDA is off by one period, the mistake you were warned to watch for. It passes a glance, because it runs and returns numbers without an error. Then it fails the one check that matters. Its period-0 output is $4,975,590.70, when the actual starting revenue is $4,200,000. That's an 18.47% overstatement, and without the known-case check it would ship unnoticed.

Where this goes next: galaxy cross-links

• G2 · CS1.02 Python II: Data Structures, Functions & Idiomatic OOP, where a named LAMBDA with LET is a one-line version of a Python function with local variables. You name a computation once and call it by name in both, just in a different syntax.
• X1 · AI0.03 Prompt Engineering & Structured Prompting. The PROJECT prompt above names an exact transformation and an exact starting condition, and writing a prompt precise enough that its output can be checked mechanically is what that module teaches.

Teach it back & journal

Feynman prompt. Explain what a "spill" is to someone who has only ever used old Excel, using the picture of a water tap and a row of cups. You open one tap, which is writing one formula in one cell, and it fills as many cups as there is water, so the result spills into just the cells it needs. Add more water at the source and the tap fills one more cup by itself, which is the spill resizing. Then explain the one thing that goes wrong. Someone has left a spoon in one of the cups (a stray value in the spill range). Why does the tap then refuse to pour at all (#SPILL!), and how do you find the cup with the spoon (Select Obstructing Cells)? Only in your last two sentences may you use the words anchor cell, spilled-range # and LAMBDA. Attach each one to the moment in the story it describes.

Journal prompt. Open a workbook you already maintain and hunt for two things: (1) any helper column, a scratch column of dragged formulas whose only job is to feed another formula, and (2) any formula you dragged down a whole column that you now suspect one spilling formula could replace. Rebuild one of them with FILTER, SORT, UNIQUE, MAP, or a LET, delete the helper column, add a row of fresh data, and note what got easier once the result resized itself instead of waiting for you to re-drag. Then write the one sentence you want to remember about why declaring a result and letting Excel size it is safer than managing cells by hand. Last, pick one formula you retype often (a CAGR, a margin, a day-count) and promise yourself you'll make it a named LAMBDA this week.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.