TL;DR —
LAMBDAwraps a formula into a function you can name and reuse — the first time Excel lets you build a custom function without VBA. A bare=LAMBDA(x, x*2)returns#CALC!because a definition is not a call; add a trailing paren=LAMBDA(x, x*2)(5)to run it, or save it in Name Manager so the whole workbook can call=MyFunc(...). Excel 365 and 2021+.
=LAMBDA(x, x*2)(5) ' -> 10 (trailing paren passes the argument)
=FtoC(212) ' -> 100 (after naming =LAMBDA(t,(t-32)*5/9) as FtoC)
Every other Excel function was written by Microsoft. LAMBDA is the one you
write. It takes the formula logic you keep copying from cell to cell, gives it a
name and parameters, and turns it into a function that behaves exactly like
SUM or XLOOKUP — except it does whatever you defined. No macros, no .xlsm,
no Visual Basic editor.
What you'll learn
- The mental model: a definition of a function, not a result
- The one rule that confuses everyone: a bare LAMBDA returns
#CALC!— and the two ways to call it - How to save a LAMBDA in Name Manager so it works everywhere
- Recursion — a LAMBDA that calls itself, the one thing only LAMBDA (and VBA) can do
- When a LAMBDA beats copy-pasting a formula — and when it doesn't
The mental model: a LAMBDA is a function definition
LAMBDA(parameters, calculation) doesn't compute anything on its own — it
describes a calculation, waiting for inputs. Read =LAMBDA(x, x*2) as
"a function that takes x and returns x times 2." That's a definition, like
writing function double(x) { return x*2 } in code. Nothing has been called
yet — which is exactly why Excel, on its own, can't show you a value.
The rule that breaks LAMBDA: a definition is not a call
Type =LAMBDA(x, x*2) into a cell and you get #CALC!. This is the number
one source of "LAMBDA doesn't work," and it's not a bug — you defined a function
and never called it. There are two ways to call it:
=LAMBDA(x, x*2) ' #CALC! — a definition with no inputs
=LAMBDA(x, x*2)(5) ' 10 — the trailing (5) calls it with x = 5
The trailing parenthesis is the call. =LAMBDA(x, x*2)(5) means "define this
function, then immediately run it with 5." Useful for testing — but writing the
whole definition every time defeats the purpose. The real payoff is naming it.
Name it in Name Manager = your own worksheet function
Calling a LAMBDA inline is a party trick. The point is to store it under a name and call that name anywhere. Go to Formulas ▸ Name Manager ▸ New, give it a name, and put the LAMBDA in Refers to:
Name: FtoC
Refers to: =LAMBDA(t, (t-32)*5/9)
Now =FtoC(212) returns 100 in any cell, in any sheet of the workbook — exactly
like a built-in function, no (...) definition in sight:
=FtoC(212) ' 100
=FtoC(A2) ' converts whatever is in A2
You've added a function to Excel's vocabulary. Share the workbook and the function travels with it. This is the difference between LET and LAMBDA: LET names values inside one formula; LAMBDA names a function you call from everywhere. (And the body of a good LAMBDA is almost always a LET.)
Recursion: a LAMBDA can call itself
Because a named LAMBDA has a name, it can call itself — recursion, the one thing no ordinary formula can do and previously needed VBA. The catch: the name only exists once you've saved it in Name Manager, so recursion only works on a named LAMBDA, never a bare one.
Name: FACT
Refers to: =LAMBDA(n, IF(n<=1, 1, n*FACT(n-1)))
=FACT(5) walks itself down — 5*FACT(4), 4*FACT(3), … — and returns 120.
The IF that stops the descent (n<=1) is the base case; forget it and Excel
recurses until it gives up. Recursion is how LAMBDA handles the genuinely hard
jobs — walking a delimited list, cleaning a string of many bad characters, parsing
a hierarchy — in pure formula form.
The judgment call: when LAMBDA earns its keep
Reach for LAMBDA when you catch yourself pasting the same complex formula into many cells, or building logic you'd otherwise write as a VBA UDF. Naming it once means fixing it once — change the definition in Name Manager and every call updates. The honest limits:
- Don't wrap a one-liner you use once — that's just indirection.
- For a calculation that lives in a single formula, you want LET, not LAMBDA.
- To run a LAMBDA across a whole array (a column of values, each row of a table), you pair it with the LAMBDA helpers — MAP, REDUCE, BYROW.
The progression is the point: LET gives you variables, LAMBDA gives you functions, and the helpers give you loops. Together they turn Excel formulas into a small programming language.
How ExcelMaster helps
Writing a correct LAMBDA — parameters, base case, the trailing-paren test, getting it into Name Manager — is fiddly, and exactly the kind of thing an AI copilot nails in one shot. ExcelMaster turns "make a function that converts Fahrenheit to Celsius" or "a recursive function that strips every digit out of a string" into a working, named LAMBDA, defines it in Name Manager for you, and explains the base case so the recursion actually stops. Try it on your own data.
Frequently asked questions
What is the LAMBDA function in Excel?
LAMBDA lets you create your own reusable function from a formula, without VBA.
You define parameters and a calculation, save it under a name in Name Manager, and
then call it like any built-in function: =MyFunc(...).
Why does my LAMBDA return #CALC!?
Because a bare =LAMBDA(...) is a definition, not a call. Add a trailing
parenthesis with the arguments — =LAMBDA(x, x*2)(5) — or save the LAMBDA under a
name in Name Manager and call that name.
How do I make a LAMBDA reusable across the workbook?
Open Formulas ▸ Name Manager ▸ New, give it a name (for example FtoC), and
put the LAMBDA in Refers to: =LAMBDA(t,(t-32)*5/9). Now =FtoC(212) works in
any cell of the workbook.
Can a LAMBDA call itself (recursion)?
Yes — a named LAMBDA can reference its own name, so
=LAMBDA(n, IF(n<=1, 1, n*FACT(n-1))) named FACT computes a factorial. Always
include a base case (here n<=1) so the recursion stops.
Do I need VBA to create a custom function in Excel?
No. LAMBDA creates custom functions in pure formulas — no macros, no .xlsm file,
no Visual Basic editor. It's available in Excel 2021, 2024, and Microsoft 365.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-22.
Related guides: Excel LET · Excel MAP, REDUCE & BYROW · Excel FILTER · Excel SEQUENCE