All About Excel’s New LET Function
Excel has released a new function in 2020 that will allow you to define variables within the scope of the formula. This functionality has the potential to bring clarity to what is going on within a formula and in some cases significantly reduce the length of complex formulas stored in a cell that have repetitive components.
Function Availability: M365 Subscription – at time of writing
Let Function Inputs
The Let function allows you to define up to 126 variables within a single formula. At a minimum, you are required to define one variable (Name 1, Value 1) and provide a calculation to be performed.
- Name 1
- Value 1
- Name 2 – 126 (optional)
- Value 2 – 126 (optional)
- Calculation
Formatting Best Practices
Since LET is largely focused on allowing users to better document complex formulas, there are a bit of “best practices” in terms of formatting how your LET formula is written to ensure your spreadsheet users can easily and quickly understand what is written.
1. Use Descriptive Variable Names
I know spreadsheet users love to make formulas as compact as possible, but if you are trying to utilize the LET function to provide documentation, it’s much better to use descriptive names for your variables. If a user is looking at a variable named “x” and “tax_rate”, which one do you think is going to instantly resonate?
Which of the following formulas gives you a better sense of what is being calculated?
=LET(x, 45, y, 1050, z, 75, x+y+z)vs.
=LET(Paint, 45, Furniture, 1050, Wall_Art, 75, SUM(Paint, Furniture, Wall_Art))2. Utilize Multiple Lines
3. Indent Subsequent Lines
In coding, it is common practice to indent any additional lines of your code if a line needs to be wrapped into multiple lines. This practice allows the reader to easily determine if the additional lines are part of the previous line of code or the start of a completely new command in the code.
It is recommended to use 4 spaces as an indentation.
Real-World Examples
Example 1: Notate Expenses
Example 2: Shorten Repetitive values
Another great benefit is the ability of the LET function to minimize the need of achieving the same value over and over again. Let’s look at the following example where we have a VLOOKUP function combined with an IF function to provide different regional labels (AMER, APAC, EMEA) based on any employee’s country.
Logic Summary:
IF Country = Japan OR China, THEN output APAC
IF Country = France OR Spain, THEN output EMEA
ELSE output AMER
Normally, we would write something like the following formula where a VLOOKUP function is getting repeatedly tested to provide the desired output:
= IF(OR(VLOOKUP(E2, $A$1:$B$11,2,0)="Japan", VLOOKUP(E2, $A$1:$B$11,2,0)="China"), "APAC", IF(OR(VLOOKUP(E2, $A$1:$B$11,2,0)="Spain", VLOOKUP(E2, $A$1:$B$11,2,0)="France"),"EMEA","AMER"))But with LET, you have the ability to only write your VLOOKUP function once and set it to a much shorter variable name. In the below code, I set the name “CTRY” to be equal to whatever my VLOOKUP function outputs. This takes the length of my original formula from 178 characters to 128. A reduction of 28% in character length!
=LET(CTRY,VLOOKUP(E2, $A$1:$B$11,2,0), IF(OR(CTRY="Japan",CTRY="China"),"APAC", IF(OR(CTRY="Spain",CTRY="France"), "EMEA", "AMER")))This LET formula is also much more comprehensible and will be easier to maintain if the company adds any additional countries to its employee base.
I Hope This Helped!
Hopefully, I was able to explain how you can use Excel’s new LET function to reduce your formula length and/or provide clarity to complex equations. If you have any questions about this new function or suggestions on how to improve the article, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.