Excel LAMBDA Function Examples for Reusable Formulas

Excel LAMBDA lets you turn a formula into a reusable function: define it with =LAMBDA(parameters, calculation), save it under a name in Name Manager, then use that name in worksheet formulas. For example, define a function called SQUARE and enter =SQUARE(A2) to square the value in A2 without repeating the calculation.

What Excel LAMBDA does

A LAMBDA function packages a calculation and its inputs together. Instead of copying a long formula down a column or rebuilding it in several places, you can give the calculation a name and call it like a built-in Excel function. The name can accept one or more inputs, called parameters, and return a result.

LAMBDA is available in Microsoft 365 and Excel 2024. It is not available in older perpetual releases such as Excel 2019 or Excel 2016. A workbook that uses a custom LAMBDA may show a #NAME? error when opened in a version that does not support the function or does not have the named function defined.

Unlike a VBA user-defined function, LAMBDA is created with a worksheet formula rather than a macro. It can be useful for standardizing repeated business rules, cleaning data, and making complex formulas easier to read. It does not automatically make a calculation faster; its main benefit is reuse and maintainability.

How to create and use a named LAMBDA

In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New. Enter a name, such as SQUARE, and put the LAMBDA definition in the Refers to box. Set the scope to Workbook if you want to call it from any worksheet in that workbook. Select OK, close Name Manager, and use the new name in a cell formula.

No worksheet cells are required to define this function; enter it in the Name Manager Refers to box with the name SQUARE: =LAMBDA(number,number^2).

With a number in A2, enter this formula in another cell: =SQUARE(A2). If A2 contains 6, the result is 36. In a LAMBDA definition, number is a parameter: Excel supplies its value when the named function is called. Choose parameter names that describe what the inputs represent, and avoid names that Excel could interpret as cell references.

The Name Manager workflow is for the desktop application. Excel for Mac also supports LAMBDA in Microsoft 365 and Excel 2024, though dialog and menu presentation can vary slightly by release. Excel for the web can calculate supported LAMBDA formulas, but the available name-management controls may differ from desktop Excel.

Example: square a value

A small function is a good way to learn the pattern. The SQUARE definition above accepts one input and returns that input multiplied by itself. Once it exists in the workbook, it can be used in a table, in a larger expression, or anywhere the same operation is needed.

With the value to square in A2, enter this formula in B2: =SQUARE(A2). If A2 is 6, B2 returns 36. Copying the call down the column adjusts the cell reference, so a value of 8 in A3 gives 64 in B3. The calculation itself remains in one named definition; if the rule later changes, you can update the definition instead of editing every copied formula.

Example: calculate a discounted price

A named function can accept multiple inputs. This example applies a discount rate to a price, returning the amount after the discount. It is a useful pattern whenever a calculation needs a value and a rate, multiplier, limit, or other setting.

No worksheet cells are required to define this function; create a name such as AFTER_DISCOUNT and enter this in Refers to: =LAMBDA(price,rate,price*(1-rate)).

With the original price in A2 and the discount rate in B2, enter this formula in C2: =AFTER_DISCOUNT(A2,B2). If A2 is 100 and B2 is 10%, the result is 90: 100 multiplied by 1 minus 10%. Enter the rate as a percentage or decimal, not as the whole number 10; a cell containing 10 means 1,000% in this calculation. This example is only a spreadsheet illustration, not financial advice.

Example: handle division by zero

Division formulas often need an agreed response when the denominator is zero. A LAMBDA can put that decision in one place, so different worksheets do not quietly use different error-handling rules. Here the function returns a blank text value when the denominator is zero and otherwise performs the division.

No worksheet cells are required to define this function; create a name such as SAFE_DIVIDE and enter: =LAMBDA(numerator,denominator,IF(denominator=0,"",numerator/denominator)).

With a numerator in A2 and denominator in B2, enter this formula in C2: =SAFE_DIVIDE(A2,B2). If A2 is 12 and B2 is 3, the result is 4; if B2 is 0, the result is blank. The blank is text, not the numeric value zero, which can affect later calculations. If downstream formulas need a number, you might choose 0 or a message such as "No denominator" instead, depending on the workbook’s purpose.

Example: standardize names or labels

Repeated text cleanup is another practical use. The following function removes leading and trailing spaces, reduces repeated ordinary spaces to one, and applies title-style capitalization. It can make imported labels more consistent, though it is not a complete solution for every naming convention or every kind of whitespace.

No worksheet cells are required to define this function; create a name such as CLEAN_NAME and enter: =LAMBDA(text,PROPER(TRIM(text))).

