LET Me Explain: Why This One Function Fixed the Ugliest Habit in Your Spreadsheets
Because typing the same formula four times in a row is not a personality trait. It’s a cry for help.
Let me paint you a picture.
You’re building a formula. It needs to check something, then do a calculation with that something, then do another calculation with that something, and finally format that something into a sentence for a report. Reasonable request. Excel should handle this easily.
So you write it. And because Excel formulas don’t have variables — or at least, they didn’t used to — you end up writing the same lookup, the same calculation, the same nested mess, four separate times inside one formula. Once to check it. Once to use it in the “if true” branch. Once to use it in the “if false” branch. Once more because you forgot you already wrote it and pasted it in again out of muscle memory.
Your formula is now 400 characters long. It contains the same XLOOKUP repeated so many times that if XLOOKUP were a person, it would have filed a restraining order. It works — technically — but if you need to change one tiny thing about that lookup, you now have to find and update it in four different places, inside one unreadable wall of parentheses, at 6pm, on a Friday, with the file due in ten minutes.
This is the exact moment LET was invented for. And if you’ve never used it, congratulations — you’ve been doing the spreadsheet equivalent of writing out “the customer’s total order value including tax and shipping” longhand every single time instead of just… naming it once and calling it back.
Let’s fix that.
First, What LET Actually Does (The 30-Second Version)
LET lets you name a value, or the result of a calculation, and reuse that name inside the same formula — without repeating the calculation itself.
Instead of calculating something over and over, you calculate it once, give it a name, and then just use the name for the rest of the formula. Like giving a nickname to your painfully complicated coworker’s full official job title so you don’t have to say the whole thing every time you talk about them.
Syntax:
LET(name1, value1, name2, value2, ..., calculation)
You can define as many name/value pairs as you want, and the very last argument is always the actual calculation that uses those names. Excel evaluates each named value exactly once, no matter how many times you reference it afterward. That last part is not a small detail — we’ll come back to it, because it’s the difference between a spreadsheet that opens instantly and one that makes your laptop fan sound like it’s preparing for takeoff.
Three things LET gives you that you didn’t have before:
- You stop repeating yourself. Write the calculation once. Use the name everywhere else.
- Your formulas become readable. Instead of
XLOOKUP(A2,B:B,C:C)*XLOOKUP(A2,B:B,D:D)-XLOOKUP(A2,B:B,E:E)*0.1, you get something that reads almost like a sentence. - Your formulas get faster. Repeated calculations are repeated work. LET calculates once and reuses the result, instead of asking Excel to redo the same expensive lookup or calculation multiple times per cell, multiplied across every row in your sheet.
If you’ve ever built a formula so long that it wrapped around your screen three times and you had genuinely lost track of what it was even trying to do — this function was built directly for you, specifically, as a person.
Two Simple Examples, Because We Start Gentle
Example 1: Stop Repeating a Calculation You’ve Already Done
Say you’re calculating a discounted price. The subtotal is in A2, and if it’s over 100, you apply a 10% discount.
Before LET, a normal person writes this:
=IF(A2*1.22>100,(A2*1.22)*0.9,A2*1.22)
Look at that. A2*1.22 — the price with VAT added — appears three times. If your VAT rate ever changes, or you notice a typo, you now have to fix it in three places inside one cell, and if you miss one of them, your spreadsheet will lie to you silently, with total confidence, forever.
With LET:
=LET(
priceWithVAT, A2*1.22,
IF(priceWithVAT>100, priceWithVAT*0.9, priceWithVAT)
)
You calculate A2*1.22 exactly once, name it priceWithVAT, and then just talk about priceWithVAT like it’s a normal noun for the rest of the formula. Change the VAT rate once, and every reference updates. This is the whole pitch of LET in miniature: calculate once, name it, move on with your life.
Example 2: Making a Formula Actually Readable
Say you want to calculate someone’s age in years from a birthdate in A2, and add a label depending on whether they’re a minor.
Before LET:
=IF(DATEDIF(A2,TODAY(),"Y")<18, DATEDIF(A2,TODAY(),"Y")&" (minor)", DATEDIF(A2,TODAY(),"Y")&" (adult)")
DATEDIF(A2,TODAY(),"Y") — three times. Same calculation, copy-pasted with tiny variations, which is exactly the kind of formula that looks fine today and becomes an archaeology project in eight months when someone else (or future you, who is legally a different person by then) has to edit it.
With LET:
=LET(
age, DATEDIF(A2,TODAY(),"Y"),
IF(age<18, age&" (minor)", age&" (adult)")
)
Nobody needs a computer science degree to read this. age is calculated once, and the formula becomes a sentence you can actually follow: if age is less than 18, say minor, otherwise say adult. This is what “self-documenting” means in practice — the formula explains itself, instead of requiring a private investigator.
💡 Pro Tip: Name your variables like you’d name a variable in real code —
age,priceWithVAT,discountRate— notx,y,temp1. LET doesn’t care either way, but the next person opening this file (probably you) absolutely will.
Now the Extremely Complex Examples — Where LET Stops Being “Nice” and Starts Being Necessary
Simple examples are cute. They show you the syntax. They do not show you why professionals building serious workbooks treat LET as close to mandatory. For that, we need formulas that would be genuinely, actively dangerous without it — the kind of formula that, written the old way, becomes an unmaintainable monster that only one person in the office understands, and that person just put in their two weeks’ notice.
Complex Example 1: The Sales Commission Calculator From Hell
The scenario: A sales manager needs to calculate commission for each rep, per deal, with the following rules — because of course there are rules, there are always rules:
- Look up the rep’s base commission rate from a
Repstable (by rep ID). - Look up the deal size tier bonus from a
Tierstable (by deal value, using an approximate match — bigger deals get a bonus percentage on top). - If the deal was closed in the last 30 days, apply a “fast close” bonus of an additional 2%.
- The final commission is:
(base rate + tier bonus + fast close bonus) × deal value, but capped at a maximum of 25% total rate, because finance set a ceiling and finance’s word is law.
Without LET, here’s what this formula actually looks like when someone tries to write it in one shot:
=IF((XLOOKUP(A2,Reps[ID],Reps[BaseRate])+XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1)+IF(TODAY()-C2<=30,0.02,0))>0.25,0.25,(XLOOKUP(A2,Reps[ID],Reps[BaseRate])+XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1)+IF(TODAY()-C2<=30,0.02,0)))*B2
Read that again. Now read it a third time, because I guarantee you skimmed it the first two times — everybody does, that’s the whole problem. XLOOKUP(A2,Reps[ID],Reps[BaseRate]) appears twice. The entire tier-bonus-plus-fast-close-bonus calculation appears twice, once inside the cap check and once inside the actual result. If finance changes the cap from 25% to 20% next quarter — which finance will absolutely do, without warning, on a Thursday — you now need to hunt down every occurrence of 0.25 across potentially hundreds of formulas, hoping you don’t also accidentally change some unrelated 0.25 that meant something completely different three columns over.
One typo in any one of those four repeated lookups, and the formula still runs. It just quietly returns the wrong commission to the wrong salesperson, who will absolutely notice, and will absolutely bring it up in the next team meeting, and now you’re explaining spreadsheet architecture to someone who wanted to talk about their bonus.
With LET, the exact same logic:
=LET(
baseRate, XLOOKUP(A2,Reps[ID],Reps[BaseRate]),
tierBonus, XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1),
fastCloseBonus, IF(TODAY()-C2<=30,0.02,0),
totalRate, baseRate+tierBonus+fastCloseBonus,
cappedRate, MIN(totalRate,0.25),
cappedRate*B2
)
Every lookup runs exactly once. The 25% cap exists in exactly one place, so when finance moves the goalposts, you change one number. And critically — anyone new to this file can read it top to bottom like a recipe: get the base rate, get the tier bonus, get the fast-close bonus, add them up, cap it, multiply by deal value. That is not a coincidence. That is the entire point of the function.
💡 Pro Tip: Notice
cappedRateis built fromtotalRate, which is built from three other named values — LET lets your named variables reference each other, in order, like a normal chain of reasoning. This is enormously underused and enormously powerful.
Complex Example 2: The Text-Parsing Nightmare That Actually Justifies Its Own Existence
The scenario: You’ve inherited a column of dirty, inconsistent order reference codes from a legacy system, formatted like "ORD-2026-04-88231-EU" — order, year, month, sequence number, region. Someone in operations needs you to extract just the sequence number, but only if the order is from the EU region and was placed in the last two calendar years, otherwise return "N/A". And the codes aren’t perfectly consistent — some have extra spaces, some are lowercase, because of course they are.
Without LET, this is the formula a determined but slightly unhinged person writes at 11pm:
=IF(AND(RIGHT(UPPER(TRIM(A2)),2)="EU",VALUE(MID(UPPER(TRIM(A2)),5,4))>=YEAR(TODAY())-2),VALUE(MID(UPPER(TRIM(A2)),MID(UPPER(TRIM(A2)),1,0)+15,5)),"N/A")
(And realistically it would take several broken attempts to even get this far, because you’d need to calculate the position of the sequence number dynamically, which means nesting a FIND inside a MID inside… you get the idea. This is the formula equivalent of assembling flat-pack furniture using only the picture on the box, with three pieces missing, at midnight.)
UPPER(TRIM(A2)) — the cleaned-up version of the code — needs to happen every single time you touch the string, because you can’t clean it once and refer back to it. You’re cleaning the same messy text three, four, five times inside one formula, which is not just ugly, it’s genuinely wasteful: Excel is redoing the same TRIM and UPPER operations over and over on every single row, across potentially tens of thousands of rows, for no reason except that the language didn’t give you a way to say “do this once.”
With LET:
=LET(
cleanCode, UPPER(TRIM(A2)),
region, RIGHT(cleanCode,2),
orderYear, VALUE(MID(cleanCode,5,4)),
sequencePosition, FIND("-",cleanCode,FIND("-",cleanCode,FIND("-",cleanCode)+1)+1)+1,
sequenceNumber, VALUE(MID(cleanCode,sequencePosition,5)),
isRecentEU, AND(region="EU", orderYear>=YEAR(TODAY())-2),
IF(isRecentEU, sequenceNumber, "N/A")
)
This is not a shorter formula. It is, however, a formula a human being can actually maintain six months from now without opening it, sighing audibly, and starting over from scratch. cleanCode is computed once and reused four times. Each named step describes exactly what it’s doing — you can trace the logic in plain English just by reading the variable names top to bottom: clean it, get the region, get the year, find where the sequence number starts, extract it, check if it qualifies, return the result or “N/A.”
If operations comes back next month and says “actually we also need to check the APAC region” — you edit one line (isRecentEU) instead of hunting through a 300-character wall of nested MIDs and FINDs trying to figure out which parenthesis belongs to which clause. This is the difference between a formula you can hand off to a colleague and a formula that only survives as long as you personally remain employed at the company.
⚠️ Watch out: In the “without LET” version, one wrong parenthesis anywhere in that soup and Excel gives you a cryptic error — or worse, no error at all, just a silently wrong answer. Untangling which of the five repeated
UPPER(TRIM(A2))instances has the typo is its own special kind of Tuesday. LET doesn’t just make formulas readable — it makes them debuggable, because when something’s wrong, you can check one named step at a time instead of reverse-engineering a novel.
The Performance Angle Nobody Mentions Enough
Here’s the part that turns LET from “nice for readability” into “actually necessary at scale”: every time a value is repeated in a normal formula, Excel recalculates it. Every. Single. Time. Across every row.
In the commission formula above, XLOOKUP(A2,Reps[ID],Reps[BaseRate]) ran twice per cell in the non-LET version. Multiply that across 10,000 rows of sales data, and you’ve just asked Excel to perform 10,000 unnecessary lookups for absolutely no benefit — pure wasted computation, just sitting there making your file slower to open, slower to recalculate, and more likely to freeze at the exact moment you’re trying to demo it live in a meeting.
LET calculates each named value exactly once per row, no matter how many times it’s referenced afterward. On a small sheet, you won’t notice. On a large one — thousands of rows, several LET-worthy formulas, a few expensive lookups — the difference between the repeated version and the LET version can be the difference between a spreadsheet that recalculates instantly and one that makes you stare at the little “Calculating… (4 processors)” message in the status bar like it owes you money.
Pro Tips: The Stuff That Makes LET Actually Click
Combine LET with LAMBDA for reusable custom functions. Once you’re comfortable naming values inside a formula, the natural next step is wrapping a LET formula inside LAMBDA and saving it as a named function in the Name Manager — effectively building your own custom Excel function, with your own name, that you can reuse across the entire workbook. That’s a whole article on its own, but know that it exists and that LET is the gateway drug to it.
You don’t need to use every name in the final calculation. You can define a helper value purely to make an earlier step readable, even if it’s just feeding into another named value rather than the final output. Nobody’s checking your work. Name things for clarity, not economy.
Order matters. Each named value can only reference names defined before it, not after. LET reads top to bottom, like a recipe — you can’t use an ingredient you haven’t measured out yet.
Nest LET inside array formulas and SUM/COUNT patterns. LET plays nicely inside SUMPRODUCT, FILTER, and other array-returning functions, letting you name intermediate arrays instead of recalculating them at every step. This is where LET quietly becomes essential for anyone doing serious dynamic-array work, not just single-cell formulas.
Rename with F2 confidence. If you rename a LET variable, Excel doesn’t auto-update every reference to it the way a real programming environment would — you have to update each usage yourself. It’s not perfect. It’s still enormously better than nothing.
The Bottom Line
LET doesn’t add any new mathematical capability to Excel. It can’t calculate anything you couldn’t already calculate before. What it does is remove the tax you were paying every time you wrote a formula that needed the same value more than once — the tax of repetition, unreadability, wasted recalculation, and formulas so tangled that changing one number meant hunting through four different hiding spots hoping you found them all.
Simple formulas get easier to read. Complex formulas — the ones with nested lookups, layered conditions, and text parsing that would otherwise require a treasure map — go from “technically works, understood by exactly one person, that person is currently on vacation” to “readable by anyone who opens the file, edited safely by changing one line instead of five.”
Next time you catch yourself typing the same XLOOKUP or the same TRIM(UPPER(...)) for the third time inside one formula, stop. Wrap it in LET. Name it something sensible. Thank yourself in six months.
And the next time a colleague hands you a 400-character formula with the same lookup repeated four times and says “I don’t know why it’s so slow” — you’ll know exactly what to tell them.
Shay Stibelman is a digital consultant based in Milan. He helps businesses and educators work better with the digital tools they already have, and has strong, possibly excessive opinions about people who write the same XLOOKUP three times in one cell. He writes at blog.stibelman.com and makes video tutorials for people who’d rather watch someone else’s formula break first.
Coming up next: LET’s cooler, more ambitious sibling — LAMBDA, or how to build your own custom Excel functions without writing a single line of VBA.