Microsoft 365 added two functions that change how formulas are written. LET lets you name pieces of a formula so it is shorter and faster. LAMBDA lets you package a formula as your own function — with no VBA and no macro security warnings.
In this article
LET: name the parts
Before:
=IF(SUMIFS(F:F,A:A,H2)>100000, SUMIFS(F:F,A:A,H2)*0.05, SUMIFS(F:F,A:A,H2)*0.02)After:
=LET(sales, SUMIFS(F:F, A:A, H2),
rate, IF(sales > 100000, 5%, 2%),
sales * rate)The SUMIFS is calculated once instead of three times, and a colleague can read the logic. Syntax: LET(name1, value1, name2, value2, …, result).
LAMBDA: your own function in three steps
- Write and test the formula as a LAMBDA directly in a cell, passing a test value at the end:
=LAMBDA(t, PROPER(TRIM(SUBSTITUTE(t, CHAR(160), " "))))(A2) - When it works, open Formulas → Name Manager → New. Name:
CLEANTEXT. Refers to:=LAMBDA(t, PROPER(TRIM(SUBSTITUTE(t, CHAR(160), " ")))) - Now use it anywhere in the workbook:
=CLEANTEXT(A2).
Five useful LAMBDAs to copy
| Name | Refers to | Use |
|---|---|---|
| CLEANTEXT | =LAMBDA(t, PROPER(TRIM(SUBSTITUTE(t, CHAR(160), " ")))) |
Tidy names from exports |
| FYEAR | =LAMBDA(d, "FY" & TEXT(EDATE(d, -3), "yy") & "-" & TEXT(EDATE(d, 9), "yy")) |
Indian financial year label (Apr–Mar) |
| PCTCHANGE | =LAMBDA(old, new, IF(old=0, "", (new-old)/old)) |
Safe % change |
| WORKDAYSLEFT | =LAMBDA(d, NETWORKDAYS(TODAY(), d)) |
Working days until a deadline |
| INITIALS | =LAMBDA(n, CONCAT(LEFT(TEXTSPLIT(TRIM(n), " "), 1))) |
“Asha Sharma” → “AS” |
💡 Add a comment in Name Manager describing the arguments — it appears as a tooltip when you type the function.
Things to know
- LAMBDA functions live in the workbook. To reuse them elsewhere, copy a sheet that uses them into the new workbook, or keep a template file.
- They need Microsoft 365 or Excel 2024; older versions show
#NAME?. - Helper functions like
MAP,BYROW,SCANandREDUCEtake a LAMBDA to apply it to every item — e.g.=BYROW(B2:M50, LAMBDA(r, SUM(r)))sums each row.
Paste any long formula into the Formula Explainer to see how it breaks down before rewriting it with LET.