LET – Names in formulas for Excel

By Office Feature PM on June 1, 2020

Hello, Office Insiders, my name is Chris Gross and I am a Program Manager on the Excel team. I am excited to share with you a new function we have released, Names in formulas or =LET().

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 name portions of your formula for easier consumption? With the addition of the LET function, now you can!

Introducing LET – Names in formulas function

The LET function allows you to name, and then use a calculation or value in your formulas, and increase both readability (by giving context to others) and performance (by reducing the number of times an expression is calculated). It’s names but on a formula level.

Let’s take a closer look!

LET (name1, value1, [name2…], [value2…], calculation)

  • name1: The name for the 1st value
  • value1: The value to associate with the 1st name
  • name2 (optional): Additional names
  • value2 (optional): Additional values
  • calculation: The calculation to perform. This is always the final argument and it can refer to any of the defined names in the LET function.

Looking more closely at the parameters, there are a few things to note:

  • The names and their values must be in pairs.
    • For example:
      • =LET(total, SUM(A1:A10), total * 3).

In this case,  total and  SUM(A1:A10) are a pair.

If we wanted to add another name, we would just need to define an additional pair:

=LET(total, SUM(A1:A10), count, COUNT(A1:A10), total / count)

  • The last parameter of the function is the calculation in which you use the named values.
  • A properly structured LET function will have an odd number of arguments. In the examples above, the last parameters are total *3 and total/count respectively.

Example

Suppose you have some raw sales data that you’d like to filter to show a relevant subset to one person, in this case, based on the Sales Rep data. You’d also like to add a dash in any blank cells.

This formula can be authored using the formula below. However, notice that the same expression is used twice, in this case the FILTER expression, which will result in a performance hit.

Original Formula

=IF(ISBLANK(FILTER(A2:D8,A2:A8=”Fred”)),”-“, FILTER(A2:D8,A2:A8=”Fred”))

By using the LET function:

  • you can abstract the FILTER value and even update the criteria, or point to a cell reference.
  • this formula will be calculated twice as fast because Excel does not have to repeat the same calculation.

Please also note that name definitions can only make use of prior names and not subsequent names. For example, you can see that filterCriteria is able to be used by the filteredRange name definition because it comes afterwards.

Formula using LET

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),

IF(ISBLANK(filteredRange),”-“,filteredRange))

Learn more about the LET function here.

 Availability

The LET function is available to users running Insider Fast builds on Windows and Mac.

  • Windows: Version 2004 Build 12711.10000 or higher.
  • Mac: Version 16.36 Build 20032300 or higher.

Feedback

If you have any feedback or suggestions, you can submit them using the Help Improve Office button in the top right corner or click Help > Feedback. To stay connected to Excel and its community, send us ideas and suggestions via UserVoice.

Learn more about what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you! With the Office Insider newsletter, you can get the latest information about Insider features in your inbox once a month. Sign up here.

Posted in Excel, Windows Desktop

Comments are closed.