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

Enter your email address below to download the sample 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โ.

Matthew Smith

Finally got to use LET at work. Thanks for helping me understand it!

Mynda Treacy

Yay! Glad we could help.

Jeff Robson

A suggestion to simplify the formula in the Relative References section further would be to use SUMIFS instead of FILTER then SUM

e.g. BrandTotal = SUMIFS(Table1[Sales], Table1[Date], “>=” & MonthStart, Table1[Date], “<=" & MonthEnd, Table1[Brand], Brand)

Wes Stewart

Not a question, but a comment.

Thank you for your LET function tutorial and the sample workbook. I managed to expand on the function example to be able to select a Brand and find total sales for each month for each for each category. Took a bit of work, but I am happy with myself (LOL).

Mynda Treacy

Well done! Great to hear, Wes ๐

Teresa

What would be the formula column c is I want to divide a by b but sometimes b is zero ?

Philip Treacy

Hi Teresa,

Not sure you need to do this with LET?

If you want to avoid divide by zero errors use the IFERROR function

In your cell in Column C you’d have

Regards

Phil

Steve Buckley

I cannot find a definite answer about the general availability of the LET function. Do you know when it was (or is it?) released for general use, not when it was released for those in the Microsoft Insiders program? I want to use it in applications that other people use, but I don’t want them to see #NAME? when they use my spreadsheet. Thanks!

Mynda Treacy

Hi Steve, LET has been GA for a couple of months now. The only reason you’d see errors is if users are on a semi-annual update channel instead of monthly.

Peg Moter

I loved your video on the LET function! Is there a way to download the raw data workbook so that I can follow along and create the formulas as you do? The workbook I downloaded has that all done, and I can see the formulas, but I learn by doing as I see. Thanks!

Mynda Treacy

Hi Peg, great to hear you enjoyed my LET function video ๐ I don’t have a blank workbook, but you can just use an empty cell to recreate the formulas yourself, or delete mine and start again.

Mynda

NORMAN JOHN HARKER

Simple Question. Why is LET put in the Text function category? I would have thought Lookup & Reference was more appropriate.

Mynda Treacy

Hi Norman,

I don’t know the logic for putting LET in the text function category. Maybe it didn’t really fit in any category, so it was least out of place there. I don’t think I’d call it a lookup and reference function either. It needs a new category ๐

Mynda

Peta

Thanks so much Mynda for another great & clear how to..

I appreciate your efforts.

Mynda Treacy

Cheers, Peta! Glad you liked it ๐

Kevan Rice

Great lesson on the LET function, thank you

When it is released to the generic Office 365 version, will it work on prior versions of Excel or would you have to have Office 365?

Mynda Treacy

Thanks, Kevan! LET won’t ever go back and work in earlier versions of Excel. Only Office 365 and the next perpetual licence e.g. Excel 2022!

bernard liengme

Pease explain the use of # in the formula =XLOOKUP(D5#,A5:A14,B5:B14) in the Arrays sheet

Mynda Treacy

Hi Bernard, the # is a spilled range operator. It is a shortcut to referencing the whole spilled range, so instead of entering D5:D7 we can use D5# and if the spilled range changes size it will automatically find the correct range. More on dynamic array functions here.

Jon Wittwer

This is about how many times over the years that I’ve thought about wanting to define variables within a formula:

=LET(Awesome,1,Awesome*150)

This function is truly brilliant. Kudos to the Excel team, and thank you for the great explanation.

I am SO excited to start using this!

Mynda Treacy

Thanks for your kind words, Jon! Have fun with LET ๐