Excel LAMBDA Formula Examples for Reusable Functions

Excel LAMBDA lets you turn a calculation into a reusable function, so you can name it once and call it like a built-in formula. For example, define =LAMBDA(price,rate,price*(1+rate)) as ADDVAT, then use =ADDVAT(B2,C2) to add the rate in C2 to the price in B2.

What Excel LAMBDA does

A LAMBDA formula packages a calculation and its inputs into a function. Instead of repeating a long expression across a worksheet, you can give that expression a descriptive name and use the name wherever you need the same calculation. This can make formulas easier to read, update, and audit.

LAMBDA is available in Microsoft 365 and Excel 2024. It is not supported in every older Excel release, so a workbook using it may not calculate correctly for someone on an earlier version. The examples here use Excel for Microsoft 365 on Windows and comma-separated arguments. Depending on regional settings, Excel may display semicolons instead of commas.

The general pattern is =LAMBDA(parameter1,parameter2,calculation). Parameters are placeholders for values supplied when the function is called. The final argument is the calculation Excel performs. A LAMBDA can take one or several parameters, or none, but the calculation must be the last argument.

Try a LAMBDA directly in a cell

You can test a LAMBDA before naming it by adding its input values in parentheses immediately after the definition. In an empty cell, with no worksheet data required, enter =LAMBDA(number,number^2)(5). It returns 25: Excel passes 5 into the parameter called number and squares it.

This direct-call form is useful for checking whether the calculation works, but it is not usually the best way to reuse a calculation throughout a workbook. If you copy the full expression into many cells, you still have repeated, harder-to-read formulas. Naming the LAMBDA solves that problem.

Create and use a named LAMBDA

To save a LAMBDA as a reusable function in Excel for Windows, open Formulas > Name Manager > New. Enter a name without spaces, such as ADDVAT, and put the LAMBDA definition in the Refers to box. Select OK, then use the name in a worksheet formula. The name is usually workbook-scoped, so it can be called from different sheets in that workbook.

For a named function called ADDVAT, assume the intended inputs are a price in B2 and a tax rate in C2; enter this definition in the Refers to box: =LAMBDA(price,rate,price*(1+rate)). In the worksheet, with 100 in B2 and 8% in C2, call it with =ADDVAT(B2,C2). The result is 108. Format the result as currency if you want it displayed as a monetary amount.

Choose names that describe the work the function performs, such as ADDVAT or CLEANNAME. Avoid spaces and names that could be mistaken for a cell reference. In Name Manager, you can review, edit, or delete workbook names later. If you change the definition there, formulas using that name update their behavior without having to replace each call in the worksheet.

Useful LAMBDA formula examples

These examples show different kinds of calculations. Each definition can be stored as a named function, or tested directly by adding sample inputs after the closing parenthesis. For reusable functions, put the definition in Name Manager and call it by its chosen name.

Clean and standardize names: Suppose A2 contains a person’s name with accidental extra spaces or inconsistent capitalization. Define a function named CLEANNAME using =LAMBDA(text,PROPER(TRIM(text))). With the text ada lovelace in A2, enter =CLEANNAME(A2) in another cell; the result is Ada Lovelace. TRIM removes extra spaces, and PROPER capitalizes the first letter of each word. PROPER is not a universal name-formatting rule, so check results for names with unusual capitalization.
Assign a simple letter grade: For a score in A2, define a function named LETTERGRADE with =LAMBDA(score,IF(score>=90,"A",IF(score>=80,"B",IF(score>=70,"C","Needs review")))). If A2 is 87, =LETTERGRADE(A2) returns B. This example assigns scores below 70 to Needs review; change the thresholds or labels to match the rules you actually use.
Calculate percentage change: Assume the old value is in A2 and the new value is in B2. Define PCTCHANGE as =LAMBDA(old_value,new_value,IF(old_value=0,"N/A",(new_value-old_value)/old_value)). With 80 in A2 and 100 in B2, =PCTCHANGE(A2,B2) returns 0.25; format the result as a percentage to display 25%. The zero check avoids dividing by zero. The text result N/A means this formula returns text for that case, so it should not be treated as a numeric percentage.
Count weekdays between dates: Assume a start date is in A2 and an end date is in B2. Define WEEKDAYSINRANGE as =LAMBDA(start_date,end_date,NETWORKDAYS(start_date,end_date)). With May 1, 2026 in A2 and May 8, 2026 in B2, =WEEKDAYSINRANGE(A2,B2) returns 6, counting both dates and excluding Saturday and Sunday. Holidays are not excluded in this version; for a holiday-aware calculation, add a holiday-range parameter and pass it to NETWORKDAYS.

Use LAMBDA with ranges

