Pinterest Pixel

How to use Excel LET function

John Michaloudis
If you write long complex formulas in Excel, then the new LET function is a perfect match for you. It lets you define variables inside the formula and get calculation results.
Excel LET function is useful when you use the same expression multiple times in a formula.
How to use Excel LET function | MyExcelOnline How to use Excel LET function | MyExcelOnline

This function is available exclusively for Excel 365 subscribers.

Sign up to Office 365 to get access to all the cool updates and new functions that allow you to be more efficient in Excel.

In this article, you will be provided a detailed guide on the following:

Watch how to use the LET function in Excel on YouTube and give it a thumbs-up!

Download this Introduction to the Microsoft Excel LET Function Tutorial workbook so you can practice along with us:

download excel workbookLetFunction-1.xlsx

How to use Excel LET function | MyExcelOnline

 

Introduction to Excel LET function

You might have used Named Range in Excel while creating lengthy formulas and know how they make the formula easier to read. LET function is like an advanced version of named ranges.

It lets you assign names directly to the calculation. In this way, Excel calculates the value of the variables just once and uses it several times within the same formula.

The three benefits of using LET functions are:

  • Improves formula readability – Assigning a user-friendly name to a range/variable and using it repeatedly in the calculation will make the formula cleaner, easier to read and understand.
  • Improves performance – Excel LET function eliminates duplicate calculation. The expression is evaluated at the beginning of the formula and then reused multiple times without further calculation. This increases the speed and performance of the worksheet.
  • Easier to update – If you need to change the value of the defined variable, you can easily change it at the beginning instead of updating the same expressions multiple times.

 

Syntax & Arguments

The formula breakdown of the Excel LET function is:

=LET (name1, value1, [name2], [value2], calculation)

where:

  • name1 – the name of the first variable defined (it should be without spaces, period, or special characters);
  • value1– the value that should be assigned to the first name (it can be text, number, cell reference, or formula);
  • name2 – the name of the second variable (optional);
  • value2the value assigned to second variable (optional);
  • calculation – the expression or formula that uses these defined variables.

For example:

=LET(x, 5, y, 9, x+y/x)

Here, Excel will assign 5 to variable x, 9 to variable y, and then perform the calculation – 5+9/5. It will return the output as 6.8.

How to use Excel LET function

You can define numerous pairs of names and their values and insert a calculation that uses all of them. Excel can handle a total of 126 name-value pairs in this function.

Before you start using this incredible function, there are few things that you should note:

  • LET function is not available in any of the older versions of Excel (2010, 2013, 2016, 2019). Only in Excel for Microsoft 365.
  • The scope of defined names is limited to that formula only. You can reuse these assigned names multiple times within the formula but not anywhere else.
  • There should be several name-value pairs but the last argument should be “calculation”.
  • The name argument should start with a letter, an underscore (_), or a backslash (\). It cannot start with an “r” or “c” as they are reserved for the R1C1 style.
  • The name argument should follow the same convention as the named range i.e. it should be without spaces, period, or special characters.

 

LET with one name-value pair

Let’s start with an easy example of one name-value pair to simply understand the working of the Excel LET function.

In this example, we have date, expected output, actual output in columns B, C, and D respectively. Since there is a discrepancy between the actual and expected output, we have calculated the difference in percentage between the two in column E.

This will give an estimate regarding how much was actually produced over how much was expected to produce.

How to use Excel LET function
Based on the difference % mentioned in column E, we would like to give feedback as below:
  • If the difference is less than 0.8, we want to return the word “bad”. It was not a productive day as the units produced was below the expected units.
  • If the difference is greater than 1, we want to return the word “good”. It was a productive day as we produced more than what we had expected.

Now, we can use either the Nested IF function or the LET function to achieve this result.

 

Nested IF Function

IF function is used to test a condition and return a value if the condition is met, and a value if it is not. The syntax of the IF function is:

=IF(Logical Test, Value if True, Value if False)

In the example, you will be using the Nested IF function to test both conditions. So, let’s begin!

STEP 1: We need to enter the IF function in a blank cell:

