The Excel LET function can help you make complex formulas easier to read and use. With the LET function, you can assign a unique name or identifier to the values in a formula. This name is like a “container” for any value that you can store temporarily and reuse in your calculations as many times as you want.
LET allows you to associate a calculation or value in your formula with a name. It's names except on a formula level. The main benefits are: 1.
- Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Take your analytics to the next level with Excel.
- The Excel LET function can help you make complex formulas easier to read and use. With the LET function, you can assign a unique name or identifier to the values in a formula. This name is like a “container” for any value that you can store temporarily and reuse in your calculations as many times as you want.
You assign names once but reuse them multiple times. The value assigned to a name can be constant values or dynamic values that result from a calculation. Excel allows 126 name/value pairs with the LET function.
The LET function works just like variables in any programming language. Even Excel beginners can use this simple function to manage spreadsheet data in several ways:
- Makes complex formulas simpler to manage.
- Makes calculations easier to read and understand.
- Makes it remember complex formulas with descriptive names.
- Makes spreadsheets perform better as Excel calculates a formula once and assigns it to the given name.
How to get the LET function in Excel
The LET function is available in Excel for Microsoft 365 for Windows and Mac, and Excel for the web. The function was gradually rolled out from July 2020. Do note that the LET function may not be available to you yet if you are not a Microsoft 365 subscriber.
Syntax of the LET function
Microsoft Excel defines it as a Text function with this syntax:
- Name1: The name for the 1st value
- Value1: The value assigned to the 1st name
- Name2 (optional): Additional names
- Value2 (optional): Additional values
- Calculation: The calculation the function performs with the names and values. This is always the final parameter, and it can use any combination of the names defined in the LET function.
For instance, when you use one name-value pair with the LET function:
Here, we set up NAME as the variable which holds the value of 100. The next part of the formula uses this value and adds “1” with the SUM function. The formula returns “101” as the result.
Here:
When you want to use more than one name value pair with the LET function:
Here:
Example of a complex LET function
Download your free practice file
Use this free Excel file to practice along with the tutorial.
The LET function is more useful when you have a more complex formula with multiple nested functions.
In this example, we add the number of cars sold for each make. If the number of cars sold is greater than five, then the sum is multiplied by a factor of ten. If not, it is multiplied by five.
The first results column uses a simple IF statement:
You can see that the same function (SUM) is repeated three times in the calculation. This can be avoided, and the statement can be leaner with a LET function.
Here:
Both statements lead to the same result but LET helped to simplify the IF calculation.
Summary
The Excel LET function is a great way to simplify your formulas and make them easier to read and understand. This is especially useful to make complex formulas and calculations easier to manage and remember. Another bonus is that your spreadsheets will run better as Excel calculates a formula once and assigns it to the given name.
The LET function is currently available for Microsoft 365 subscribers and in Excel Online, so you may not see it if you are using a previous version of Excel.
Learn more Excel skills
To learn more Excel essentials, including more new functions like XLOOKUP, the GoSkills Basic and Advanced Excel course can help. Start learning today with a seven day free trial to all courses on GoSkills. Or you can start with the free Excel in an Hour course to cover some basics in Excel.
Learn Excel for free
Start learning formulas, functions, and time-saving hacks today with this free course!
Start free courseBY STANLEY ZAROWIN
I prepare very complex spreadsheets. Before data can be added or amended in some cells, a user must be briefed with special instructions, which are different for each cell. Is there some convenient way to attach short, customized directions for each cell so any staff person can input data in my files?
You can do it several ways, but I think Excel’s Data Validation tool does it best. You can program it to display a popup memo with your instructions and then disappear after data are entered. The screenshot below illustrates what a Data Validation pop–up memo looks like when cell A1 is highlighted.
Let Excel Graph Show Only Selected Dates
To set up the program, click on Data , Validation . Be sure the Input Message tab is displayed and there is a check at Show input message when cell is selected . In the Title box enter a name for the message or the name of the file owner. Then add the full instructions in the Input message box and click on OK .
Stanley Zarowin is a contributing editor to the JofA . His e-mail address is stanley.joatech@gmail.com.
Let Excel