Expert Excel · Interactive lesson 2 of 12
Advanced dynamic array systems
LET named a value once. LAMBDA turns that idea into a reusable formula you can name, save, and apply to an entire column at once — no copy-paste, no risk of row 47 quietly using a different formula than row 3.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialReusable Formulas with LAMBDA and Dynamic ArraysSkill check
Try these first:
- Explain what a LAMBDA is, in one sentence, to someone who's used LET.
- Write a LAMBDA that takes one input and returns a classification.
- Apply that LAMBDA across a whole range with MAP instead of copying a formula down.
- Explain what REDUCE and SCAN do differently from each other.
Comfortable already? Skip ahead to E3 — Power Query Engineering.
Concept teach
What you're learning
- LET as formula architecture
- Beyond tidiness — LET is where a formula's logic gets designed and named, one readable piece at a time, before it's wrapped into something reusable.
- LAMBDA for reusable logic
LAMBDA(x, IF(x>=500,"Large","Small"))defines a formula with a placeholder inputx. Name it with LET, then call it just like a built-in function.- MAP, SCAN & REDUCE
- MAP transforms every value in an array with a LAMBDA. SCAN walks through an array keeping a running result at every step. REDUCE walks through the same way but only keeps the final result.
- BYROW, BYCOL & MAKEARRAY (when available)
- The same idea applied across a real two-dimensional table — one LAMBDA per row (BYROW), per column (BYCOL), or building a whole grid from scratch (MAKEARRAY). This simulator only models single-row and single-column spills, so treat these as reference material for real Excel rather than something to try here.
- Spill-aware design
- Reference an entire spilled result with
#—D2#means "whatever D2's array spilled into," so a formula built on it keeps working even if the array grows or shrinks. - Formula testing patterns
- Test a new LAMBDA against one or two known-answer cases in a scratch cell before applying it across a real column — the same instinct as testing a function before shipping it.
LAMBDA(x, body) takes one parameter (name it anything) and a formula that uses it. MAP(range, LAMBDA(...)) runs that LAMBDA once per value in range and spills the results back out — the array in, the same shape out.Where to find it in Excel
Formulas›Define Name / Name Manager›Refers to: =LAMBDA(…)
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Price | With tax | Tax rate | 8% | ||||
| 2 | Desk | 240.00 | 259.20 | ||||||
| 3 | Chair | 85.00 | 91.80 | ||||||
| 4 | Lamp | 32.00 | 34.56 | ||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Swipe sideways to see the whole window.
- 1Formulas › Define Name (or Name Manager › New).
- 2The name becomes a function you can call in any cell.
- 3Refers to holds the LAMBDA: parameters first, calculation last.
- 4Call it like a built-in function. MAP, SCAN, REDUCE and BYROW are under Logical.
- 5Test the LAMBDA in a cell first: =LAMBDA(p,r,p*(1+r))(B2,F1).
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 transaction amounts below. Build one reusable LAMBDA, then apply it — and fold it — three different ways.
Independent extension
Build a reusable formula system
Find a calculation you copy-paste down a column in your own work — a shipping-tier lookup, a grade curve, a late-fee rule. Write it once as a named LAMBDA, apply it with MAP across a real column, and confirm every row matches what the old copy-pasted version produced.
Spot a mistake or have an idea for this lesson? Send feedback