📎 This article includes 2 downloadable practice files ↓
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” |
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.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 44 KB)
- 📗Dynamic arrays scenarios workbookFILTER, SORT, UNIQUE, SEQUENCE and LET with PASS/FAIL checks, a 'Try it' sheet with spilling formulas, and the two FILTER mistakes that break reports.⬇ XLSX · 10 KB
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.