Hi ,

Before we dive into today’s Excel Newsletter, just a quick heads-up that for a limited time all our courses are 20% off, including upgrades and renewals for existing students.

Ok, back to business.

Excel LET Function

Have you ever written a long Excel formula that broke the moment you changed a single value? The LET function can help you avoid those headaches while improving both readability and performance. Whether you're new to Excel or an advanced user, LET is a game-changer.

Prefer to read this on our website? 

Then click this link >>> Excel LET Function

Video: Excel LET Function

Watch on YouTube

What Is the LET Function?

The LET function allows you to define named variables within your formula, just like defining variables in programming. These variables can be:

• Cell references
• Constants/Text/Numbers
• Calculated expressions
• Arrays
• Boolean TRUE/FALSE values
• Defined Names

Once defined, you can reuse them throughout your formula, making it cleaner, easier to debug, and faster to calculate.

LET is available in:

• Excel for Microsoft 365
• Excel Online
• Excel 2021 and newer
• Not available in Excel 2019 or earlier

LET Syntax

The LET Function syntax is made up of name and value pairs, followed by a calculation:

=LET(

name1, value1, 

name2, value2, ..., 

calculation)

• Each name/value pair defines a variable.
• The last argument is the final calculation that uses the variables.
• There must always be an odd number of arguments.

Example 1: Simplify a Sales Commission Formula

Let's start with a common formula for calculating commissions. Without LET it looks like this:

=IF(C5>10000, C5*(0.1+0.02), C5*0.1)

This gives a 12% commission for sales over 10,000, and 10% otherwise.

With LET, we can make this formula clearer:

Benefits:

• Variables like sales, limit, and baseRate make the logic easier to follow.
• When you return to the workbook later (or hand it off), the formula is self-explanatory.
• Tip: Use ALT+ENTER to add line breaks in your formula to make it easier to read.

Example 2: Improve Performance in Repetitive Calculations

LET also helps reduce redundant calculations - boosting performance.

Here's a classic case where the same expression is repeated multiple times:

=IF(
([@Sales]-[@COGS])/[@Sales] > 0.4,
"High (" & TEXT(([@Sales]-[@COGS])/[@Sales], "0.0%") & ")",
IF(
([@Sales]-[@COGS])/[@Sales] > 0.25,
"Medium (" & TEXT(([@Sales]-[@COGS])/[@Sales], "0.0%") & ")",
"Low (" & TEXT(([@Sales]-[@COGS])/[@Sales], "0.0%") & ")"
)
)

The formula above calculates the profit margin 5 times!

With LET we only need to calculate it once:

=LET(
cost, [@COGS],
revenue, [@Sales],
margin, (revenue - cost) / revenue,
label,
IF(margin > 0.4, "High",
IF(margin > 0.25, "Medium", "Low")),
label & " (" & TEXT(margin, "0.0%") & ")"
)

Benefits:

• The formula is shorter and easier to follow.
• The margin is only calculated once resulting in improved efficiency.

Debugging LET Formulas

One challenge with LET is that you can't directly evaluate intermediate variables in the formula bar.

But there's a workaround. Let's say you want to evaluate the margin calculation. We can assign the variable you want to inspect to a name, I'll call it calc and return that as the final argument; in the formula below Excel will show the result of margin:

=LET(
cost, E2,
revenue, D2,
margin, (revenue - cost) / revenue,
calc, margin,
calc
)

Then when you're finished inspecting the result of the margin calc, you can replace the last argument with your final expression/calculation.

LET vs Named Ranges

You might wonder: isn't this just like using Named Ranges?

Named Ranges:

• Defined in the Name Manager
• Global scope - can be reused across your workbook

LET Variables:

• Defined inline
• Local scope - exist only within a single formula

You can use both together but the LET function offers flexibility when you want the formula to be fully self-contained.

When Not to Use LET

While the LET function is powerful, it's not always the best choice:

🚫 Single-use variables
If a value is only used once, naming it adds unnecessary complexity.

🚫 Logic split across columns
If you're already breaking calculations into separate columns, that's often easier to read than a single LET-packed formula.

✅ Long formulas
LET is perfect for simplifying long formulas that live in one cell.

✅ Reused logic
If the same expression is used multiple times, LET helps avoid repetition.

Want to Go Further?

Once you're comfortable with LET, the next step is creating your own custom functions using LAMBDA, which works hand in hand with LET.

Check out the LAMBDA tutorial here

Take Your Excel Skills to the Next Level

If you enjoyed learning how to break down formulas with LET, you'll love the full Advanced Excel Formulas course. It's packed with practical examples and step-by-step lessons to sharpen your formula-writing skills.

Final Thoughts

LET helps you write:

• Cleaner formulas
• More readable logic
• Faster calculations

Try using it the next time you write a complex formula - you'll never go back!

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Want to sponsor our newsletters?  Just reply to this email to get in touch with us.