A parameter does not have to be a single value; it can receive a cell range. This lets you wrap a familiar calculation in a function with a clearer name. For example, assume sales amounts are in B2:B5. Define TOTALSALES as =LAMBDA(values,SUM(values)), then enter =TOTALSALES(B2:B5). If the four cells contain 10, 20, 30, and 40, the result is 100.

This kind of wrapper is most useful when the name communicates a business meaning that SUM alone does not, or when the calculation will later grow to include additional rules. If a plain SUM is already clear and used only once, a LAMBDA may add unnecessary setup. A named function is not automatically better simply because it is possible.

Recursive LAMBDA example

A named LAMBDA can call itself, which is called recursion. One classic demonstration is factorial: for a positive whole number, multiply it by the factorial of the number before it, stopping at 1. To create FACTLAMBDA, enter this definition in Name Manager’s Refers to box: =LAMBDA(n,IF(n<=1,1,n*FACTLAMBDA(n-1))). With an integer input of 5, =FACTLAMBDA(5) returns 120, because 5 × 4 × 3 × 2 × 1 = 120.

Recursion is a useful way to learn what LAMBDA can do, but it is not necessary for most everyday worksheet functions. A recursive function needs a stopping condition; without one, it keeps calling itself until Excel cannot continue and may return an error. Use established functions such as FACT when they already fit the calculation. For a real factorial task, the built-in function is usually simpler than creating a recursive substitute.

Choosing between LAMBDA, LET, and ordinary formulas

LAMBDA is for creating a reusable function. LET is for assigning names to intermediate values inside a single formula, which can improve readability or avoid repeating the same calculation within that formula. LET does not, by itself, create a workbook-level function you can call by name elsewhere.

For example, if a complicated expression is used only once, LET may be a more direct choice. If the same logic appears in many cells or sheets, a named LAMBDA can centralize it. Built-in Excel functions remain the best option when they already perform the required task clearly. VBA macros are another possibility for automation, but they have different security and maintenance considerations; a formula-based LAMBDA does not require a macro.

Before building a custom function, consider whether another person will understand its name and expected inputs. Add a short explanation in the workbook or use clear parameter names when the logic is not self-evident. A function that hides a complicated rule behind an unclear name can make a workbook harder, rather than easier, to maintain.

Common LAMBDA problems and fixes

#CALC! when testing a definition: A LAMBDA entered by itself in a cell is a function definition, not a completed calculation. Supply arguments after it to test it directly, or save it as a name and call that name with the required inputs.
#NAME? after creating a function: Check that the name exists in Name Manager, that the spelling in the worksheet matches, and that the workbook is open in an Excel version that supports LAMBDA. Also check whether a name was created with a different scope.
An argument-count error: Compare the number of parameters in the LAMBDA definition with the values in the function call. If a function accepts two parameters, its call needs two corresponding arguments in the same order.
Unexpected results: Check the order and type of the arguments. A date parameter needs a valid Excel date, and a percentage such as 8% is stored as 0.08. Also test boundary cases, such as a zero starting value or a score exactly on a grading threshold.
Recursive calculation fails: Confirm that the function reaches its stopping condition and that each recursive call moves toward it. Very deep or unbounded recursion can exceed Excel’s calculation limits.

For a complicated function, test a few small inputs with known answers before filling a large range. You can also use Formulas > Name Manager to inspect the saved definition. Remember that function names and formulas are part of the workbook’s design: document unusual assumptions, such as whether a date range includes both endpoints.

Frequently Asked Questions

Can I use LAMBDA in older versions of Excel?
LAMBDA is available in Microsoft 365 and Excel 2024, but not in every older Excel version. If someone opens a workbook in an unsupported version, custom LAMBDA formulas may not calculate as expected. Check the recipient’s Excel version before relying on named LAMBDA functions in a shared workbook.

Do I need Name Manager to use LAMBDA?
No. You can call a LAMBDA immediately by placing its arguments after the definition in the same formula. Name Manager is the practical choice when you want a shorter, reusable function name across a workbook.

Can a LAMBDA return more than one value?
A LAMBDA can return an array when its calculation produces an array. Whether the results spill into neighboring cells depends on the calculation and the Excel version’s dynamic-array support. Make sure the spill area is empty, or Excel may return a spill-related error.

Why does my named LAMBDA show #NAME?
The function name may be misspelled, missing from the workbook, or unavailable in the Excel version opening the file. Open Formulas > Name Manager and check the name and its Refers to definition, then confirm that the worksheet call uses the same spelling.

Is LAMBDA the same as a custom VBA function?
No. LAMBDA creates a reusable function using an Excel formula, while VBA custom functions are written in code in a macro-enabled workbook. LAMBDA avoids writing a macro for formula logic, but VBA can handle other kinds of automation that formulas cannot.

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.