How to Use LET Function in Excel
Excel's LET function fundamentally changes how we write formulas. Introduced in 2020, LET allows you to assign names to calculation results within a formula, then reference those names instead of...
Key Insights
- The LET function eliminates redundant calculations by storing intermediate results in named variables, significantly improving performance in complex formulas
- Named variables transform cryptic nested formulas into readable, self-documenting code that’s easier to debug and maintain
- LET is available only in Microsoft 365 and Excel 2021+; older versions require workarounds using helper columns or named ranges
Introduction to the LET Function
Excel’s LET function fundamentally changes how we write formulas. Introduced in 2020, LET allows you to assign names to calculation results within a formula, then reference those names instead of repeating the same expressions multiple times.
Before LET, complex formulas often became unreadable tangles of nested functions with repeated calculations. If you needed to use VLOOKUP(A2,$D$2:$E$100,2,FALSE) three times in one formula, Excel would perform that lookup three separate times—wasting computational resources and making your formula harder to understand.
LET solves both problems. It improves performance by calculating each expression once and storing the result. It improves readability by letting you use descriptive names like customerDiscount instead of repeating INDEX(MATCH(...)) throughout your formula.
Basic Syntax and Structure
The LET function follows this pattern:
=LET(name1, value1, [name2, value2, ...], calculation)
You define one or more name-value pairs, followed by a final calculation that uses those names. The calculation result becomes the formula’s output.
Here’s a simple example calculating a discounted price:
=LET(price, A2, discount, 0.15, price * (1-discount))
This creates two variables: price (assigned the value from A2) and discount (assigned 0.15), then calculates the final discounted price. If A2 contains 100, the formula returns 85.
Variable names must follow these rules:
- Cannot conflict with cell references (avoid names like “A1” or “XFD”)
- Cannot be existing Excel functions
- Cannot contain spaces or special characters
- Are case-insensitive
You can chain as many variable assignments as needed:
=LET(
base, A2,
rate, B2,
years, C2,
periods, years * 12,
monthlyRate, rate / 12,
base * (1 + monthlyRate) ^ periods
)
Eliminating Redundant Calculations
The performance benefits of LET become clear when you’re repeating expensive operations. Consider this formula that uses the same VLOOKUP three times:
=IF(VLOOKUP(A2,$D$2:$E$100,2,FALSE)>1000,
VLOOKUP(A2,$D$2:$E$100,2,FALSE)*0.1,
VLOOKUP(A2,$D$2:$E$100,2,FALSE)*0.05)
Excel executes that VLOOKUP three separate times—once for the comparison, once for each multiplication. With LET, you perform the lookup once:
=LET(
orderValue, VLOOKUP(A2,$D$2:$E$100,2,FALSE),
IF(orderValue>1000, orderValue*0.1, orderValue*0.05)
)
The performance difference multiplies across hundreds or thousands of rows. I’ve seen spreadsheets where converting repeated calculations to LET reduced recalculation time from minutes to seconds.
This applies to any expensive operation: INDEX/MATCH combinations, SUMIFS with multiple criteria, array formulas, or complex text parsing functions.
Improving Formula Readability
Beyond performance, LET transforms incomprehensible formulas into readable logic. Consider this nested formula determining employee bonuses:
=IF(AND(C2>=5,D2>=90000),D2*0.15,IF(AND(C2>=3,D2>=60000),D2*0.10,IF(C2>=1,D2*0.05,0)))
What does this do? You have to mentally parse the conditions and trace through the nested IFs. With LET:
=LET(
yearsOfService, C2,
salary, D2,
isSenior, AND(yearsOfService>=5, salary>=90000),
isMid, AND(yearsOfService>=3, salary>=60000),
isJunior, yearsOfService>=1,
IF(isSenior, salary*0.15,
IF(isMid, salary*0.10,
IF(isJunior, salary*0.05, 0)))
)
The logic becomes self-documenting. Anyone reading this formula immediately understands the business rules without decoding cell references and nested conditions.
Practical Use Cases
Financial Calculations: Compound Interest
When calculating loan payments or investment returns, you often need multiple intermediate values:
=LET(
principal, B2,
annualRate, B3,
years, B4,
compoundsPerYear, 12,
totalPeriods, years * compoundsPerYear,
periodRate, annualRate / compoundsPerYear,
futureValue, principal * (1 + periodRate) ^ totalPeriods,
totalInterest, futureValue - principal,
futureValue
)
This calculates compound interest with monthly compounding. Each variable has a clear purpose, and you can easily modify the formula to return totalInterest instead of futureValue by changing the last line.
Data Analysis: Statistical Metrics
When analyzing data sets, you often need multiple statistical measures based on filtered data:
=LET(
salesData, A2:A100,
threshold, 5000,
qualifyingSales, FILTER(salesData, salesData>=threshold),
avgHighSales, AVERAGE(qualifyingSales),
countHighSales, COUNTA(qualifyingSales),
percentHigh, countHighSales / COUNTA(salesData),
"Average: " & TEXT(avgHighSales,"$#,##0") &
" | Count: " & countHighSales &
" | Percent: " & TEXT(percentHigh,"0.0%")
)
This single formula produces a formatted summary of high-value sales without helper columns.
Text Manipulation: Parsing Email Addresses
When working with text data, LET makes complex string operations manageable:
=LET(
email, A2,
atPosition, FIND("@", email),
username, LEFT(email, atPosition-1),
domain, MID(email, atPosition+1, LEN(email)),
dotPosition, FIND(".", domain),
domainName, LEFT(domain, dotPosition-1),
PROPER(username) & " (" & UPPER(domainName) & ")"
)
This parses an email address and formats it as “John.Smith (COMPANY)”. Without LET, you’d need multiple helper columns or an unreadable nested formula.
Common Pitfalls and Best Practices
Don’t over-engineer simple formulas. If your formula is just A2*B2, wrapping it in LET adds complexity without benefit. Use LET when you have repeated calculations or complex logic that benefits from decomposition.
Use descriptive variable names. Names like x, y, temp defeat the readability purpose. Choose names that explain what the value represents: taxableIncome, shippingCost, isWeekend.
Remember variable scope. Variables defined in LET only exist within that LET function. You cannot reference them in other cells or formulas.
Debug systematically. When a LET formula returns an error, test each variable individually. Replace the final calculation with just the variable name to see its value:
=LET(
price, A2,
discount, B2,
finalPrice, price * (1-discount),
finalPrice // Change this to 'price' or 'discount' to debug
)
Avoid circular references. You cannot reference a variable before it’s defined. Variables are evaluated in order, so place dependencies before the variables that use them.
Consider formula length. While LET improves readability, extremely long formulas (even well-structured ones) can be hard to maintain. If your LET formula spans 30+ lines, consider whether the logic belongs in VBA or Power Query instead.
Compatibility and Alternatives
LET requires Microsoft 365 or Excel 2021 or later. It’s not available in Excel 2019, 2016, or earlier versions, nor in Excel for the web (as of this writing, though Microsoft is rolling it out).
If you’re stuck with older Excel versions, you have alternatives:
Helper columns are the traditional approach. Break your calculation into steps across multiple columns. This is actually easier to debug than complex formulas, but it clutters your worksheet and can expose intermediate calculations you’d prefer to hide.
Named ranges (Formulas → Define Name) let you assign names to values or formulas, but they’re worksheet-scoped, not formula-scoped. They work for constants or simple references but can’t replace LET’s ability to create formula-specific intermediate calculations.
LAMBDA function (also Microsoft 365 only) lets you create custom functions with parameters. While more powerful than LET, it’s also more complex and better suited for reusable functions rather than one-off calculations.
The bottom line: if you have access to LET, use it. It’s one of the most significant improvements to Excel’s formula language in years, making your spreadsheets faster, more readable, and easier to maintain.