LET function

The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.

To use the LET function, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.

LET diagram

Note: This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers.

Benefits

  • Improved Performance If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once.

  • Easy Reading and Composition No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

Argument

Description

name1

Required

The first name to assign. Must start with a letter. Cannot be the output of a formula or conflict with range syntax.

name_value1

Required

The value that is assigned to name1.

calculation_or_name2

Required

One of the following:

  • A calculation that uses all names within the LET function. This must be the last argument in the LET function.

  • A second name to assign to a second name_value. If a name is specified, name_value2 and calculation_or_name3 become required.

name_value2

Optional

The value that is assigned to calculation_or_name2.

calculation_or_name3

Optional

One of the following:

  • A calculation that uses all names within the LET function. The last argument in the LET function must be a calculation.

  • A third name to assign to a third name_value. If a name is specified, name_value3 and calculation_or_name4 become required.

Notes: 

  • The last argument must be a calculation which returns a result.

  • Names of variables align with valid names that can be used in the name manager. E.g. "a" is valid but "c" is not because it conflicts with R1C1 style references.

Examples

Example 1

Consider the simple expression "SUM(x, 1)" where x is a named variable that can be assigned a value (in this case, x is assigned the value 5).

=LET(x, 5, SUM(x, 1))

When this formula is input into a cell, it will return the value 6. 

Example 2

Suppose you have some raw sales data, and you'd like to filter that data to show one person, and add a dash to any blank cells.

Unfiltered Data

Filtered Data

Unfiltered Data of Sales

Filtered Data of Sales

While the above can be done without a LET, using LET improves readability of the formula and computes twice as fast as without LET.

Sample data   

Copy the following table to cell A1 of a blank worksheet if you'd like to walk through this example yourself.

Rep

Region

Product

Profit

Amy

East

Apple

$1.33

Fred

South

Banana

$0.09

Amy

West

Mango

$1.85

Fred

North

$0.82

Fred

West

Banana

$1.25

Amy

East

Apple

$0.72

Fred

North

Mango

$0.54

Original Formula

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

Formula using LET

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-", filteredRange))

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×