Basic LAMBDA Function in Excel: How to Create One

Formulas & functions · Updated November 21, 2026

A basic Excel LAMBDA turns a calculation into a function: with no worksheet cell layout required, enter =LAMBDA(x,x*2)(5) to double 5 and return 10. To reuse that calculation by name, save =LAMBDA(x,x*2) in Name Manager as DoubleIt, then call it with a value or cell reference.

LAMBDA is useful when you repeat the same logic in several formulas and want one clear, maintainable definition instead of copying a long expression across a workbook.

What the LAMBDA function does

LAMBDA lets you define a custom function using ordinary Excel formulas, without writing VBA or installing an add-in. Its general pattern is =LAMBDA(parameter1,parameter2,calculation). The parameters are placeholders for inputs; the final argument is the calculation Excel performs using those inputs.

For example, in the opening formula, x is a parameter and x*2 is the calculation. The final (5) supplies the value for x. Excel evaluates the expression and returns 10. A LAMBDA can take no more than 253 parameters, although simple functions often need only one or two.

There are two common ways to use LAMBDA:

  • Inline: put the LAMBDA definition and its input together in one formula to test or use it once.
  • By name: save the definition as a named function, then call that name in formulas throughout the workbook.

How LAMBDA arguments and calculations work

A LAMBDA definition has a sequence: first its parameters, then its calculation. Excel uses the values supplied when the function is called in place of the parameter names. The names are labels, not values or cell references. For a function that adds two inputs, for instance, the parameters might be first and second, with first+second as the calculation.

When you save a LAMBDA as a named function, the definition itself does not include actual input values. Each worksheet formula provides those values when it calls the function. This separation is what makes the same definition reusable for different rows, cells, or calculations.

Parameter names must be valid Excel defined names. Avoid spaces and names that look like cell references, such as A1. Choose descriptive names when a function has more than one input: quantity and unitprice are easier to interpret than a and b. A readable definition is easier to check later, especially when the calculation has several operations.

Test a LAMBDA directly in a cell

An inline LAMBDA is a quick way to confirm that its parameters and calculation work before creating a reusable name. No worksheet cell layout is needed for this temperature conversion; enter the formula in any blank cell:

=LAMBDA(celsius,(celsius*9/5)+32)(20)

The result is 68, because 20 degrees Celsius converts to 68 degrees Fahrenheit. The first part defines a function with one input, celsius; the final (20) calls it with 20. This is a useful pattern for a one-off test, but it repeats the definition if you paste the same calculation into multiple cells.

You can also pass a cell reference instead of a number. With the Celsius value 20 in A2, enter =LAMBDA(celsius,(celsius*9/5)+32)(A2) in another cell; it returns 68. If A2 changes, the result updates. For a function you expect to use repeatedly, save it with a name rather than keeping the full inline expression in every formula.

Create a reusable named LAMBDA

In Excel for Microsoft 365 on Windows, create a named function through the ribbon:

  1. Select Formulas > Name Manager > New.
  2. In the Name box, enter DoubleIt.
  3. In Refers to, enter =LAMBDA(x,x*2). No worksheet cell layout is required for this definition; it describes the function rather than calling it.
  4. Choose OK, then close Name Manager.

In the formula bar, use DoubleIt like a built-in function. With the number 18 in A2, enter =DoubleIt(A2) in another cell. The result is 36. The formula refers to the input cell, so changing A2 recalculates the answer.

Names are generally available throughout the workbook unless you deliberately set a different scope in the New Name dialog. Use a clear name without spaces, and remember that Excel does not distinguish function names by capitalization. If Excel does not recognize the name, check its spelling and whether you created it in the workbook you are using.

Useful basic examples

A named LAMBDA can make a short repeated calculation easier to read. For example, a function that doubles a value may be clearer as =DoubleIt(A2) than as =A2*2 when the same rule is used across many sheets or formulas. The benefit is not that the shorter formula does more; it is that the rule has one definition that you can update centrally.

LAMBDA also works with ranges when the calculation supports array results. With the numbers 2, 4, and 6 in A2:A4, enter =LAMBDA(x,x*2)(A2:A4) in a clear area outside that range. In Microsoft 365, the formula returns 4, 8, and 12 in the cells below it as a spilled array. Leave the spill area empty; if another value blocks the output, Excel can return a spill error.

As a design habit, test a function with a small, known input first. Check both a typical value and any meaningful boundary values, such as zero or a negative number if those are possible in your data. That helps distinguish an error in the LAMBDA definition from an unexpected input in the worksheet.

Choose clear parameters and predictable behavior

A good custom function has a focused purpose. If a LAMBDA tries to handle many unrelated cases, it may become harder to understand than the formulas it replaces. Start by writing down what the function receives, what it returns, and any assumptions about the inputs. Then use parameter names that reflect those inputs.

Consider how the function should behave with blanks, text, zero, or errors in its inputs. Excel’s normal calculation rules still apply inside a LAMBDA: a text value used in arithmetic may produce an error, and a referenced error can flow through to the result. Add handling only when it reflects the intended business rule; hiding every error can make incorrect data harder to spot.

Keep the definition easy to inspect in Name Manager. You can edit the Refers to expression there if the calculation needs to change. Because every call to the named function uses that definition, a change can affect many formulas at once. Review the results in representative cells after editing, just as you would after changing a shared formula.

Common LAMBDA errors and fixes

  • #CALC! while defining it: A LAMBDA entered by itself in a cell is a function definition, not a completed call. For an inline test, provide the arguments after the definition; for reuse, save the definition as a name and call that name.
  • #NAME? when calling a custom function: Check that the name exists in Name Manager, is spelled correctly, and is available in the current workbook. Also confirm that your Excel version supports LAMBDA.
  • #VALUE! from the call: Check that the number of supplied arguments matches the parameters and that the inputs have suitable types. A two-parameter function needs two arguments.
  • Invalid parameter or name: Check for spaces, invalid characters, or a parameter that resembles a cell reference. Rename it with a valid, descriptive identifier.
  • Spill error from a range input: Make sure the cells where the array result should appear are not occupied or merged in a way that blocks the output.

When troubleshooting, test the calculation with a single known value before applying it to a full range. If the inline version works but the named version does not, the issue is likely in the name or its saved definition rather than in the underlying arithmetic.

LAMBDA compared with LET and ordinary formulas

LET gives a name to a value or calculation within one formula. It can make a complicated formula easier to read and can avoid recalculating the same expression inside that formula. Its names do not create a reusable worksheet function: they apply only within that formula.

LAMBDA creates a function definition that can be saved as a workbook name and called from multiple formulas. A simple formula such as =A2*2 may be the clearest option if it appears only once. Use a named LAMBDA when a calculation is repeated, needs a consistent definition, or is complex enough that a meaningful function name improves readability.

VBA can also create user-defined functions, but it involves macro code and workbook macro considerations. LAMBDA is a formula-based option that does not require a VBA macro. It is often the simpler choice for reusable calculations that Excel’s formula language can express.

Version and platform availability

LAMBDA is available in Microsoft 365 and Excel 2024. It is not part of every older Excel release, so a workbook using named LAMBDAs may not calculate correctly for someone opening it in an unsupported version. If you share a workbook, consider the recipients’ Excel versions and provide a conventional formula or documented alternative when compatibility matters.

The examples here use Excel for Microsoft 365 on Windows. Excel for Mac has the same core LAMBDA formula syntax; the ribbon layout can differ slightly, but use the Formulas tab and Name Manager or the available name-definition command. Excel for the web can calculate supported formulas, though the controls for managing workbook names may differ from desktop Excel.

Frequently Asked Questions

What is the simplest LAMBDA formula in Excel?
No worksheet cell layout is needed for this inline example: =LAMBDA(x,x+1)(5) returns 6. It defines a function that adds one, then calls it with 5.

Why does a LAMBDA formula return #CALC!?
Usually, Excel has received a LAMBDA definition without the arguments needed to call it. Add the input values after an inline definition, or save the definition as a name and call that name from a worksheet formula.

How do I save a LAMBDA as a function name?
In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New, enter a name, and put the LAMBDA definition in the Refers to box. Confirm it, then use the name followed by its arguments in a cell.

Can a LAMBDA take more than one input?
Yes. Put multiple parameter names before the calculation, separated by commas, and provide the same number of arguments when you call the function. Excel allows up to 253 parameters, though a smaller number usually makes a function easier to use.

Can I use a LAMBDA with a range?
Yes, when the calculation can return an array. In Microsoft 365, an array result can spill into neighboring cells. Keep the expected output area clear, and verify that your Excel version supports the functions used in the calculation.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.