Excel’s LET function assigns names to values inside a formula, while LAMBDA lets you create a reusable function with your own inputs. Use LET to make a single formula easier to read and maintain; use LAMBDA when you want to reuse a calculation across a workbook, often combining it with LET to make that custom function clearer.
What LET and LAMBDA mean
LET and LAMBDA are related functions, but they do different jobs. LET gives a name to a value or calculation for use within one formula. Those names exist only while Excel evaluates that formula; they do not become worksheet-wide names. LAMBDA defines a function that accepts inputs and returns a result. You can use a LAMBDA directly in a cell or save it as a named formula so that you can call it elsewhere.
The practical distinction is scope and reuse. A LET variable is local to its formula. A named LAMBDA can be called from multiple cells, and a workbook-level name can make it available across sheets. Neither function requires VBA or a macro: they are native formula features.
Both can help with long formulas, but neither is automatically the right choice for every calculation. A short formula that is already easy to understand may not benefit from extra names or a custom function. Their value is greatest when a calculation is repeated, difficult to audit, or likely to need later changes.
How LET works in a worksheet formula
LET follows a repeated name-and-value pattern, followed by the final calculation. Excel calculates the named values and lets the final expression refer to them. You can use several name-value pairs, which is useful when a calculation has intermediate steps.
For example, suppose B2 contains a unit price of 12.50 and C2 contains a quantity of 4. With unit price in B2 and quantity in C2, this formula returns 50:
=LET(unit_price,B2,units,C2,unit_price*units)
The names make the calculation easier to follow than repeating the cell references. If you later need to change the calculation, you can adjust its named parts in one place. LET can also avoid repeating the same expression several times in a longer formula. That can make a formula simpler to inspect and, in some cases, reduce repeated calculation work.
Here is another example using revenue and cost. With revenue in B2 and cost in C2, where B2 is 250 and C2 is 175, this formula returns 0.3, or 30% if the result cell is formatted as a percentage:
=LET(revenue,B2,cost,C2,(revenue-cost)/revenue)
LET names must follow Excel’s naming rules, so avoid names that look like cell references. Descriptive names such as revenue or unit_price are easier to interpret than single letters. The names are for the formula, not labels displayed in the worksheet.
How LAMBDA creates a reusable function
A LAMBDA has parameters, which act as inputs, and a calculation that uses them to produce the result. For a simple example, you can define a function that calculates the perimeter of a rectangle. The definition does not depend on particular worksheet cells; its inputs are the width and height supplied when it is called:
=LAMBDA(width,height,2*(width+height))
That expression defines the function but does not, by itself, provide values for its inputs. If you enter a LAMBDA definition directly in a cell without calling it, Excel returns a #CALC! error. You can call it immediately by adding input values after the definition. With a width of 5 and a height of 3 supplied as the inputs, this returns 16:
=LAMBDA(width,height,2*(width+height))(5,3)
For repeated use, save the definition as a named formula. In Excel for Microsoft 365 on Windows, open Formulas > Defined Names > Name Manager, select New, enter a name such as RectanglePerimeter, and put the LAMBDA definition in Refers to. Choose a workbook scope if you want to call it from different sheets. After saving, you can use it like a built-in function. With width in A2 and height in B2, this formula calls the named function:
=RectanglePerimeter(A2,B2)
Excel then supplies the cell values to the named LAMBDA’s parameters and returns the calculated result. Choose a clear name that does not conflict with an existing function or a cell reference. Naming conventions also apply to LAMBDA parameters.
Using LET inside LAMBDA
LET and LAMBDA work especially well together. LAMBDA makes a calculation reusable; LET gives meaningful names to intermediate values within that reusable calculation. For instance, a named function can calculate a gross margin from revenue and cost, while a LET name makes the intermediate gross profit explicit.
To define this function, no worksheet-cell layout is required: the LAMBDA receives revenue and cost as inputs. Save the definition with a name such as GrossMargin in Name Manager:
=LAMBDA(revenue,cost,LET(gross_profit,revenue-cost,gross_profit/revenue))
Then, with revenue in B2 and cost in C2, call the named function like this:
=GrossMargin(B2,C2)
If B2 is 250 and C2 is 175, the result is 0.3, or 30% when formatted as a percentage. This example assumes revenue is not zero; if it can be zero, decide how the workbook should handle that case rather than allowing a division-by-zero error. The named function also centralizes the calculation: if the business definition changes, update the LAMBDA once instead of editing many formulas individually.
When to use LET, LAMBDA, or both
Choose LET when the calculation is specific to one cell or when repeated parts make a formula hard to read. It is a good fit for a formula that needs intermediate values but does not need to become a reusable workbook function. Choose LAMBDA when you have a calculation that should be called repeatedly with different inputs.
Use both when you want a reusable function whose internal steps should remain understandable. A LAMBDA can also call another named LAMBDA, allowing a workbook to build more complex calculations from smaller functions. That flexibility is useful, but it also makes documentation important: a colleague may not know what a custom name means just by looking at a cell.
- Use LET to name and reuse values within one formula.
- Use a named LAMBDA to give a repeated calculation a reusable function name.
- Use both when a reusable function has intermediate steps that benefit from clear names.
- Use a conventional formula when it is already short, clear, and unlikely to be reused.
Before creating a custom function, check whether Excel already has a built-in function that does the job. A built-in function is often more familiar to other users and may be easier to maintain. A custom LAMBDA is most helpful when it captures a calculation that is specific to your workbook or combines several steps into one meaningful operation.
Version availability and platform notes
LET is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. LAMBDA is available in Microsoft 365 and Excel 2024; it is not included in Excel 2021. Availability can depend on the Excel edition and update channel, so check the function support in the version used by everyone who needs to open the workbook.
The formulas shown here use standard Excel formula syntax and comma argument separators. Excel installations configured for some regional settings may display semicolons instead of commas as argument separators. That is a regional formula-setting difference, not a different LET or LAMBDA function.
On Mac, the functions are available in supported Excel versions as well. The Name Manager interface may differ slightly from Windows; use Excel’s Formulas tab and its defined-name tools to create or edit a named formula. Excel for the web supports many modern formulas, but an organization’s Microsoft 365 setup or a particular feature’s availability may affect the available interface. If a workbook will be shared across platforms or editions, test the formulas in the versions recipients use.
Common errors and practical fixes
A few recurring problems explain most first attempts that do not work:
#CALC!from a LAMBDA definition: The formula defines a function but does not call it. Supply arguments after the definition, or save the definition as a name and call that name from a cell.#NAME?for a custom function: Check that the LAMBDA was saved under the spelling you used, that the name is in the intended scope, and that the Excel version supports LAMBDA. If a name has worksheet scope, it may not be available from another sheet in the same way as a workbook-scoped name.#VALUE!when calling a LAMBDA: Check that the call supplies the same number of arguments as the function’s parameters and that the inputs are appropriate for the calculation.- Unexpected results from LET: Check the order of the name-value pairs and the final calculation. Each name must be paired with its value before the next pair or the final expression.
- Names that Excel rejects: Use valid, descriptive names and avoid text that Excel interprets as a cell reference or another invalid name. Underscores can make multiword names easier to read.
- Errors from unusual inputs: Consider blank cells, text, zero denominators, and negative values. A formula that works for one example may still need an explicit rule for these cases.
For a complicated LAMBDA, test the calculation with a few small, known inputs before using it across a large range. You can temporarily call the LAMBDA directly with sample values, then move the definition into Name Manager once the result is right. Add a short description in the name’s comment field, or document the function near the worksheet, so other users know what its inputs and output represent.
Frequently Asked Questions
Is LET the same as LAMBDA in Excel?
No. LET assigns local names to values in one formula. LAMBDA defines a function that takes inputs and returns a result; saving it as a name lets you reuse it in other formulas.
Do I need Name Manager to use LAMBDA?
No. You can call a LAMBDA directly in a cell by supplying its inputs. Name Manager is useful when you want to save the function under a reusable name.
Can I use LET inside a LAMBDA?
Yes. LET can name intermediate calculations inside a LAMBDA, which can make a custom function easier to read and update.
Why does my LAMBDA return #CALC!?
A LAMBDA definition entered without a call has no input values to calculate with. Call it with arguments or save it as a name and use that name in a formula.
Does LET work in Excel 2021?
Yes. LET is supported in Excel 2021 and later. LAMBDA requires Microsoft 365 or Excel 2024, so it is not available in Excel 2021.
Should I replace every long formula with a LAMBDA?
No. Use a named LAMBDA when a calculation is reused or has a useful function-like meaning. For a one-off calculation, LET may be enough to improve readability, and a short formula may need neither.
Related
- Large Excel Cheat Sheet Mouse Pad: What to Know
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP in Excel: What If the Return Cell Is Blank?If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Excel Filters: How to Sort and Show the Rows You NeedTo filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
- Excel Function Keys: What F1–F12 DoIn Excel for Microsoft 365 on Windows, the function keys F1–F12 open Help, edit cells, repeat actions, move…
- Excel Trim a Table: Clean Text or Remove Rows?To trim text inside an Excel table, add a column with =TRIM([@[Raw Name]]); to trim the table’s physical…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.