LET Function in Excel: Name the Parts of a Formula

The LET function in Excel lets you name the parts of a formula. Instead of repeating the same calculation three times, you name it once, such as total, and use the name wherever you need it. Your formula gets shorter, easier to check and often faster. This guide is for anyone who writes long formulas in Microsoft 365 or Excel 2021 and wants them to read more like plain sentences.

What does the LET function do?#

LET stores a value under a name for the length of one formula. The value can be a number, a range or a whole calculation. The last argument is the calculation that uses those names, and its answer is what the cell shows.

=LET(name1, value1, [name2, value2, ...], calculation)

A tiny example: =LET(x, 5, x*2) returns 10. The name x means 5, and the calculation doubles it.

LET is part of the newer set of functions that came with dynamic arrays. It needs Microsoft 365, Excel 2021, Excel 2024 or Excel for the web. Excel 2019 and earlier show #NAME?. The overview of dynamic arrays in Excel explains what else arrived at the same time.

Why use LET? A formula without it#

LET exists because long formulas often repeat the same piece. Take this sales table in A1:C8:

The North team earns a 5% bonus on its total sales if the total is over 10,000, and 3% if not. Without LET, the formula repeats the North total three times:

=IF(SUM(FILTER(C2:C8,B2:B8="North"))>10000,SUM(FILTER(C2:C8,B2:B8="North"))*5%,SUM(FILTER(C2:C8,B2:B8="North"))*3%)

It works, but it is hard to read, and if the region changes you must edit it in three places. Excel also runs the same FILTER three times.

How to write a LET formula, step by step#

Here is the same bonus with LET:

=LET(north, FILTER(C2:C8,B2:B8="North"), total, SUM(north), IF(total>10000, total*5%, total*3%))

Read it from left to right:

1. north, FILTER(C2:C8,B2:B8="North") names the list of North sales: 4,200, 5,600 and 3,800.
2. total, SUM(north) adds that list and names the answer: 13,600. A later name can use an earlier one.
3. IF(total>10000, total*5%, total*3%) is the calculation. 13,600 is over 10,000, so the result is 13,600 × 5% = 680.

To change the region, you now edit one word. If you are new to FILTER, the FILTER function guide and the article on how FILTER returns matching rows explain it.

Long LET formulas are easier to read on several lines. Press Alt+Enter (Mac: Option+Return) inside the formula bar to start a new line, and widen the formula bar with the arrow at its right edge.

Rules for LET names#

LET names follow the same rules as defined names, with a few extra points:

• Start with a letter (or an underscore). Don't use spaces. north_total works, north total doesn't.
• A name can't look like a cell address. tax1 is fine, but TAX2026 is a real cell in column TAX, so Excel rejects it.
• Names are not case-sensitive. Total and total are the same name.
• Each name exists only inside its own formula. Another cell can't use it.
• You need pairs. Name, value, name, value, and then one final calculation. An even number of arguments means the calculation is missing, and Excel will not accept the formula.

If you want a name the whole workbook can use, create a defined name with Formulas > Define Name instead. LET is for names that only one formula needs.

When does LET make formulas faster?#

LET helps speed when a heavy piece is repeated. Every FILTER, XLOOKUP or SUMPRODUCT in a formula is a calculation. Without LET, a piece that appears three times runs three times. With LET, it runs once.

On a small sheet you won't notice. On a large model with thousands of rows, removing repeated lookups can matter. The guide to why Excel formulas get slow lists other fixes.

LET is also a neat way to avoid repeating a lookup just to hide a blank or an error, a pattern you will know from making VLOOKUP return blank instead of 0:

=LET(price, XLOOKUP(F2,A2:A8,C2:C8,""), IF(price="","",price*1.2))

The lookup runs once, and the name price is checked and used.

Common LET errors#

Most LET errors come from missing pieces:

1. #NAME? in older Excel. The file was opened in Excel 2019 or earlier. Replace LET with a normal formula, or use helper columns.
2. #NAME? in new Excel. A name is misspelled in the calculation, or it is used before it is defined. Names must be defined left to right.
3. "There's a problem with this formula". Usually a missing comma, or no final calculation after the last pair.
4. A name that looks like a cell. Names such as q1 or Q2026 are real cell addresses, so Excel rejects them. Use q_1 or q_2026 instead.

To check a LET formula, select the cell and use Formulas > Evaluate Formula (Windows) to step through it, or test each value in a spare cell first.

Practise it#

LET is easiest once FILTER and the other modern functions feel natural. Start with the exercise on this page, which builds the north piece. Then practise FILTER in a live spreadsheet and work through the modern Excel functions lesson, where every formula is checked as you type. When you can write the pieces, wrapping them in LET is one small step.