LAMBDA Function in Excel: Write Your Own Functions
By ExcelReady TeamUpdated 5 min read
The short answer
LAMBDA lets you build your own Excel function from a formula. List the inputs, then the calculation: =LAMBDA(old,new,(new-old)/old). Test it by adding values in brackets after it, then save it in Formulas > Name Manager with a name like PCTCHANGE and use =PCTCHANGE(B2,C2) anywhere in the workbook. It needs Microsoft 365 or Excel 2024.
Key takeaways
- Syntax: =LAMBDA(parameter1, parameter2, ..., calculation). The last argument is always the calculation.
- Typed on its own in a cell, a LAMBDA returns #CALC!. Test it with values in brackets: =LAMBDA(x,x*2)(5).
- Save it as a name in Name Manager to call it like a built-in function, anywhere in that workbook.
- LAMBDA works in Microsoft 365, Excel for the web and Excel 2024. Older versions show #NAME?.
- Helper functions like MAP and BYROW run a LAMBDA on every row or value of a range.
On this page+
The LAMBDA function in Excel lets you turn any formula into your own named function. You write the formula once, give its inputs names, save it in Name Manager, and then call it like SUM or ROUND, for example =PCTCHANGE(B2,C2). This guide shows the syntax, how to test and save a LAMBDA step by step, and the errors you'll meet on the way. LAMBDA needs Microsoft 365, Excel for the web or Excel 2024.
What is the LAMBDA function?#
LAMBDA wraps a calculation and gives names to its inputs. Those inputs are called parameters. The syntax is:
=LAMBDA([parameter1, parameter2, ...], calculation)| Part | What it means | Example |
|---|---|---|
| parameter | A name for an input value | old, new |
| calculation | The formula that uses those names; always the last argument | (new-old)/old |
So =LAMBDA(old,new,(new-old)/old) describes a percentage change: take a new value, subtract the old one, divide by the old one. A LAMBDA can have up to 253 parameters, and parameter names can't look like cell references (such as A1 or AB12).
If you're not sure what a function is in the first place, the guide to what an Excel function is explains arguments and syntax from the start.
How to test a LAMBDA in a cell#
A LAMBDA on its own does nothing, because it has no values to work on. Type =LAMBDA(old,new,(new-old)/old) into a cell and Excel shows #CALC!. To test it, put the values in brackets straight after it:
=LAMBDA(old,new,(new-old)/old)(800,900)This returns 0.125, or 12.5% when formatted as a percentage. You can use cell references too: =LAMBDA(old,new,(new-old)/old)(B3,C3). Testing in a cell like this is the best way to check the logic before you save it. The guide to the #CALC! error covers this and the other causes of that error.
How to save a LAMBDA as your own function#
Saving the LAMBDA under a name is what turns it into a reusable function:
- Go to Formulas > Name Manager and click New (or use Formulas > Define Name).
- In Name, type
PCTCHANGE. - In Refers to, type
=LAMBDA(old,new,(new-old)/old). - Optionally add a Comment that explains the inputs, then click OK.
Now you can use it in any cell of that workbook:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Last year | This year | Change |
| 2 | Desks | 1,200 | 1,500 | =PCTCHANGE(B2,C2) → 25% |
| 3 | Chairs | 800 | 900 | =PCTCHANGE(B3,C3) → 12.5% |
| 4 | Lamps | 2,000 | 1,700 | =PCTCHANGE(B4,C4) → −15% |
Names follow the same rules as named ranges: start with a letter or underscore, use no spaces, and don't pick something that looks like a cell reference. The guide to named ranges covers the rules and how to edit or delete a name later. To change the function, edit the Refers to box in Name Manager, and every cell that uses it updates.
A more useful example: optional inputs#
Parameters in square brackets are optional. Inside the calculation, ISOMITTED checks whether a value was given. This function adds tax at a default 20% unless you pass another rate:
=LAMBDA(price,[rate],price*(1+IF(ISOMITTED(rate),0.2,rate)))Saved as ADDTAX, =ADDTAX(50) returns 60, and =ADDTAX(50,0.1) returns 55. Optional parameters let one function cover the common case and the exceptions.
LAMBDA also pairs well with LET, which names steps inside a formula. A long calculation can use LET inside the LAMBDA so each step reads clearly.
LAMBDA helper functions: MAP, BYROW and more#
Helper functions take a LAMBDA and run it on many values at once. The results spill into nearby cells, as explained in the guide to dynamic arrays.
| Helper | What it does | Example |
|---|---|---|
| MAP | Runs the LAMBDA on each value (or pair of values) | =MAP(B2:B4,C2:C4,LAMBDA(o,n,(n-o)/o)) |
| BYROW | Runs it on each row, returns one result per row | =BYROW(B2:C4,LAMBDA(r,SUM(r))) |
| BYCOL | Runs it on each column | =BYCOL(B2:C4,LAMBDA(c,MAX(c))) |
| SCAN | Builds a running result, like a running total | =SCAN(0,B2:B4,LAMBDA(t,x,t+x)) |
| REDUCE | Boils a range down to one final result | =REDUCE(0,B2:B4,LAMBDA(t,x,t+x)) |
On the table above, the MAP formula spills 0.25, 0.125 and −0.15. BYROW returns 2,700, 1,700 and 3,700. The names you give inside these LAMBDAs, like o and n, only exist inside that formula.
Why a LAMBDA returns an error#
Most LAMBDA errors come from four causes:
- #CALC!: the LAMBDA has no values to work on. Call it with values in brackets, or save it as a name.
- #VALUE!: the function got the wrong number of inputs, such as
=PCTCHANGE(B2)when it needs two. - #NAME?: the name is misspelled, the name was never saved, or the file is open in Excel 2021 or earlier. The #NAME? error guide covers each case.
- Wrong answer: check the order of the parameters.
PCTCHANGE(C2,B2)swaps old and new and gives a different result.
When to use LAMBDA, and when not to#
LAMBDA is worth it when the same multi-step calculation appears in many places, such as a pricing rule, a commission formula or a cleanup step for imported text. One named function means one place to fix a mistake.
It is not worth it for a short formula you use once. Also think about who opens the file next: the logic lives in Name Manager, where many people never look, and anyone on older Excel sees #NAME?. Add a comment to each name and keep a note in the workbook about what each custom function does.
Practise it#
Every LAMBDA starts as a normal formula that you could write by hand, so get that part right first: try the exercise on this page. Then work through the modern Excel functions lesson to practise XMATCH, SORTBY, TEXTSPLIT and spill references in a live spreadsheet, with each step checked instantly. These are the building blocks that LAMBDA helpers like MAP and BYROW work with.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
A PCTCHANGE LAMBDA would calculate (new-old)/old. Write that calculation as a normal formula first: in D3, work out the change from B3 (last year) to C3 (this year).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Change | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 |
Tip: Subtract last year from this year inside brackets, then divide by last year.
Practise this in a lesson
Frequently asked questions
What is the LAMBDA function in Excel?
LAMBDA creates a custom function from a normal Excel formula. You name the inputs, write the calculation, and save it in Name Manager. After that you can call it by name, like =PCTCHANGE(B2,C2), without writing VBA.
Which versions of Excel have LAMBDA?
LAMBDA is available in Microsoft 365, Excel for the web and Excel 2024. Excel 2021 and earlier don't have it and show #NAME? for any formula that uses it.
Why does my LAMBDA return #CALC!?
A LAMBDA on its own has no values to work on. Add them in brackets straight after it, like =LAMBDA(x,x*2)(5), or save it as a name and call it with arguments.
Can a LAMBDA be used in other workbooks?
A LAMBDA saved in Name Manager belongs to that workbook only. To use it elsewhere, copy a sheet that uses it into the other workbook, or create the name again there.
Is LAMBDA better than a VBA function?
For calculations, LAMBDA has the advantage that it is a normal formula, so the file doesn't need to be saved as macro-enabled. VBA can still do things formulas can't, such as changing formatting or working with files.