How to Use the LET Function in Excel: A Step‑by‑Step Guide
What Is the LET Function?
The LET function lets you assign names to intermediate results inside a single formula. By storing those values in variables you can:
| Benefit | Why It Matters |
|---|---|
| Readability | A long formula becomes a series of named steps, making it easier to follow. |
| Performance | Each named expression is evaluated only once, which speeds up calculations that would otherwise be repeated. |
| Maintainability | Changing a value or logic in one place updates every reference automatically. |
Note: LET is available only in Excel for Microsoft 365, Excel 2021, and later desktop versions. Earlier releases (Excel 2019, 2016, etc.) return
#NAME?.
Syntax
LET(name1, value1, [name2, value2, …], calculation)
| Argument | Description |
|---|---|
| nameX | Your chosen identifier (letters, numbers, underscores; no spaces or reserved words). |
| valueX | The expression or constant that the name represents. |
| calculation | The final expression that can use any of the defined names. |
All arguments are required, and you can define as many name/value pairs as you need before the final calculation.
Simple Example
| A | B | |
|---|---|---|
| 1 | X | Y |
| 2 | 10 | 5 |
| 3 | 20 | 8 |
=LET(
x, A2,
y, B2,
total, x + y,
product, x * y,
total & " / " & product
)
Result: 15 / 50
x and y hold the values from A2 and B2 (10 and 5), total and product compute intermediate results, and the final expression concatenates them.
Benefits of Using LET
| Benefit | Why It Matters |
|---|---|
| Improved speed | Repeated sub‑expressions are calculated once, which can cut processing time on large data sets. |
| Cleaner formulas | Meaningful names replace tangled parentheses, making troubleshooting faster. |
| Easier debugging | You can change a single variable definition to test a part of the formula without hunting through the whole expression. |
| Reuse inside a formula | The same intermediate result can be referenced many times without extra calculations. |
| Better with dynamic arrays | Store an array once (e.g., a FILTER result) and reuse it, preventing repeated spills. |
Basic Example with a Discount Calculation
| Qty | UnitPrice |
|---|---|
| 3 | 15.00 |
| 5 | 8.40 |
| 2 | 22.75 |
To calculate the discounted total for the first row (Qty × UnitPrice × 0.9):
=LET(
qty, A2,
price, B2,
discount, 0.9,
qty * price * discount
)
Result: 40.5
The variable names make the intention obvious and ensure each sub‑calculation runs only once.
Advanced Usage: Multiple Variables
When a formula works with ranges, naming each component can dramatically improve readability and performance.
=LET(
qty, A2:A6,
price, B2:B6,
disc, C2:C6,
net, qty * price * (1 - disc),
total, SUM(net),
total
)
| A (Qty) | B (Price) | C (Discount) |
|---|---|---|
| 10 | 5.00 | 0.10 |
| 7 | 12.00 | 0.00 |
| 3 | 8.50 | 0.05 |
| 15 | 2.75 | 0.20 |
| 4 | 20.00 | 0.15 |
Result: 254.225 (45 + 84 + 24.225 + 33 + 68)
| Variable | Meaning |
|---|---|
qty |
Quantities (A2:A6) |
price |
Unit prices (B2:B6) |
disc |
Discount rates (C2:C6) |
net |
Line‑level net amount |
total |
Sum of all net amounts |
Tips for Multiple Variables
- Separate assignments with commas – the final argument must be the calculation that uses the names.
- Keep the list short – only define variables you actually reuse; extra names add overhead.
- Use array‑compatible functions (
FILTER,SORT,XLOOKUP) to stay within the dynamic‑array engine.
Performance Tips & Common Pitfalls
| Tip | Why It Matters | How to Apply |
|---|---|---|
| Name only what you reuse | Names used once add length without saving work. | Define a name when it appears two or more times or makes the formula clearer. |
| Know what volatile functions do | NOW(), RAND(), INDIRECT() and OFFSET() still recalculate on every change, even inside LET. LET only stops the same name from being calculated twice inside one formula. |
Use RAND() or NOW() once as a named value (LET(r, RAND(), r*r)) so the formula uses one consistent result. |
| Reuse, don’t repeat | Computing the same sub‑expression multiple times defeats the purpose of LET. | =LET(t, SUM(A2:A1000), t/COUNT(A2:A1000)) calculates the sum once. |
| Prefer modern lookup functions | XLOOKUP, FILTER, and INDEX/MATCH work natively with arrays, keeping LET efficient. |
=LET(arr, Sheet2!A:A, ret, Sheet2!B:B, XLOOKUP(D2, arr, ret, "Not found")). |
| Test on a small range first | Large ranges can hide performance issues. | Build the formula on a subset, verify the result, then expand the range. |
Integrating LET with Other Functions
Combining LET with other formulas lets you compute a heavy sub‑expression once and reuse it in several places.
| Function | Reason to Combine | Example |
|---|---|---|
| SUM / AVERAGE | Compute a subtotal once, then use it for multiple aggregates. | =LET(rng, B2:B20, total, SUM(rng), avg, total/COUNT(rng), avg) |
| IF | Test a condition that requires a costly calculation only once. | =LET(v, VLOOKUP(A2,Data!A:B,2,FALSE), IF(v>100,"High","Low")) |
| FILTER (365/2021) | Store a filtered array and reuse it for other calculations. | =LET(src, FILTER(Table1[Score], Table1[Pass]=TRUE), AVERAGE(src)) |
| XLOOKUP | Keep the lookup and return arrays in variables for readability and reuse. | =LET(keys, Sheet2!A:A, vals, Sheet2!B:B, XLOOKUP(D2, keys, vals, "Not found")) |
| TEXT | Build a formatted date string once and embed it in several concatenations. | =LET(d, TODAY(), f, TEXT(d,"mmm dd, yyyy"), "Report for "&f&" – "&f) |
Practical Pattern
=LET(
data, A2:A100,
clean, FILTER(data, data<>""),
total, SUM(clean),
avg, total/COUNT(clean),
"Total: "&total&", Avg: "&TEXT(avg,"0.00")
)
When Not to Use LET
| Situation | Why LET Isn’t Ideal | Recommended Alternative |
|---|---|---|
| Very short formulas (one‑ or two‑step) | The overhead of naming outweighs readability gains. | Write the expression directly, e.g., =A1*B1. |
| Legacy workbook sharing | Users with Excel 2016 or earlier see #NAME?. |
Stick to traditional formulas or provide a separate legacy version. |
| Spilling dynamic‑array results | LET does not affect how a formula spills; naming can make debugging spilled ranges harder. | Use native dynamic‑array functions without wrapping them in LET unless you need to reuse a sub‑expression. |
| Deeply nested LET blocks | Too many nested LETs become as hard to follow as a giant IF. |
Break the logic into helper columns or use named ranges. |
| Sharing with Excel 2019 or older | These versions do not have LET and show #NAME?. |
Use standard formulas or helper cells for those users. |
Bottom line: Deploy LET when a formula contains repeated sub‑expressions, is long enough to benefit from naming, and the workbook will be used only in Excel 365/2021 or later. For quick calculations, shared legacy files, or cross‑platform projects, stick with conventional formulas.
FAQ
What versions of Excel support the LET function?
LET is available in Excel for Microsoft 365, Excel 2021, and later desktop releases. Earlier versions (Excel 2019, 2016, etc.) return #NAME?.
Can I use LET in Google Sheets?
Yes. Google Sheets added a LET function with the same name/value syntax, so simple LET formulas usually work in both. Excel-only functions inside the formula (for example TEXTSPLIT) still will not.
How does LET improve calculation speed?
LET evaluates each named expression only once, even if the name is referenced multiple times. This eliminates redundant calculations, which can noticeably speed up formulas that operate on large ranges or contain expensive functions.
Is LET compatible with array formulas?
Yes. LET works with dynamic‑array functions such as FILTER, SORT, UNIQUE, and XLOOKUP. You can store an array in a variable and reuse it without triggering multiple recalculations.
What are common mistakes when using LET?
Typical errors include: using a name before it’s defined, redefining a name within the same LET block, and forgetting that LET is not supported in Excel 2019 or older. Variable names also cannot look like cell references (for example A1 or R2C3). Keeping the variable list concise and testing on a small data set helps avoid these pitfalls.