Microsoft has announced the addition of the LET function to Excel. This way, Excel users can reuse portions of their formula in the spreadsheet. By using the LET function, Microsoft Excel users can associate a calculation or value in their formula with a name. To put that into perspective, Microsoft states that LET essentially enables names except on a formula level.
Microsoft Excel gets new LET function
Describing the LET function, Microsoft had this to say in its blog post:
“Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!”
As Microsoft describes, two of the primary benefits of the LET function are Readability and Performance.
The LET function allows users to give meaningful context to readers their formula. This way, users no longer have to remember what a specific range or cell reference referred to. Plus, users no longer have to remember their calculation or duplication of a similar expression within a formula.
Whenever users repeat the same expression multiple times within a single formula, Excel calculates the same expression more than once. The LET function addresses this.
You can name the expression and refer to it using that name using the LET function. It combines similar name expressions and performs calculation only once.
“This can significantly improve performance for computationally complex expressions.”
The newly added LET function in Excel works as follows:
LET(name1, value1, [name2…], [value2…], calculation)
Whereas,
- name1 is the name for the 1st value
- value1 is the value to associate with the 1st name
- name2 is the optional/additional name
- value2 is the option/additional value
- calculation denotes the final calculation to perform
See the following example:
Traditional formula
=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-", FILTER(A2:D8,A2:A8="Fred"))
Formula using LET
=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-",filteredRange))
If you want to know more about the LET function in Excel, check out this official blog post.
