Cellwise Start free

Logic function

Excel LET function

LET gives names to values inside one formula. =LET(total,SUM(C2:C7),total*E2) works out the sum once, calls it total, and uses the name in the last argument. Long formulas get shorter, easier to read and faster to calculate.

=LET(name1, name_value1, calculation_or_name2, ...)

Arguments

name1
The first name, such as total or rate. It must start with a letter and cannot look like a cell address (Q1 or tax1 are cells).
name_value1
The value, range or formula the name stands for.
calculation_or_name2
Either the final calculation, which can use every name, or another name followed by its value. The last argument is always the calculation.

LET examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1RepRegionSalesCommission rate
2PriyaWest184000.04
3MarcusEast15200Target
4LenaWest2010018000
5TomNorth16800
6InesEast22900
7OmarWest14300

Download this sheet as an Excel practice file (.xlsx)

  1. =LET(total,SUM(C2:C7),total*E2)

    Result: 4308

    Names the sales total, then multiplies it by the rate in E2: 4,308 in commission.

  2. =LET(avg,AVERAGE(C2:C7),COUNTIF(C2:C7,">"&avg))

    Result: 3

    Works out the average once (17,950) and counts the reps above it: 3.

  3. =LET(sales,C2:C7,MAX(sales)-MIN(sales))

    Result: 8600

    A name can hold a range. The gap between the best and the weakest rep is 8,600.

  4. =LET(west,SUMIFS(C2:C7,B2:B7,"West"),target,E4*3,IF(west>=target,"On target","Short by "&target-west))

    Result: Short by 1200

    Two names, then a readable test: West sold 52,800 against a target of 54,000, so it is short by 1200.

How to use LET

  1. Click the cell for the result.
  2. Type =LET( then a name, a comma and its value.
  3. Add more name, value pairs if you need them. A later value can use an earlier name.
  4. End with the calculation that uses the names, close the bracket and press Enter.

Common LET mistakes

A name that is a cell address

Names like q1, tax1 or abc123 are cell addresses, so Excel will not accept the formula. Use names such as qtr1_sales or tax_rate.

Forgetting the final calculation

LET needs an odd number of arguments: pairs of name and value, then the calculation. =LET(x,5) has no calculation and is rejected.

Using a LET name in another cell

LET names exist only inside their own formula. Another cell that types total gets #NAME?. For a name the whole workbook can use, go to Formulas > Define Name.

Using it in older Excel

LET needs Microsoft 365, Excel 2021 or later, or Excel for the web. Older versions show #NAME?.

LET FAQ

What does LET do in Excel?

It assigns names to values or formulas inside one formula, then uses those names in a final calculation. Each named part is calculated once, even if the calculation uses it several times.

Does LET make Excel formulas faster?

It can. Without LET, a part you repeat is calculated every time it appears. With LET it is calculated once and reused, which helps with slow parts such as FILTER or XLOOKUP over large ranges.

What are the rules for LET names?

A name starts with a letter, has no spaces, and cannot be a cell address such as Q1 or a value such as TRUE. Each name can be defined only once in the same LET.

Can I use LET with FILTER?

Yes, and it reads well: =LET(west,FILTER(A2:C7,B2:B7="West"),SORT(west,3,-1)) names the filtered rows, then sorts them by sales.

Related functions

Updated