LET function in Excel: readable formulas with named steps

LET syntax and rules

=LET(name1, name_value1, [name2, name_value2, …], calculation)

• name1: the first name, for example gross. It must start with a letter, may not look like a cell address (B2 or R1C1 are not allowed) and may not contain spaces.
• name_value1: what the name stands for: a number, a cell, a range or a calculation such as B7*C7.
• More pairs (optional): up to 126 name/value pairs. A later value may use earlier names, so you can build a calculation step by step.
• calculation: the last argument, always without a name. Its value is the result of the whole formula.

The names only exist inside this one formula. They do not appear in the Name Manager and cannot be used in other cells, which is the difference to named ranges. LET is available in Microsoft 365, Excel 2021 and later and Excel for the web; older versions show #NAME?.

Step by step: a quote with discount tiers

A quote gets 5% off from an order value of 2,000 and 10% off from 5,000, plus shipping of 1.50 per unit. Without LET the order value B7*C7 appears three times: once for the price and twice in the tier test.

=B7*C7*(1-IF(B7*C7>=$B$3,$C$3,IF(B7*C7>=$B$2,$C$2,0)))+B7*$B$4

With LET you calculate the order value once, name it, then name the discount rate and finally write the result in plain words:

=LET(gross,B7*C7, rate,IF(gross>=$B$3,$C$3,IF(gross>=$B$2,$C$2,0)), gross*(1-rate)+B7*$B$4)

For Q-102 (120 units at 18.50): gross = 2,220, which is at least 2,000 but below 5,000, so rate = 5%. The result is 2,220 × 0.95 + 120 × 1.50 = 2,109 + 180 = 2,289.00. Q-106 sits exactly on the second limit: 200 × 25 = 5,000 gets 10%, so 4,500 + 300 = 4,800.00. Column E holds the long version for every quote, and the check cell =SUM(D7:D12)-SUM(E7:E12) returns 0.00: LET changes how the formula reads, not what it returns.

LET with ranges and dynamic arrays

A name can stand for a whole array. =LET(gross,B7:B12*C7:C12,SUM(--(gross>=B2))) calculates all six order values at once and counts how many reach the first tier: 4 quotes. The double minus turns TRUE and FALSE into 1 and 0 so SUM can add them.

LET is especially useful with FILTER, where the same condition or the same filtered result is often needed twice. =LET(gross,B7:B12*C7:C12, net,D7:D12, AVERAGE(FILTER(net,gross>=B2))) averages the net value of the discounted quotes: 15,601.50 / 4 = 3,900.38. Another common pattern is to calculate a value once and use it both for a lookup and in a label: =LET(net,D7:D12, top,MAX(net), INDEX(A7:A12,MATCH(top,net,0))&" ("&TEXT(top,"#,##0.00")&")") returns Q-103 (6,120.00).

Worked example: six quotes priced with LET, checked against the long formula

The yellow cells are the discount tiers, the shipping fee per unit and the quotes. Column D uses LET, column E the same logic written out in full. Hover over a green cell to see its formula.

Worked example: six quotes priced with LET, checked against the long formula

	A	B	C	D	E
1	Setting	From order value	Discount
2	Discount tier 1	2,000	5%
3	Discount tier 2	5,000	10%
4	Shipping per unit	1.50
5
6	Quote	Units	Unit price	Net with LET	Same without LET
7	Q-101	40	25.00	1,060.00	1,060.00
8	Q-102	120	18.50	2,289.00	2,289.00
9	Q-103	300	21.00	6,120.00	6,120.00
10	Q-104	75	32.00	2,392.50	2,392.50
11	Q-105	15	99.00	1,507.50	1,507.50
12	Q-106	200	25.00	4,800.00	4,800.00
13
14	Summary formulas with LET				Result
15	Total net value of all quotes				18,169.00
16	Check: LET column minus the long version (should be 0)				0.00
17	Quotes that get a discount (gross ≥ tier 1)				4
18	Discount given in total				1,361.00
19	Average net value of the discounted quotes				3,900.38
20	Largest quote as a text label				Q-103 (6,120.00)
21	Shipping as a share of the total net value				6.19%

Fictional quotes and tiers. LET needs Microsoft 365, Excel 2021 or later or Excel for the web.

Why LET makes formulas faster and safer

In the long formula Excel calculates B7*C7 three times. With LET it calculates it once and reuses the stored value. For a multiplication that makes no visible difference, but if the repeated part is a FILTER over thousands of rows, an XLOOKUP or a SUMPRODUCT, calculating it once can shorten recalculation noticeably.

The bigger benefit is maintenance. When the tier rule changes, you edit one place instead of three, and you cannot forget one copy. The names also document the model: gross, rate and ship say more than a chain of cell references. Line breaks help too: in the formula bar, press Alt+Enter (Option+Return on a Mac) after each pair to put every step on its own line.

LET, helper columns or LAMBDA?

Helper columns are still the best choice when other people need to see and check each step, or when the intermediate results are useful on their own, such as a gross column that a pivot table uses. LET is the better choice when the steps only serve one result and would otherwise clutter the sheet. If you need the same calculation in many places, move it into a LAMBDA function and give it a name in the Name Manager; inside the LAMBDA you can again use LET for the steps.

A good habit for debugging: replace the final calculation with one of the names. =LET(gross,B7*C7, rate,…, rate) shows the rate on its own, so you can check each step before you put the final expression back.

Common mistakes

• Ending with a name/value pair. LET needs an odd number of arguments: pairs first, then one final calculation. =LET(x,B7*C7) has no calculation and is rejected.
• Names that look like cell references. tax1 is fine, but VAT1 or AB12 are valid cell addresses and cannot be used as names.
• Using a name before it is defined. Each value can only use names defined to its left. =LET(rate,gross*0.05,gross,B7*C7,rate) fails because gross does not exist yet.
• Expecting the names in other cells. LET names are local to one formula. For a value the whole workbook should use, create a named range or a cell for it.
• Naming a value that should be an input. Hard-coding rate,0.05 inside LET hides an assumption. Keep inputs in cells and use LET only for the calculation steps.