Names and custom functions

LET function playground

Names values or calculations inside a formula, so long formulas are shorter, clearer and calculate each part once.

Available in: Excel 2021 and later, Microsoft 365Read the full tutorial →

The playground runs in your browser. Nothing you type is sent anywhere.

How LET works

LET works in pairs: a name, then the value or calculation it stands for. The last argument is the calculation that uses those names, and its result is what the cell shows.

Later names can use earlier ones, so you can build a formula step by step. Each named part is calculated once, even if you use the name several times.

The names only exist inside that one formula. They don't appear in Name Manager and other cells can't see them.

Syntax

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

Argument	What it means
`name1`required	A name for the first value. It must start with a letter and can't look like a cell address (TAX1 won't work).
`value1`required	The value, range or calculation the name stands for.
`name2, value2, ...`optional	More name and value pairs. Later ones can use earlier names.
`calculation`required	The last argument: the calculation that uses the names. Its result is the answer.

Examples

Formula	What it does	Result
`=LET(total,SUM(F2:F11),units,SUM(E2:E11),total/units)`	Average revenue per unit, with each part named.	$578.79
`=LET(east,SUMIFS(F2:F11,B2:B11,"East"),west,SUMIFS(F2:F11,B2:B11,"West"),east-west)`	How far East is ahead of West.	$1,450.00
`=LET(r,F2:F11,MAX(r)-MIN(r))`	Name a range once and use it twice: the spread of sale sizes.	$4,050.00
`=LET(goal,20000,actual,SUM(F2:F11),IF(actual>=goal,"Goal met","Short by "&TEXT(goal-actual,"$#,##0")))`	A readable goal check with a message.	Short by $900

Common mistakes

Odd number of argumentsEvery name needs a value, and the calculation comes last, so LET always has an odd number of arguments.

Names that look like cellsNames such as A1, TAX2 or Q1 look like cell addresses and are rejected. Use tax_rate or q_one instead.

Older ExcelExcel 2019 and earlier don't have LET and show #NAME?.

Learn more

Download the example workbook (.xlsx)

The workbook has the same sample data and formulas, plus a practice sheet.

Read the full tutorial →