Advanced Excel · Interactive lesson 3 of 12
Formula engineering with LET
A long formula that repeats the same sub-expression three times is slow to read and slow to fix. LET names each intermediate result once, so the final formula reads like a sentence instead of a wall of nested parentheses.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialCleaner Formulas with LET in ExcelSkill check
Try these first:
- Rewrite a formula that repeats
B2/C2twice so it's only calculated once. - Explain the difference between
IFERRORandIFNA. - Break a long formula into pieces to test each one separately before combining them.
All familiar? Skip ahead to A4 — Advanced Lookup Patterns.
Concept teach
What you're learning
LET=LET(name1,value1,name2,value2,…,calculation)— binds each name once, then the final calculation can reuse them by name instead of repeating the expression.- Naming intermediate results
- Turn
B2/C2, referenced three times, into one name —ratio— calculated once and reused. - Readable nested formulas
=LET(s,sales,t,target,IF(s/t>=1,"Met","Below"))reads closer to English than the same logic crammed into one line.IFERRORvs.IFNAIFERRORcatches every error type — sometimes hiding a real mistake.IFNAcatches only#N/A(a lookup miss), letting other errors like#DIV/0!surface so you notice them.- Testing formulas in small pieces
- Build and check
sales/targeton its own before wrapping it inIF, and wrap it inIFbefore wrapping that inLET— verify each layer before adding the next.
Bonus multiplier table — lives in F2:G5
| F — Rep | G — Multiplier |
|---|---|
| Ari | 1.1 |
| Mina | 1.0 |
| Jon | 1.2 |
| Dev | 1.15 |
Notice who's missing — Lena isn't in this table at all.
Where to find it in Excel
Formulas›Logical›LET
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Returns | Bonus | |||||
| 2 | Ana | 820 | 40 | 78.00 | |||||
| 3 | Ben | 410 | 25 | 19.25 | |||||
| 4 | Chris | 1,050 | 90 | 96.00 | |||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Swipe sideways to see the whole window.
- 1LET and LAMBDA are listed under Logical.
- 2LET(name, value, …, result) names each intermediate step.
- 3Press Alt+Enter in the formula bar to put each name on its own line; Ctrl+Shift+U expands the bar.
- 4IFERROR and IFNA, also under Logical, catch errors on purpose.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Column D rewrites a bonus-status check with LET. Column E adds IFNA for the rep missing from the multiplier table above.
Independent extension
Rewrite your longest formula
Find the longest, most-repeated formula in a workbook you use and rewrite it with LET, naming every intermediate step. Then decide, for each place it could fail: does a missing lookup deserve IFNA, or would IFERROR quietly hide a mistake worth seeing?
Spot a mistake or have an idea for this lesson? Send feedback