Anyone who works a lot with spreadsheets knows long formulas, where the same expression appears three or four times and one misplaced bracket breaks everything. LET and LAMBDA, available in Excel for Microsoft 365, Excel for the web and Google Sheets, solve two different problems: LET makes a complex formula readable, LAMBDA lets you turn it into a named function to use like any built-in one. Recent versions of LibreOffice Calc include LET too.
LET: naming the steps
The syntax is a series of name and value pairs, followed by the final calculation:
=LET(name1, value1, [name2, value2, ...], calculation)
Take a concrete case. You want to apply a tiered discount: 10% if the net amount, that is price times quantity, exceeds 1,000 euros, otherwise 5%. Without LET:
=IF(B2*C2>1000, B2*C2*0.9, B2*C2*0.95)
The expression B2*C2 appears three times. With LET:
=LET(net, B2*C2, discount, IF(net>1000, 10%, 5%), net*(1-discount))
The formula reads like reasoning: calculate the net, decide the discount, apply it. There are three advantages:
- readability: each step has a name that explains its meaning;
- maintenance: if the net calculation changes, you edit it in one place;
- performance: the expression is calculated only once, which matters when it is heavy, for example a FILTER over thousands of rows.
To write long LET formulas, break lines in the formula bar (Alt+Enter in Excel for Windows) and put each pair on its own line:
=LET(
net, B2*C2,
discount, IF(net>1000, 10%, 5%),
net*(1-discount)
)
Rules for names
Names in LET must start with a letter, cannot contain spaces and must not look like a cell reference: vat is fine, A1 or VAT2026 are not, because the latter could be read as cell VAT2026. A name defined in LET exists only inside that formula.
LAMBDA: creating custom functions
LAMBDA goes one step further: it defines a function with parameters.
=LAMBDA(parameter1, [parameter2, ...], calculation)
Written on its own in a cell, a LAMBDA returns an error, because it is a definition with no arguments. To test it, you can pass it values directly in brackets:
=LAMBDA(price, qty, price*qty*0.9)(B2, C2)
Its real use, though, is to give it a name.
Saving a LAMBDA under a name
In Excel open Formulas, Name Manager (Ctrl+F3), create a new name, for example DISCOUNTEDPRICE, and in the "Refers to" field paste the formula:
=LAMBDA(price, qty, LET(net, price*qty, net*(1-IF(net>1000, 10%, 5%))))
From then on, in any cell of the workbook you can write:
=DISCOUNTEDPRICE(B2, C2)
The discount rule lives in one place only. If tomorrow the threshold becomes 1,500 euros, you edit the definition in the Name Manager and every cell using the function updates. In Google Sheets the same thing is done from Data, Named functions, with a guided interface where you enter the name, parameters and definition.
Examples of useful functions
| Name | Definition | Use |
|---|---|---|
WITHVAT |
=LAMBDA(net, rate, ROUND(net*(1+rate), 2)) |
price including VAT, rounded |
DAYSLATE |
=LAMBDA(due, MAX(0, TODAY()-due)) |
days overdue, never negative |
CLEANUP |
=LAMBDA(t, TRIM(SUBSTITUTE(t, CHAR(160), " "))) |
text without stray spaces |
Functions like these make the sheet's formulas much shorter and less prone to typing errors.
Helper functions
Alongside LAMBDA came some functions that use it to process whole ranges, such as MAP, which applies a LAMBDA to every element, BYROW and BYCOL, which apply it to every row or column, and REDUCE, which accumulates a result. For example, to calculate the total of each row of a table with a single formula:
=BYROW(B2:M50, LAMBDA(row, SUM(row)))
They are powerful tools, but it is worth introducing them gradually: whoever receives the file must be able to understand it.
When not to use them
- Files shared with old versions. LET and LAMBDA do not work in Excel 2019 or earlier: the cells show errors.
- Colleagues who are not used to them. A custom function is invisible until you open the Name Manager. Document it on a "ReadMe" sheet, with name, parameters and purpose.
- Calculations that work better as columns. If an intermediate step is useful to see, a helper column remains more transparent than a name inside a formula.
In my work I use LET almost every time a formula runs beyond one line, and LAMBDA for the few rules that appear in many places across a workbook: rates, rounding, text cleaning.
In short
- LET gives names to the steps of a formula: more readable, easier to change, faster.
- Names in LET must not look like cell references.
- LAMBDA defines a function with parameters; named in the Name Manager it becomes reusable.
- In Google Sheets custom functions are created from Data, Named functions.
- Check compatibility and document custom functions for whoever will use the file.
