Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
LAMBDA
Dynamic arrays (Microsoft 365)Build your own reusable function — no VBA — then name it and call it like any built-in.
Difficulty
1What is it?
LAMBDA turns a formula into a reusable, custom function: you list the inputs (the parameters), then write the calculation that uses them. Give that LAMBDA a name in the Name Manager — say MARGIN — and from then on you type =MARGIN(rev, cogs) anywhere in the workbook, exactly like a built-in function. It's how you write your own functions without VBA: define a gnarly calculation once, name it, and reuse it everywhere, so a fix in one place updates every sheet that calls it. You can also test a LAMBDA before you name it by adding the arguments right after it: =LAMBDA(x, x*2)(5) returns 10. LAMBDA is also the engine behind MAP, REDUCE, SCAN, and BYROW, which run a LAMBDA over a whole array. Microsoft 365 / Excel 2021+.
2What it looks like
LAMBDA(parameter1, [parameter2, …], calculation)- parameter1
- A name for the first input, like a variable — letters and numbers, no spaces. Up to 253 parameters.
- [parameter2, …]
- More named inputs the calculation can use.
- calculation
- The formula to run, written using the parameter names — this is the last argument and is what the function returns.
3When you use it
- Name a repeated calculation once (a margin, a tax gross-up, a custom rounding) and call it across the workbook.
- Replace a fragile copy-pasted formula with one named function, so a fix propagates everywhere.
- Feed a LAMBDA to MAP/REDUCE/BYROW to run your logic over a whole column without dragging a formula down.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Write a gross-margin function and call it right away by adding its arguments in a second set of parentheses. Edit B2 or B3.
=LAMBDA(rev, cogs, (rev - cogs) / rev)(B2, B3)60.0%| A | B | C | |
|---|---|---|---|
| 1 | Field | Value | Result |
| 2 | Revenue | 60.0% | |
| 3 | COGS |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#CALC!The LAMBDA was entered on its own with no arguments to run on.Fix: Either add the arguments to test it — =LAMBDA(x, x+1)(4) — or define it as a name and call that name instead.
#VALUE!It was called with the wrong number of arguments.Fix: Pass exactly as many arguments as there are parameters, in the same order.
#NAME?A parameter name is misspelled in the calculation — or you're on a version without LAMBDA.Fix: Match each name to its definition; LAMBDA needs Microsoft 365 or Excel 2021+.
6Better functions & alternatives
- LET — Name values inside one formula — LAMBDA wraps a LET-style calc into a reusable named function.
- MAP / REDUCE / SCAN — Apply a LAMBDA across an entire array — sum, transform, or accumulate down a column.
- Custom function (VBA) — The pre-LAMBDA way to write your own function — needs a macro-enabled .xlsm and VBA.
Want LAMBDA already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →