Excel LET Function

The Excel LET function* enables you to declare variables and intermediate calculations inside of a formula. It’s like the DAX VAR function or ‘let’ inside of Power Query. Those familiar with Power Pivot, Power Query or programming will understand these terms, but don’t be put off. LET is a dead easy function to learn and improves the readability and performance of your formulas.

The LET function syntax is:

For example: =LET( x, 5, y, 10, x + y)

Result: 15

*The Excel LET function is currently in beta in the Office 365 Insiders build. This is a sneak preview for those who don’t have it yet.

LET Function Arguments Explained

name1  - The name of the variable (cannot be the output of a formula or conflict with range syntax)

value1   - Values can be text, formulas/expressions, arrays, numbers, cell references, Boolean values or defined names

name2  - Optionally add more name and value pairs as required

value2   - As above

calculation - the formula/expression that uses the names within the LET function.

Notes:

• LET will not error if you don't use all the names, but any unused names are redundant, so you should remove them to avoid confusion and unnecessary work for Excel
• The formula should have an odd number of arguments i.e. pairs of names and values, then a calculation
• The ‘calculation’ can be contained inside of a name and value pair. e.g. here I’ve declared a name for the formula; ‘result’ =LET(x, 5, y, 10, result, x + y, result)
• Values can reference previously declared names, but not names downstream e.g. x in this formula is later referenced in y’s value argument =LET(x, 5, y, x+1, total, y * 2, total) = 12
• Avoid using names in the LET function that are already defined names in the name manager. If you use a name already defined in the name manager, LET will ignore the name manager version
• Names defined inside of LET are available in the intellisense drop down.
• LET formulas can be defined as names in the name manager

Watch the Excel LET Function Video

Download the Excel LET Function Workbook

When to use the LET Function

Improve Formula Readability

Naming variables, like ranges being referenced etc., makes it easier to understand what the formula is doing. Before the LET function we would define names in the Name Manager for this purpose, however LET now enables us to define names more quickly inside of the formula itself making it quicker to write and quicker to interpret.

For example, in the formula below we can easily understand that it is calculating sales from cells C2:C500 including GST of 10%, without the need to refer to any other cells, sheets or defined names:

=LET(

GST, 10%

Sales, C2:C500,

SalesIncl.GST, Sales*1+GST,

SalesIncl.GST)

The limitation of defining names inside of LET is that those names can only be used inside of that specific LET formula whereas names defined in the name manager can have the scope of the workbook.

Improve Calc Performance

The other reason to use LET is for improved performance through the elimination of duplicate calculations. By naming calculations inside of LET they are being evaluated once at the beginning of the formula and the results can then be re-used multiple times in the formula without requiring further calculations. This can significantly improve performance.

For example, the formula below performs the SUM at least twice. Once for the logical test and again for either the TRUE or FALSE outcome:

Whereas with LET we can reduce this to a single calculation by declaring it as a name, SalesCY, and then reusing the name later in the formula:

Obviously in practice there will be more efficiency to be gained with more complex formulas.

Excel LET Function Examples

Referencing Names in Values

Names you define can be used inside of downstream value arguments. For example, the value argument for the name ‘agg’ in the formula shown below references the name ‘select’ which was previously declared.

When you’re authoring a LET function, prior names will be available in the intellisense drop down to choose from, just like a name defined in the name manager:

Relative References

In this example I want you to take notice of the use of relative references in the name arguments.

First, the data I’m using is called Table1. It contains sales by brand and product category:

Below I’ve summarised the table by brand and month using a LET formula:

The formula in cell G4 (wrapped onto separate lines for readability) is:

=LET(

MonthStart, G$3,

MonthEnd, EOMONTH(G$3,0),

Brand, $F4,

BrandRng, FILTER(Table1[Sales], (Table1[Brand]=Brand)*(Table1[Date]>=MonthStart)*(Table1[Date]<=MonthEnd)),

SUM(BrandRng)

)

You can see in the image above the LET formula nests the EOMONTH function  and the FILTER function which perform calculations that are later used in the SUM calculation argument.

Also note that the first three ‘value’ arguments for each name use relative references in either the row or column, thus allowing the LET function to be copied to the remaining cells in the table.

Tip: When copying formulas with Table Structured References, be sure to copy and paste as opposed to left clicking and dragging to ensure the absolute referencing of the structured references is applied correctly.

Arrays as an Input and Output

You may have already noticed in the second example that the LET function can accept arrays as inputs, but it can also return arrays as outputs. The ‘RankArray’ name in the example below has an array as a value {1;2;3}, and it returns an array, being the list of top 3 brands which spills to the cells D5:D7:

Debugging LET Formulas

You can easily check the results returned by a name and value pair by placing that name in the last argument (which is ‘calculation’). In the example below in cell C6 the calculation has been declared as a name (calc) and value pair, and the last argument simply repeats the last name to return the result:

However, we can test any name and value pair by replacing the last argument with a different name. For example, say I want to check how the ‘Rng’ name evaluates. In the image below I’ve placed ‘Rng’ in the last argument of the LET formula and you can see in cell C6 it now spills the sales values from Table1:

I can then simply replace ‘Rng’ with ‘Calc’ in the last argument to return the final calculation I want, without losing the formula I authored for ‘Calc’.