Excel LET Function

Mynda Treacy

August 5, 2025

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.

Watch the LET Function Video

Subscribe YouTube

Get the LET Function Example Workbook

Enter your email address below to download the free file.



By submitting your email address you agree that we can email you our Excel newsletter.

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:

Excel interface
=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:

why to use LET function in Excel?
=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:

how to use LET function in Excel?

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 like you can with regular formulas. In the image below you can see the margin calc returns the #NAME! error in the formula bar:

LET NAME errors

But there's a workaround. Let's say you want to evaluate the margin calculation in the formula below. We can assign the final calculation to a name, I'll call it calc and then in the final argument, place the name you want to inspect, in this case, the margin:

=LET(

cost, E2,

revenue, D2,

margin, (revenue - cost) / revenue,

label,

    IF(margin > 0.4, "High",

    IF(margin > 0.25, "Medium", "Low")),

calc, label & " (" & TEXT(margin, "0.0%") & ")"

margin

)

Then when you're finished inspecting the result of the margin calc, you can replace the last argument with 'calc' or 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!

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

Stop Excel Conditional Formatting Rules Duplicating and Fragmenting

Build a Searchable CRM in Excel with Power Query Custom Data Types

27 thoughts on “Excel LET Function”

  1. Along with the things mentioned, I like to write the LET to have inputs listed first, then Alt-Enter for new rows building whatever layers of calculation I need. (I prefer to have it broken up like this for readability.) But with the inputs listed first and all in one logical row (’cause it may actually span more than one), one can easily modify the inputs. I do it because one thing that always vexed me about complicated formulas was changing them required one to go through laboriously to find every instance of an input and change it (if the inputs changed, which they do more often than the logic changing).

    A second thing that helps sometimes is when I want to use a variable name that already exists as a Named Range. Using it in the LET will override the Named Ranges using it. Good for for when someone has poorly defined those so that they have workbook scope rather than worksheet and you need something different than that “standard” for your formula.

    Nice idea with the calc/margin example for evaluating a particular variable. I picture myself using that soon!

    Also interesting is that one can have LET functions inside the main LET function and variable names used in the internal ones work in them, but do not affect things if the formula’s calculation runs by them instead of through them in any given instance. For example:

    =LET(horse, A2, cow, LET(horse, A1, horse), pig, A3, pig)

    in which the internal LET only matters if the calculation requires that portion to be used. In the example, it does not. If it were “cow” instead of “pig” it would and that step would produce a different result for its internal variable of “horse” than the main LET would if the calculation were “horse” instead of “cow”…

    I’ve also seen folks recommend adding comments in your formula by listing an element that you’d like to comment on and immediately following it with a dummy name and as the dummy name’s calculation, just have the text of the comment (double-quoted of course). It would never be part of any work done, but would be there to read if one desired.

    Reply

    • Thanks for sharing, Roy. Some great tips here, particularly the commenting.

      I’m not following a use case for your horse, cow, pig example though.

      Reply

  3. One of your examples uses form control nicely.
    However, if you want to use FILTER within the LET function (and w/CHOOSE and with SUBTOTAL), i get a #VALUE! error. Could you suggest why this happens or how to get around it?

    =LET(val,FILTER(E6:E15,C17=C6:C15),w,CHOOSE(E2,9,1,4),SUBTOTAL(w,val))

    Reply

  5. A suggestion to simplify the formula in the Relative References section further would be to use SUMIFS instead of FILTER then SUM

    e.g. BrandTotal = SUMIFS(Table1[Sales], Table1[Date], “>=” & MonthStart, Table1[Date], “<=" & MonthEnd, Table1[Brand], Brand)

    Reply

  6. Not a question, but a comment.

    Thank you for your LET function tutorial and the sample workbook. I managed to expand on the function example to be able to select a Brand and find total sales for each month for each for each category. Took a bit of work, but I am happy with myself (LOL).

    Reply

    • Hi Teresa,

      Not sure you need to do this with LET?

      If you want to avoid divide by zero errors use the IFERROR function

      In your cell in Column C you’d have 

      =IFERROR(A1/B1,"")

      Regards

      Phil

      Reply

  8. I cannot find a definite answer about the general availability of the LET function. Do you know when it was (or is it?) released for general use, not when it was released for those in the Microsoft Insiders program? I want to use it in applications that other people use, but I don’t want them to see #NAME? when they use my spreadsheet. Thanks!

    Reply

    • Hi Steve, LET has been GA for a couple of months now. The only reason you’d see errors is if users are on a semi-annual update channel instead of monthly.

      Reply

  9. I loved your video on the LET function! Is there a way to download the raw data workbook so that I can follow along and create the formulas as you do? The workbook I downloaded has that all done, and I can see the formulas, but I learn by doing as I see. Thanks!

    Reply

    • Hi Peg, great to hear you enjoyed my LET function video 🙂 I don’t have a blank workbook, but you can just use an empty cell to recreate the formulas yourself, or delete mine and start again.

      Mynda

      Reply

  10. Simple Question. Why is LET put in the Text function category? I would have thought Lookup & Reference was more appropriate.

    Reply

    • Hi Norman,

      I don’t know the logic for putting LET in the text function category. Maybe it didn’t really fit in any category, so it was least out of place there. I don’t think I’d call it a lookup and reference function either. It needs a new category 🙂

      Mynda

      Reply

  12. Great lesson on the LET function, thank you
    When it is released to the generic Office 365 version, will it work on prior versions of Excel or would you have to have Office 365?

    Reply

    • Thanks, Kevan! LET won’t ever go back and work in earlier versions of Excel. Only Office 365 and the next perpetual licence e.g. Excel 2022!

      Reply

    • Hi Bernard, the # is a spilled range operator. It is a shortcut to referencing the whole spilled range, so instead of entering D5:D7 we can use D5# and if the spilled range changes size it will automatically find the correct range. More on dynamic array functions here.

      Reply

  14. This is about how many times over the years that I’ve thought about wanting to define variables within a formula:

    =LET(Awesome,1,Awesome*150)

    This function is truly brilliant. Kudos to the Excel team, and thank you for the great explanation.

    I am SO excited to start using this!

    Reply

Leave a Comment

Current ye@r *