LET in Excel: how to use it
With LET you give an intermediate result a name inside your formula. Excel works it out once, and a long formula becomes easier to read. Works in Excel 2021 and 365.
Try the first exercises for freeIn the lesson Excel 365 for advanced users
What does LET do?
LET gives an intermediate result a name, within one formula. First you put the name, then what it stands for, and last the calculation that uses the name. Excel only calculates the value once, even if you use the name several times.
That makes long formulas shorter and easier to read: instead of A2*B2 three times, it says total three times. You can give several names, each time a name and a value, and a name can use an earlier name. LET works in Excel 2021 and 365.
When do you use LET?
- A calculation that comes back three times, such as a total with a discount: =LET(total,A2*B2,IF(total>100,total*0.9,total)).
- What percentage a value is above or below the average: =LET(avg,AVERAGE(B2:B20),(B2-avg)/avg).
- Checking a price you looked up without looking it up twice: =LET(p,XLOOKUP(E1,A2:A9,B2:B9,0),IF(p=0,"Unknown",p)).
- Making a long formula readable with names such as price and quantity: =LET(price,A2,quantity,B2,price*quantity).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =LET(total,A2*B2,IF(total>100,total*0.9,total))
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
LET | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | LET |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
name | A name you choose yourself, such as total or x. It mustn't look like a cell address. | total |
value | What the name stands for: a number, a cell, a range or a calculation. | A2*B2 |
calculation | The formula that uses the name. This argument always comes last. | IF(total>100,total*0.9,total) |
, | A comma separates the arguments from each other. | , |
LET step by step
C2 contains =LET(total,A2*B2,IF(total>100,total*0.9,total)). 12.50 × 10 is 125, more than 100: with a 10% discount you pay 112.50. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C2 and type LET with a bracket.
=LET( - Type the name, total, and a comma.
=LET(total, - Give what the name stands for, A2*B2, and type a comma.
=LET(total,A2*B2, - Type the calculation with the name: a 10% discount if the total is over 100.
=LET(total,A2*B2,IF(total>100,total*0.9,total) - Close the bracket and press Enter: 112.50.
=LET(total,A2*B2,IF(total>100,total*0.9,total))
More examples
Two names
C2 contains =LET(price,A2,quantity,B2,price*quantity). Two names, each with its value, and the calculation last.
The difference from the average
D2 contains =LET(avg,AVERAGE(B2:B4),(B2-avg)/avg). The average is 14. Lucy gets 16: about 14% above the average.
A name that uses an earlier name
C2 contains =LET(total,A2*B2,tax,total*21%,total+tax). tax calculates with total: 125 plus 26.25 tax is 151.25.
Where it often goes wrong
- A name can't look like a cell address: x or total is fine, A1 or VAT1 isn't.
- The name only exists inside that one formula. In another cell Excel doesn't know it: there you get #NAME?.
Practise this in the lesson Excel 365 for advanced users. The first exercises are free.
Frequently asked questions
What's the difference from a named range?
A named range applies to the whole workbook and refers to cells. A name in LET only applies inside that one formula and can hold any calculation.