×

All About Excel's New LET Function

By Chris Newman •  Updated: 09/20/20 •  4 min read
All about Microsoft Excel 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
Excel LET function breakdown

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

Excel LET function with 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.

Excel LET function with indented lines

Real-World Examples

Example 1: Notate Expenses

Excel LET function example

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.

Keep Learning

Chris Newman

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.