=IF(

How to use Excel LET function

STEP 2: Enter the first argument – Logical_test

What is your condition? The difference % should be greater than 1.

=IF(E5>1,

How to use Excel LET function

STEP 3: Enter the second argument – Value_if_true

What value should be displayed if the condition is true? We want “good” to be displayed.

=IF(E5>1, “good”,

How to use Excel LET function

STEP 4: Enter the third argument – Value_if_false

What value should be displayed if the condition is false? If the condition is not met, we want to insert another IF function.

=IF(E5>1, “good”, IF(E5<0.8,

How to use Excel LET function

STEP 5: Enter the value if the condition of the second IF function is met.

=IF(E5>1, “good”, IF(E5<0.8, “bad”

How to use Excel LET function

 

STEP 6: Lastly, enter a blank when both conditions are not met.

=IF(E5>1, “good”, IF(E5<0.8,”bad”,“”)

How to use Excel LET function

STEP 7: Copy the formula below in the column.

How to use Excel LET function

 

LET Function

In this example, we will assign the difference % to a variable “pchg” using the LET function and then calculate the result.

STEP 1: We need to enter the LET function in a blank cell:

=LET(

How to use Excel LET function

STEP 2: Enter the first argument i.e. name

=LET(pchg,

How to use Excel LET function….

STEP 3: Enter the second argument i.e. value to the name assigned. Here, the value is inserted in cell D5.

=LET(pchg, E5,

How to use Excel LET function

STEP 4: Now enter the entire nested IF function and replace the cell reference D5 to the variable pchg.

=LET(pchg, D5, IF(pchg>1,”good”,IF(pchg<0.8,”bad”,””))

How to use Excel LET function

As you can see, the LET function does exactly what the IF function was doing when we are using just one variable.

Let’s look at another example where using the LET function will be more useful than the nested IF .function.

 

Example 2:

In this example, we have sales from two branches, and we need to calculate commission based on it:

  • If the total sales are greater than $7,000, the commission will be 10%;
  • If the total sales are greater than $5,000, the commission will be 8%;
  • If the total sales are less than or equal to $5,000, the commission will be 0.

In the worksheet below, we have two columns E and F that will contain the commission amount calculated using LET and Nested If respectively.

How to use Excel LET function

Using the Nested IF function, we can use the following formula to total commission:

=IF(SUM(C5:D5)>7000, SUM(C5:D5)*10%,IF(SUM(C5:D5)>5000, SUM(C5:D5)*8%,0))

How to use Excel LET function

Follow the steps below to get the work done using the LET function:

STEP 1: Enter the LET function in a blank cell:

=LET(

How to use Excel LET function

STEP 2: Enter the name for the variable to calculate the total sales i.e. total

=LET(total,

How to use Excel LET function

STEP 3: Enter the formula for the defined variable i.e. SUM(C5:D5)

=LET(total, SUM(C5:D5),

How to use Excel LET function

STEP 4: Enter the IF formula using the variable “total”.

=LET(total,SUM(C5:D5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

How to use Excel LET function

If we are using the Nested IF function only, Excel has to calculate the SUM function four times:

=IF(SUM(C5:D5)>7000, SUM(C5:D5)*10%,IF(SUM(C5:D5)>5000, SUM(C5:D5)*8%,0))

Whereas, when we are using the LET function, SUM is calculated just once at the beginning and the calculated value is used in the formula later. This makes the calculation faster and the formula cleaner.

=LET(total,SUM(C5:D5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

 

Using variables in Excel formulas makes updating the formula quick and easy.  Let’s say we have added another branch in our calculation i.e. West Branch and you need to update the formula.

To do so, simply edit the value of the defined variable from SUM(C5:D5) to SUM(C5:E5) and the total commission will be updated automatically.

=LET(total,SUM(C5:E5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

 

Until now, we have just used one name-value pair, let’s try using more and see how that works!

 

LET with five name-value pairs

In the previous feedback example instead of using one name-value pair, we will now be using 5.

You can see that there are five variables that we can use in this formula:

  • Difference % – pchg
  • Percentage you want to check if the difference is good – pergood
  • Percentage you want to check if the difference is bad- perbad
  • The text you want if the difference is good – textgood
  • The text you want if the difference is bad- textbad

 

STEP 1: We need to enter the Excel LET function in a blank cell:

=LET(

How to use Excel LET function

STEP 2: Enter the first argument i.e. name of the 1st variable

=LET(pchg,

How to use Excel LET function….

STEP 3: Enter the second argument i.e. value to the name assigned. Here, the value is inserted in cell D5.

=LET(pchg, E5,

How to use Excel LET function

 

STEP 4: Enter the name for the 2nd variable i.e. pergood

=LET(pchg, E5, pergood,

How to use Excel LET function

STEP 5: Enter the value for the 2nd variable i.e. 1.

=LET(pchg, E5, pergood, 1,

How to use Excel LET function

STEP 6: Enter the name for the 3rd variable i.e. textgood

=LET(pchg, E5, pergood, 1, textgood,

How to use Excel LET function

STEP 7: Enter the value for the 3rd variable i.e. “good”Since it is a text make sure to put it within quotes.

=LET(pchg, E5, pergood, 1, textgood, “good”,

How to use Excel LET function

STEP 8: Enter the name for the 4th variable i.e. perbad

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad,

How to use Excel LET function

STEP 9: Enter the value for the 4th variable i.e. 0.8

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8,

How to use Excel LET function

STEP 10: Enter the name for the 5th variable i.e. textbad

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad,

How to use Excel LET function

STEP 11: Enter the value for the 5th variable i.e. “bad”

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad, “bad”,

How to use Excel LET function

STEP 12: Lastly, enter the nested IF function and replace the cell reference, text, and numbers with the defined variables.

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad, “bad”, IF(pchg>pergood, textgood, IF(pchg<perbad, textbad, “”)))

How to use Excel LET function

Copy paste this formula to the remaining cells below.

How to use Excel LET function

Using the Excel LET function makes your formula easier to read and update if required in the future.

 

LET with Emojis

Instead of showing the text “good” or “bad, we can even use emojis. In windows, you can press keys – Windows + . to open the dialog box for emojis and simply click on the one you want to use.

How to use Excel LET function

In step 7, replace the text “good” with smile emoji:

=LET(pchg, E5, pergood, 1, textgood,”😊”

How to use Excel LET function

In step 7, replace the text “bad” with a thumb down emoji:

=LET(pchg, E5, pergood, 1, textgood, “😊”, perbad, 0.8, textbad, “👎”

How to use Excel LET function

This is what the result will look like. Looks interesting, isn’t it?

=LET(pchg, E5, pergood, 1, textgood, “😊”, perbad, 0.8, textbad, “👎”,IF(pchg>pergood,textgood,IF(pchg<perbad,textbad,””)))

How to use Excel LET function

 

LET using Name manager

You can store the long LET formula using name manager and then easily reuse it whenever you want in the future without the need to type the formula again.

Before we do that, if you look at the formula you will see that column E is a relative reference i.e. if you use this formula the reference will also change. So, if you paste this column in column H, the reference will change from E5 to F5.

But we don’t want that. So, let’s lock column E by putting a dollar sign in front of it.

How to use Excel LET function

Now that your formula is ready, follow the steps below to learn how to store it in name manager:

STEP 1: Copy the entire LET formula by pressing the keys Ctrl + C.

How to use Excel LET function

STEP 2: Go to Formula Tab > Name Manager.

How to use Excel LET function

STEP 3: In the dialog box, click on New.

How to use Excel LET function

STEP 4: Type the following in the dialog box:

  • Name – DisplayEmoji
  • Scope – Select Workbook
  • Refers to – Paste the formula using Ctrl +V
  • Click OK

How to use Excel LET function

STEP 5: In cell H5, type the function

=DisplayEmoji

How to use Excel LET function

STEP 6: Copy-paste the formula below and you will see it is displaying the same result as before.

How to use Excel LET function

You can use this formula =DisplayEmoji anywhere in the spreadsheet and it will display the same result!

 

Conclusion

The Excel LET function is a super useful function that helps you eliminate redundancy in a formula and make the formula easy to read and compose. It improves the performance of your spreadsheet since the variables in the Excel formulas are calculated at the beginning and reused later in the formula.

This function is currently available for Office 365 users only. So if you want to use this function make sure to install Office 365 in your system!

 

 

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  ISNUMBER Formula in Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...