In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in a long time.

The LET function can store intermediate calculations and values by defining names inside a formula—names that apply only within the scope of each LET function. The function calculates a formula that uses some or all of the names, and then the function returns your calculated value.

(You can download the following examples here.)

The LET function uses this syntax…

=LET(Name1, Value1,   [Name2], [Value2],  …, …,  Result)

…where you can have as many Name-Value pairs as you want, and where…

1. Name can be nearly any Name without periods, spaces, or special characters.
2. Value can be a name, text, cell reference, formula, array formula, array constant, or any other value that you can use in a formula.
3. Result is typically a calculated value.

Why Use LET?

LET offers two significant benefits.

First, it can reduce your calculation times by allowing you to capture the result of one or more calculations and then use those results as often as necessary within your formula.

So, for example, we could replace a formula like this…

=IF( some-long-calculation = 0, NA(), some-long-calculation)

…with a formula like this:

=LET(Calc, some-long-calculation, IF(Calc = 0, NA(), Calc) )

Second, LET allows you to document and clarify your formulas.

For example, this formula calculates the approximate area of a circle with a radius of 3:

=3.14*3^2

If you saw this formula in a worksheet, its purpose might not have been obvious. But this formula does the same thing while labeling each section of the formula:

=LET(Pi,3.14,Radius,3,Pi*Radius^2)

It’s likely, however, that you when you look at this formula you see a bunch of names and values, but you aren’t sure how they’re being used in the formula. The formula looks…well…cluttered.

We can improve the readability of this LET formula in two ways.

Improving a LET Formula’s Readability

During most of Excel’s existence, we couldn’t add spaces between arguments in formulas. But now we can. And therefore, one way we can improve readability of any formula—including LET formulas—is to group LET’s name/value pairs by inserting double spaces between them, like this:

=LET( Pi, 3.14,  Radius, 3,  Pi * Radius^2 )

Alternatively, we could wrap the formula in our formula bar by using Alt + Enter to force a line break between each name-value pair, like this:

=LET(
Pi, 3.14,
Radius, 3,
Pi * Radius^2 )

Here, each row of the formula before the last row begins with a name and is followed by its definition, which makes the formula much easier to read.

If you’ve not used formulas that wrap to multiple rows, you need to know that you can increase the height of your formula bar by clicking on its down arrow like this…

…or by clicking and dragging its bottom border like this:

Taking this one step further, by wrapping the pairs of values, we now can add formula notes to any of the lines easily, like this:

=LET(
Pi, 3.14,      Note1, “Remember to replace 3.14 with PI()”,
Radius, 3,   Note2, “Replace 3 with a cell reference or range name”,
Pi * Radius^2 )

To be clear, Note1 and Note2 are names just like Pi and Radius—names that I defined but didn’t wrap to another line. So those notes must be enclosed in quotes and followed by a comma—just like the definition of any other name in a LET formula.

Finally, with regard to formula notes, to remove them from one or more formulas, first select at least two cells—otherwise, Excel will apply the next step to your entire worksheet.

Then, in the Home, Editing group, choose Find & Select, Replace. In the Replace tab of the Find & Replace dialog, type note*,*, (that is, type note asterisk comma asterisk comma) into the Find what edit box and leave the Replace with edit box empty.

When you press Enter, Excel will remove your notes from the selected range.

Best Practice for LET’s Result Argument

The previous formulas don’t represent best practice for displaying the result. Instead, I really should have written the previous formulas like this:

=LET( Pi, 3.14,   Radius, 3,   Area, Pi * Radius^2,  Area )

Or this:

=LET(
Pi, 3.14,
Radius, 3,
Area, Pi * Radius^2,
Area )

That is, I should have ended the formulas with the name of a defined calculation—which was Area in this case.

It’s best practice to use the named result—not its calculation—for three reasons.

First, the name can tell you the intended meaning of the value that the formula is returning. In this case, of course, it’s returning the area of a circle.

Second, when you use a name as the last argument, you can debug your formula more easily.

For example, suppose I improve my formula by using the PI function and by using a value in cell B3 for the radius. If I don’t get the result I expect for the Area, I could replace Area with Pi in the final argument, and then with Radius to check whether those names return correct values.

Here, for example, I’m checking the value for Radius in the formula:

=LET(
Pi, PI(),
Radius, $D$10,
Area, Pi * Radius^2,
Radius)

(Now that this version uses the PI function, there’s no reason to define the name Pi. However, I kept the name for consistency with the previous examples.)

Third, by ending with a name we can build our formulas a row at a time without displaying errors.

To illustrate, in my second post about this topic I’ll tell you about a long array formula that took some experimentation to build. I created the formula by starting with a LET-formula “stub” like this:

=LET(
DispArray,99,
DispArray)

And then, as I added more names after “LET(“ the formula continued to return 99 until I was ready to test some of the values and calculations I had assigned to names. To test any of those results, I merely replaced the last DispArray name with an earlier name that I wanted to test.

Finally, of course, I replaced the DispArray value of 99 with the calculation of the array values I wanted to display.

A Few LET Function Tips

• You don’t need to use all the names in a LET function. But if your completed formula doesn’t use all the names, it’s wasting time by performing needless calculations.
• Each named argument in a LET function can use names defined earlier in the function, but not later in the function. And IntelliSense displays those earlier names—as shown for the YearTotal name in this partially completed formula:
  
• Although you can assign names in the LET function that also are used as traditional range names in your workbook, it’s not a good idea to do so—for two reasons. First, you won’t be able to use those names with their original meaning in your LET formula. Second, you add needless confusion to your LET formula.
• If you’re a programmer, it might seem reasonable to set up a LET formula like this:

=LET(
Test1,3,
Test1, Test1 + 1,
Test1)

But LET won’t allow you to define the same name more than one time in a formula.

• Like normal range names, LET-function names can’t contain spaces or special characters. And even though normal range names can contain periods, the current version of the LET function doesn’t allow its names to do so.
• LET functions can use other LET functions as arguments. For example, this formula returns 99:

=LET(
Test, LET(Value,99, Value),
Test)

• It’s okay to use the names of Excel functions as LET-function names, as shown here:

=LET(
DateText,”20201122″,
Year, LEFT(DateText,4),
Month, MID(DateText,5,2),
Day, RIGHT(DateText,2),
Date, DATE(Year, Month, Day),
Date)

Here, each name used in the Date formula is also the name of an Excel function, but the LET function doesn’t care.

Also, by the way, notice that the DATE function doesn’t care that its values are text rather than numbers. That is, Excel “coerces” the text into the values it needs for the DATE calculation.

Data Types for LET Arguments

LET arguments can use any type of data and operators that any other Excel function can use. Specifically…

• The arguments can be literal values, text, array constants, and so on.
• They can reference ranges, range names, Tables, arrays, etc.
• They can use the reference, union, and intersection operators that would work in normal formulas.

In the second part of this topic, we’ll cover arrays in greater detail by using dynamic arrays with the LET function.

You can download the short examples here.