Man working on laptop with child on his lap

LET - Names in formulas for Excel

Feature deep dive
  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

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.

LET names in formula

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 builds on Windows and Mac.

  • Windows: Beta Channel Version 2004 Build 12711.10000 or higher and Current Channel (Preview) Version 2007 Build 13001.20266 or higher.
  • Mac. Fast Version 16.36 Build 20032300 or higher.

We typically release features over some time to ensure that things are working smoothly. This is true for Insiders as well. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as Insiders, have had the opportunity to try them.

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 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! 

Sign up for the Office Insider newsletter and get the latest information about Insider features in your inbox once a month!