Get the Excel LET Function Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with the LET formulas from this tutorial.
- Practice writing easier-to-read formulas with named variables
- See examples with multiple variables, nested IFs, and dynamic arrays
- Includes the large-range performance example from the tutorial
Enter your email below for instant access.
Why Use the LET Function?
Microsoft has introduced a way to make complex Excel formulas easier to handle. Now, you can name parts of your formulas, including calculations and values.
This feature, known as the LET function, helps in two ways:
- It makes Excel run faster by not repeating the same calculations
- It makes it simpler to read and write tough formulas.
If you often use long formulas, this new function will save you time and make your work easier to manage.
❗ The LET function is only available in Excel 2021+ and Microsoft 365.
LET was the foundation for the even more powerful LAMBDA function, which lets you create your own reusable custom functions in Excel.
LET Function Syntax
The LET function has the following syntax:
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…] )- name1: is the first name to assign and must begin with a letter. This cannot be the output of a formula or refer to an existing named range.
- name_value1: is the value assigned to the name1
- calculation_or_name2: can be a calculation that uses all names within the LET function or a second name assigned to the name_value2 If this is the latter, then the calculation_or_name3 argument becomes required.
- name_value2: (optional) is the value assigned to the calculation_or_name2
- calculation_or_name3: (optional) can be a calculation that uses all names within the LET function or a second name assigned to the name_value2 If this is the latter, then the calculation_or_name4 argument becomes required.
❗The final part of your formula needs to be a calculation that gives a result. Also, the names you choose should follow the same rules as in the Name Manager. For example, they should start with a letter and not have any spaces.
How to Use LET (Simple Example)
If you are familiar with naming a range/cell using the Name Manager, this is the same idea.
What makes the Excel LET function different is that the defined name only exists within the use of the formula and nowhere else. Think of it as a narrowly defined named range that can only be used at that calculation moment.
If we had a value stored in cell A1 and we wanted to calculate a valued added tax, we could write a LET function like the following.
=LET(VAT, 20%, A1 * VAT)The second argument in our formula (20%) can be a static value, text, or a formula.
I know what you’re saying…
“That’s over-complicating things. Wouldn’t it be simpler to just write =A1 * 20%?”
In this case, it would be simpler. Where things become useful is later when we are writing things like multi-level nested IF statements. The formula will be easier to read and easier to update when changes are needed.
LET with Nested IF: Practical Example
Using the below data set, we want to calculate the percentage difference between the Actual Revenue and the Budget Revenue.
The catch is we only want to show results for those percentages that are outside a plus or minus 10% threshold. In other words, values that are within +/- 10% are considered “expected” and therefore will be ignored. We only want to focus on the more extreme variances.
Calculating the Difference for All Apps
Just to see what results we have for all Apps we will write the percentage difference calculation for the entire table.
In cell D2, write the following formula and fill it down for the remainder of the table.
=B2 / C2 – 1
NOTE: You could also write this formula as (Actual – Budget) / Budget.
=(B2 – C2) / C2The results are as follows.
If we manually identify which results we want to see versus the results we want to hide, the selections appear as follows.
Writing the Formula Without LET
We will rewrite the original formula in cell D2 as follows (note – we are using the ABS function {absolute function} to set all results to positive values. This will make the calculations simpler):
=IF(ABS(B2 / C2 – 1) > 10%, B2 / C2 – 1, “”)
When filled down to the remaining table rows, we see the following.
Why Repeated Calculations Are a Problem
The problem with our formula is that it needs to perform each row’s calculation twice, once for the test and once for the result (if needed).
If you need to adjust the formula, you will need to perform that adjustment twice. Being human beings, prone to mistakes, we may fail to update all formulas or update all formulas the same way.
The opportunity for errors creeping into our formulas rises with every iteration of the logic.
Replacing Repeated Calculations with LET
We will update our formula to capitalize on the LET function. Select cell D2 and add the Excel LET function in front of the IF function.
=LET(VAR, B2 / C2 – 1, IF(……We are defining a name called “VAR” (for “Variance”) and setting the name to the original calculation (B2 / C2 – 1).
We can now replace all instances of the calculation with the name “VAR”.
=LET(VAR, B2 / C2 – 1, IF(ABS(VAR) > 10%, VAR, “”) )
Need a refresher on IF logic? See our full guide on the IF function in Excel.
How to Use Multiple Variables in LET
To make things more interesting, we could define a variable for the percentage threshold. If we wanted to use the name “T” (for “Threshold”), we would update the formula as follows.
=LET(VAR, B2 / C2 – 1, T, 10%, IF(ABS(VAR) > T, VAR, “”) )To make things a bit easier to read, we can apply in-cell carriage returns in our formula by using the ALT-Enter key combination.
It’s up to you how many names you want to use in the LET function.
LET with Nested IF and Symbols (Advanced Example)
Below is an example of a Excel LET function (in cell E2 and filled down) working with a 3-level nested IF to display symbols instead of calculations.
Using the ALT+Enter trick, we can display the formula as follows to make it easier to understand.
By using the VAR name, we avoid typing the same potentially lengthy formula multiple times. This reduces the opportunity to induce errors into our logic as well as easing maintenance if the formula needs to be changed.
The results are thus.
Using Symbols as Results
The symbols for arrows up and down were acquired using the Insert – Symbol feature in Excel.
The red circle-dash symbol was acquired by pressing the Windows+Period key combination to launch the emoji library.
Coloring the Symbols
The application of color to the symbols is performed using Conditional Formatting.
If you are unfamiliar with the application of Conditional Formatting, consider following the link below for a tutorial of using Conditional Formatting with symbols.
How to use Own Symbols in Excel and Conditionally Format Them
Does LET Make Excel Faster? (Benchmark Results)
The Data
Using test data that numbered into the 600K row range, I created the same formulas as above to generate symbols. This would test the calculation speed of the LET function under the same circumstances.
One thing I removed was the Conditional Formatting. This was to ensure that the total calculation time was consumed entirely by the Excel LET function.
Another minor difference was to reference cells that contain the symbols rather than embedding the symbols within the formula.
The Formulas
I wrote three versions of the formula:
- The LET function using 1 variable named “VAR” (the percent difference calculation)
=LET(VAR, A4 / B4 – 1, IF(VAR > 10%, $B$1, IF(VAR < -20%, $A$1, IF(VAR < -10%, $C$1) ) ) )- The LET function using 2 variables: “VAR” (as before) and “T” for “Threshold”
=LET(VAR, A4 / B4 – 1, T, 10%, IF(VAR > T, $B$1, IF(VAR < -20%, $A$1, IF(VAR < -T, $C$1) ) ) )- The IF function as would be written if LET was not used
=IF(A4 / B4 – 1 > 10%, $B$1, IF(A4 / B4 – 1 < -20%, $A$1, IF(A4 / B4 – 1 < -10%, $C$1) ) )The Timer
Using the “Fast Excel” add-in by Charles Williams (link to the download site) the workbook was recalculated multiple times using each formula by itself to obtain an average speed for that formula. The times were measured in milliseconds (thousands of a second).
The results were as follows:
As we can see, there isn’t any real benefit to using the LET function in this scenario. This is likely because our formula(s) is quite simple. We are only dividing one cell against another. It seems that Excel’s natural calculation speed is as good without the LET function lending a hand.
The only benefit for the user is an easier to read and maintain formula.
Giving the LET Function a Challenge
We’ll use ranges of cells manipulated by some of the newer Dynamic Array functions.
To learn more about the wonderful world of life-changing Dynamic Array functions, click here and prepare to be awestruck.
Our data set is below ranging from row 4 to row 74:
Our goals are as follows:
- Obtain the average salary for all departments
- Create a unique list of departments
- Obtain the average salary for each department
- For each department, compare the department’s average salary against the average salary for all departments to obtain a department vs. all variance
- Display a message depending on the variance value: above the average = “Above”, less than average but not more than 10% = “In Range”, and below 10% = “Far Below”.
The process is as follows:
- Obtain the average salary for all departments by selecting cell H4 and entering the following formula.
=AVERAGE(B4:B74)- Derive a unique list of departments starting in cell H6 using the UNIQUE
=UNIQUE(C4:C74)We will build the final formula in stages so things don’t get confusing.
- To obtain an average salary for each department from Step 2, Select cell I6 and enter the following formula.
=AVERAGEIFS(B4:B74, C4:C74, H6#)NOTE: The reason for the # symbol after the H6 reference is to capitalize on the Spill Range (H6:H11) generated by the UNIQUE function.
Since we want to return the verbal responses, and we will be reusing this AVERAGEIFS formula several times, we will place it inside a LET function and give it a user-friendly, easy to understand name of “VAL”.
- Update the AVERAGEIFS formula to include a LET function as witnessed below.
=LET(VAL, AVERAGEIFS(B4:B74, C4:C74, H6#), …- The remainder of the formula will be the nested IFs that will test the result of VAL against various thresholds and respond with the appropriate text response.
=LET(VAL, AVERAGEIFS(B4:B74, C4:C74, H6#), IF(VAL > H4, “Above”, IF(VAL < H4 * (1 – 10%), “Far Below”, “In Range”) ) )
By using VAL as a short name for the longer formula, we save on overall formula length and complexity. And don’t forget, if we need to update the formula, we only need to perform a single update.
Want to learn more about UNIQUE, FILTER, and other dynamic array functions? See our guide on new Excel functions including VSTACK and TEXTSPLIT.
Running a New Set of Performance Tests
Let’s see if we can make the LET function sweat a bit.
We have extended the range of data in the above table to well over 700K rows. We ran two different formulas, one using the LET function, the other using only IFs. These were run multiple times to obtain an average run time.
The results are as follows:
Bottom line:
For simple cell-to-cell formulas, LET won’t noticeably speed things up. But for formulas that process large ranges (especially with dynamic array functions like UNIQUE, FILTER, or AVERAGEIFS), LET can cut calculation time significantly.
Regardless of performance, LET always makes formulas easier to read, write, and update.
Download the Free Practice File
Even when you aren’t using crazy-large data ranges, you can still benefit from the Excel LET function in the following ways:
- Easier to read and understand formulas
- Easier to write formulas
- Easier to update formulas
- Reduced user-errors when creating or updating formulas
Download the free Workbook HERE.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
