In Excel for Microsoft 365 or Excel 2024, use LAMBDA to turn a calculation into a reusable custom function: write the parameters and calculation, save it with a name in Name Manager, then call that name in cells. For example, a named discount function can calculate a reduced price from an amount and discount rate without repeating the arithmetic in every formula.
What LAMBDA does in Excel
LAMBDA lets you create a function from a formula using Excel’s formula language. You provide optional input parameters, followed by the calculation that uses them. Once the LAMBDA is saved under a name, that name works like a built-in function in the workbook: you can enter it in cells, fill it down, and use it inside other formulas.
Before LAMBDA, people often repeated a long calculation across a worksheet, or used VBA to make a custom worksheet function. Repetition makes later changes harder: if the business rule changes, every copy may need editing. A named LAMBDA puts the rule in one place, so formulas using it are easier to read and update. It is still a formula, not a macro, and does not require VBA.
LAMBDA is especially useful when a calculation has a meaningful name, appears more than once, or has several steps that make a cell formula difficult to understand. It does not replace every ordinary formula. For a simple calculation used once, a direct formula may be clearer than creating a custom function.
How LAMBDA syntax works
The general pattern is a LAMBDA function containing zero or more parameter names and a final calculation. Parameters are local placeholders: they stand for the values supplied when someone calls the function. In the example below, amount and rate are inputs, and the calculation returns the amount after the discount.
For a no-cell test, the formula uses the literal input 5, so it assumes no input cells. Enter it in a worksheet cell to check the basic syntax; it returns 10.
=LAMBDA(x, x*2)(5)
The first part defines a function that doubles its input, and the final (5) supplies the value to use right away. This is called invoking or calling the LAMBDA. If you enter a LAMBDA definition by itself without calling it or assigning it a name, Excel displays #CALC! because it has a function definition but no instruction to run it.
Parameter names should be meaningful, such as amount or rate, and should not look like cell references. The final calculation can use Excel operators and functions, and it can refer to more than one parameter. LAMBDA supports up to 253 parameters, although a function with that many inputs would usually be difficult to maintain.
Create a named LAMBDA function
To make a LAMBDA available as a regular worksheet function in Excel for Microsoft 365 on Windows:
- Open the workbook and choose Formulas > Name Manager > New.
- In the Name box, enter a function name such as
DISCOUNT. Do not include spaces, and avoid names that Excel could interpret as cell references or that conflict with existing names. - Leave Scope set to Workbook if you want the name available throughout this workbook.
- In Refers to, enter the LAMBDA definition shown below, then choose OK.
- Close Name Manager. You can now enter the function name and its inputs in a worksheet formula.
For this reusable example, with the amount in A2 and the discount rate in B2, enter the following in the Name Manager Refers to box. The inputs are the values that future calls will supply; the definition itself returns the amount after discount.
=LAMBDA(amount, rate, amount*(1-rate))
If A2 contains 80 and B2 contains 15% (stored as 0.15), enter this formula in C2 to call the named function. The layout is amount in A2, rate in B2, and the result in C2; the returned value is 68, because 80 × (1 − 0.15) = 68.
=DISCOUNT(A2,B2)
Format C2 as currency if the result represents money. The function returns a number; cell formatting controls how that number appears. To change the discount rule later, edit the definition in Name Manager rather than rewriting every call. The function name is stored in the workbook, so another person generally needs the workbook’s name definition as well as the worksheet cells to use it.
Test and debug the function
Test a LAMBDA with a small, known input before filling it through a large table. The direct-call method is handy while developing because it lets you test the definition without first creating a name. For a no-cell test using an amount of 80 and a rate of 15%, enter the following formula; it returns 68.
=LAMBDA(amount, rate, amount*(1-rate))(80,0.15)
After it returns the expected result, save the LAMBDA under a name and test that named function against the same values. This separates errors in the calculation from errors in how the name is defined. You can also select the name in Name Manager and inspect its Refers to entry if the worksheet call does not behave as expected.
Check these common problems:
- #CALC! usually means the LAMBDA definition was entered without being called. Supply arguments after the definition for a quick test, or assign the definition a name and call that name.
- #VALUE! can indicate that the number of supplied arguments does not match the number of parameters. Check commas and make sure each required input is provided.
- #NAME? can mean Excel does not recognize the custom name. Check its spelling and scope in Name Manager, and confirm that the name was created in the workbook you are using.
- A wrong answer may come from the input values rather than the function. For rates, Excel expects 15% to be stored as 0.15; a value of 15 means 1,500% in a calculation.
- A recursively defined LAMBDA that calls itself without a stopping condition can keep calling itself until Excel returns an error. If using recursion, make sure the calculation includes a condition that eventually ends the calls.
When LAMBDA is useful
LAMBDA is a good fit for a repeatable rule that has a clear purpose and predictable inputs. Naming the rule can make a workbook easier for colleagues to review, especially when the original calculation has several operations or appears in multiple places. Examples include applying a standard markup, converting units, or returning a consistent label from a value.
For instance, a team might use one named function to calculate a standard service fee across many rows. Another workbook might have a repeated formula for classifying a score into a label. In either case, the value is not just fewer keystrokes: a descriptive function name can communicate the calculation’s intent more clearly than a long expression repeated in a column.
LAMBDA can also work with arrays when the calculation and the surrounding Excel functions support array results. That makes it possible to build functions that process more than one value at a time. Start with a single-row or single-value test, though; array behavior can be less obvious when the function combines inputs of different sizes or shapes.
Use a normal cell formula when a calculation is simple and unique. Use a named LAMBDA when you want a reusable rule without VBA. Consider VBA or another tool only when the task needs capabilities formula functions cannot provide, such as certain interactions with workbook objects or external processes. LAMBDA itself does not automate clicks, edit workbook structure, or run code outside calculation.
Availability and sharing
LAMBDA is available in Microsoft 365 and Excel 2024. It is not available in Excel 2021 under the stated version requirements, so a workbook containing LAMBDA formulas may not calculate correctly for someone using an older release. Before sharing a workbook, check the recipient’s Excel version if the custom function is important to the result.
Named LAMBDAs belong to the workbook in which they are defined. If someone copies only a worksheet into another workbook, the formulas may refer to a name that was not copied. Check the destination workbook’s Name Manager and bring over the required name definitions, or share the full workbook. A function with Workbook scope can be called from different sheets in that workbook; a sheet-scoped name is limited to its particular worksheet context.
On Mac, the same LAMBDA concept and workbook-defined names apply, although ribbon placement and dialog layout can differ slightly by Excel version. Excel for the web supports many modern Excel formulas, but the exact name-management interface can differ from the Windows desktop steps above. If you cannot find a command, look under the Formulas tab for name-management options in your version.
LAMBDA compared with LET
LAMBDA and LET both help make formulas easier to manage, but they solve different problems. LET gives a name to an intermediate value inside one formula. That can prevent repeating the same expression several times in that formula and make its steps easier to read. The LET names do not become a separate function that you can call elsewhere.
LAMBDA packages a calculation as a function that accepts inputs. When you save it through Name Manager, you can reuse it in many cells by calling its name. A LAMBDA can itself use LET internally if intermediate values make its calculation clearer. Choose LET for a one-off formula with repeated subexpressions; choose LAMBDA when you want to reuse a named calculation across the workbook.
Frequently Asked Questions
Can I use LAMBDA without Name Manager?
Yes. You can test a LAMBDA by putting its definition and its arguments together in one formula. To reuse the calculation by a readable name in other cells, define it in Name Manager.
Why does my LAMBDA show #CALC!?
The most common cause is entering a LAMBDA definition without calling it. Add arguments to run it immediately, or save the definition as a name and call that name with the required inputs.
Can I use LAMBDA in Excel 2021?
LAMBDA requires Microsoft 365 or Excel 2024 under the version guidance here. Excel 2021 users should not assume that a workbook containing LAMBDA will work for them.
Can I share a named LAMBDA with another workbook?
The name is part of the workbook, so copying cells alone may not copy the function definition. Share the workbook or also recreate the name in the other workbook’s Name Manager.
Is LAMBDA the same as VBA?
No. LAMBDA creates a custom function using Excel formulas, while VBA is a programming language for macros and automation. LAMBDA is suitable for reusable calculations; it does not perform the broader actions that a macro can.
Related
- Undo in Excel: Shortcuts, Limits, and Recovery Tips
- Undo vs Redo Excel: What Each Command Does
- Ungroup Excel Shortcut: Remove Row and Column Groups
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Budget vs Actual Excel Formula: Examples and SetupIf budget is in B2 and actual spending or revenue is in C2, the basic budget-versus-actual formula is…
- Excel Conditional Formatting Formula for DatesIn Excel for Microsoft 365 on Windows, use a formula-based conditional formatting rule to highlight dates that meet…
- Excel Formula for Days Left in MonthThe simplest Excel formula for days left in the current month is =EOMONTH(TODAY(),0)-TODAY(). This returns the number of…
- Excel Equation to Remove Duplicates: Formulas & StepsIn Microsoft 365, use =UNIQUE(A2:A100) to return a list of distinct values from A2:A100. This formula leaves the…
- DATEDIF Spreadsheet Examples: Years, Months, and DaysIn Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years,…
- How to Get Out of Filter Mode in Excel and Show All RowsTo get out of filter mode in Excel and show every row, select a cell in the filtered…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.