Excel LET

Stop repeating yourself. Start naming your intermediate calculations.

What Even IS LET?

Alright, picture this. You’ve got this formula. It calculates something. And in the middle of it, you need the same sub-calculation three different times. Like, you’re calculating a discount, and you need “price * quantity” for the subtotal, then again for the tax, then again for the shipping. So you copy-paste that same chunk three times. Your formula is now a mile long. If you need to change how you calculate the base amount, you have to hunt down every single copy and fix it. Painful.

The Basic Idea

LET takes pairs of things:

1. A name — whatever you want to call your intermediate result (like subtotal or taxRate)
2. The calculation — what that name actually equals

You can have as many name/value pairs as you want. The last argument is the actual result you want the formula to return.

The Structure (Don’t Panic)

That’s it. Name, value, name, value… final answer. You can even use earlier names in later calculations!

Why Should You Care?

Easy Warming Up

Example 1: Rectangle Area — Named Steps

Let’s start simple. You have length in A1, width in B1. You want the area. Without LET you’d just do =A1*B1. With LET, you name the steps so anyone reading it knows what’s happening.

That’s three name/value pairs, then the final result (area). Notice how area uses the names length and width we just defined? That’s the magic.

Example 2: Price with Tax — One Name

You have a price in A1. Tax rate is 8%. You want the total. One intermediate name makes this readable.

Four pairs. The last line price + taxAmount is what gets returned. You could also name that total and return total — same thing.

Example 3: Convert Minutes to Hours & Minutes

Cell A1 has 137 minutes. You want “2 hours 17 minutes” as text.

Returns "2 hours 17 minutes". The INT gives whole hours, MOD gives the remainder. Each step has a name. Easy to debug.

Medium Getting the Hang of It

Example 1: Invoice Line Total with Quantity Discount

You’re building an invoice. Price in B2, quantity in C2. If they buy 10+, they get 10% off. If 25+, 15% off. You need the line total.

See how discount uses qty? And discountAmt uses subtotal AND discount? You can chain them however you want. The final line returns the discounted total.

Example 2: Grade Calculator with Weighted Categories

Homework (30%), Quizzes (20%), Midterm (25%), Final (25%). Scores in B2:E2. You want the final grade.

Eight name/value pairs. Each weighted piece gets a name. The final line adds them all up. Way easier to verify than one giant nested formula.

Example 3: Extract Domain from Email Address

Cell A1 has "student@university.edu". You want just "university.edu".

Hard Now We’re Cooking

Example 1: Running Total by Category (No Helper Column!)

You have a list of expenses. Column A = Category, Column B = Amount. You want a running total for each category in column C. Normally you’d need a helper column or a pivot table. With LET + FILTER, you can do it in one formula.

Put this in C2 and drag down. allCats and allAmts expand as you go (mixed references: $A$2:A2). FILTER grabs only amounts for the current row’s category. SUM adds them up. Each row gets its category’s running total. No helper columns needed.

Example 2: Dynamic Tiered Commission Calculator

Sales in A2. Tiers: 0–10K = 5%, 10K–25K = 7%, 25K–50K = 10%, 50K+ = 12%. But it’s marginal — only the dollars in each tier get that tier’s rate. This is the kind of formula that makes people quit Excel. LET makes it survivable.

Yeah, it’s long. But look at it: every tier boundary has a name. Every rate has a name. Every dollar-amount-in-each-tier has a name. The final line is just amt1*r1 + amt2*r2 + amt3*r3 + amt4*r4. If the boss changes the tier boundaries next month, you edit the first 8 lines. The logic stays intact. Try doing that with a nested IF nightmare.

Example 3: Clean Messy Phone Numbers to (XXX) XXX-XXXX

You imported data. Phone numbers are a disaster: "5551234567", "555-123-4567", "(555) 123-4567", "555.123.4567", maybe even "+1 555 123 4567". You want them all formatted consistently.

Pro LET + LAMBDA = Superpowers

This is where it gets fun. You can put LET inside a LAMBDA. That means your custom named functions can have their own internal variables. Clean, organized, reusable.

Example: Custom Function for Compound Annual Growth Rate (CAGR)

CAGR = (Ending Value / Beginning Value)^(1/Years) – 1. Let’s make this a named function using LAMBDA + LET.

In Name Manager (Ctrl+F3):

Name: CAGR

Refers to:

Use it:

The LET inside keeps the LAMBDA readable. ratio and annualFactor are internal to the function — the person using =CAGR() never sees them. They just get the answer.

Cheat Sheet: The Rules

1. Names must start with a letter. No numbers, no spaces. subtotal ✓ 1stTotal ✗ sub total ✗
2. Names are case-insensitive. subtotal and SubTotal are the same. Pick a style (camelCase or snake_case) and stick with it.
3. You can’t use names that look like cell references. AB1 or Sales might conflict. When in doubt, use descriptive names like salesTotal.
4. Earlier names can be used in later values. That’s the whole point. LET(a, 1, b, a+2, ...) works fine.
5. The last argument is ALWAYS the result. No name, just the value you want returned.
6. Max 126 name/value pairs. If you need more, you’re probably doing something that should be a separate function or helper column.
7. LET calculates each name ONCE. Performance win over repeating the same sub-calculation.
8. LET + LAMBDA = named functions with internal variables. This is the pro move. Use it.

Stuff You’ll Probably Mess Up (It’s Fine, We All Do)

• Forgetting the final result. You define 5 names and forget the last argument. Formula returns the 5th name’s value instead of what you wanted. Always double-check the last item.
• Using a name before you define it. LET(b, a+1, a, 5, ...) — nope, a doesn’t exist yet when b tries to use it. Order matters.
• Naming something “sum” or “count” or “average”. These are function names. Excel gets confused. Use sumTotal or totalSum instead.
• Too many pairs for no reason. If you only use a name once, you don’t need LET for it. Just inline it. LET is for reused calculations.
• Thinking LET works across cells. LET names only exist inside that ONE formula. Cell B2 can’t see names from A2’s LET. That’s what LAMBDA + Name Manager is for.

Now You Try

Close this page (well, after you read this), open Excel, and build these with LET:

1. Circle stats. Radius in A1. Return “Area: X, Circumference: Y” using LET for radius, area, circumference, then concatenate.
2. Tip calculator with split. Bill in A1, tip% in B1, people in C1. Return “Each person pays: $X”. Use LET for tipAmount, total, perPerson.
3. Letter grade from weighted scores. HW (40%), Quiz (20%), Test (40%) in B1:D1. Use LET for each weighted piece, then sum.
4. Days between dates, formatted. Start in A1, End in B1. Return “X years, Y months, Z days” using LET for years, months, days (hint: DATEDIF function).
5. Clean a messy SSN. A1 has “123-45-6789” or “123456789” or “123 45 6789”. Return “123-45-6789” using LET to strip non-digits then format.

If you can build all five without peeking back, you officially get LET.

Go name some variables. You’ve got this.