With the text in A2, enter this formula in B2: =CLEAN_NAME(A2). If A2 contains " jORDAN lee ", the result is "Jordan Lee". Excel’s TRIM function targets ordinary spaces; text imported from websites can contain nonbreaking spaces or other characters that TRIM does not remove. Also, PROPER capitalizes words mechanically, so it may not preserve a person’s preferred capitalization, acronyms, or names such as “McDONALD.” Review cleaned data where exact spelling matters.

Example: check whether a total meets a target

A LAMBDA can accept a range as an input, not just a single cell. That makes it possible to reuse a test against different lists of values. This example adds a range and returns a simple status depending on whether the total reaches a target.

No worksheet cells are required to define this function; create a name such as TARGET_STATUS and enter: =LAMBDA(values,target,IF(SUM(values)>=target,"Met","Not met")).

With values in B2:B4 and the target in C2, enter this formula in D2: =TARGET_STATUS(B2:B4,C2). If B2:B4 contain 40, 35, and 30, and C2 contains 100, the result is "Met", because the sum is 105. The range can be changed in the call without rewriting the named calculation. Make sure the target and values use compatible units, such as all being dollars or all being units sold.

Example: use recursion for a factorial

LAMBDA can call itself, a technique called recursion. A factorial is a classic demonstration: for a nonnegative integer, its factorial is the number multiplied by the factorial of the number one less, with 0! and 1! both equal to 1. Recursion is useful to understand, although many everyday spreadsheet tasks are clearer with ordinary formulas or built-in functions.

No worksheet cells are required to define this function; create a name called FACTORIAL and enter: =LAMBDA(n,IF(n<=1,1,n*FACTORIAL(n-1))).

With the input number in A2, enter this formula in B2: =FACTORIAL(A2). If A2 is 5, the result is 120, calculated as 5 × 4 × 3 × 2 × 1. This simple definition treats negative numbers as 1 because of its stopping condition, so it does not validate its input. Use it only with nonnegative integers, or extend the definition to return an error or message for unsupported inputs. Very deep recursion can also fail, so this is not a good way to process a long sequence.

Testing, troubleshooting, and design tips

Before relying on a named LAMBDA across a workbook, try it with a normal input, a boundary value, and any unusual case the calculation is expected to handle. Test the named function itself in a worksheet cell, then test formulas that depend on it. A concise name and clearly named parameters make the function easier for another person to understand later.

#CALC! can appear if a LAMBDA definition is entered in a worksheet cell without being called. Save the definition in Name Manager, or call it with arguments.
#VALUE! commonly points to a mismatch between the number of arguments supplied and the parameters expected, or to an argument of an unsuitable type.
#NAME? can mean the function name is misspelled, the name is out of scope, or the workbook is opened in an Excel version without LAMBDA support.
Choose workbook scope when a function should work throughout the file. A worksheet-scoped name is limited to that sheet and can be harder to reuse elsewhere.
Keep definitions focused. A function with a clear purpose is easier to test than one LAMBDA that combines unrelated rules and many nested conditions.

One common misconception is that a LAMBDA is automatically available in every workbook. A named function belongs to the workbook in which it is defined; sharing a worksheet formula without the corresponding name definition may leave the recipient with an undefined name. Include the named function in the workbook, or provide its definition and instructions when sharing a template.

Frequently Asked Questions

Can I use LAMBDA without Name Manager?
You can write a LAMBDA expression in a worksheet formula, but it must be called with its arguments to return a result. Naming it in Name Manager makes the calculation reusable under a readable function name.

Which Excel versions support LAMBDA?
LAMBDA is available in Microsoft 365 and Excel 2024. It is not supported in older Excel releases such as Excel 2019 and Excel 2016, so check compatibility before sending a workbook to someone using an older version.

Can I share a custom LAMBDA with another workbook?
A named LAMBDA is stored as a name in its workbook. Copying a formula that calls the name does not necessarily copy the definition. The destination workbook needs its own definition, or you can copy the named formula along with the relevant workbook content.

Is LAMBDA the same as VBA?
No. LAMBDA creates reusable calculations using Excel formulas and does not require a macro. VBA is a programming language used for macros and other automation. LAMBDA is a good fit for formula-based logic, while VBA can handle tasks that require broader automation.

Why does my LAMBDA return #CALC!?
A common cause is entering the definition in a cell without invoking it. A LAMBDA needs arguments and a calculation result. For a reusable function, save the definition as a name and call that name with the required inputs.

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.