LET — making formulas readable and fast

1. Syntax

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

Pairs of name, value, and the last argument is the result. Names exist only inside this formula.

2. A first example

North's share of sales, without LET:

=SUM(FILTER(tblSales[Amount], tblSales[Region]="North")) / SUM(tblSales[Amount])

With LET:

=LET(
    sales,  tblSales[Amount],
    region, tblSales[Region],
    north,  SUM(FILTER(sales, region = "North")),
    total,  SUM(sales),
    north / total
)

→ 47.6%. Longer, but every part has a name. Press Alt + Enter inside the formula bar for new lines, and widen the bar (Ctrl + Shift + U).

3. Faster: calculate once

Without LET, repeated expressions are calculated each time they appear:

=IF(SUMIFS(tblSales[Amount], tblSales[Region], B1) > 300000, SUMIFS(tblSales[Amount], tblSales[Region], B1) * 2%, SUMIFS(tblSales[Amount], tblSales[Region], B1) * 1%)

With LET, once:

=LET(s, SUMIFS(tblSales[Amount], tblSales[Region], B1),
     IF(s > 300000, s * 2%, s * 1%))

For B1 = North: 5,09,000 × 2% = 10,180. On big data this can make a sheet noticeably faster.

4. LET + dynamic arrays: a whole report in one cell

=LET(
    names,  UNIQUE(tblSales[Salesperson]),
    totals, SUMIFS(tblSales[Amount], tblSales[Salesperson], names),
    share,  totals / SUM(totals),
    SORTBY(HSTACK(names, totals, share), totals, -1)
)

Spills a sorted 3-column table: Amit 4,19,000 39.2% · Ravi 2,36,000 22.1% · Sonal 2,10,000 19.6% · Neha 2,05,000 19.2%.

5. Debugging trick

Replace the final calculation with any name to see that step:

=LET(names, UNIQUE(tblSales[Salesperson]), totals, SUMIFS(tblSales[Amount], tblSales[Salesperson], names), totals)

Check names, then totals, then put the real calculation back.

6. Naming rules

Names must start with a letter, can't look like a cell address (A1, FY26 ✗ — FY26 is a column + row!), and shouldn't clash with function names. Use clear words: sales, region, fyStart.

Reference: Microsoft LET documentation.

Common mistakes

An even number of arguments: LET needs name/value pairs plus one final calculation, so the total is odd (at least three). Names like tax1 or q4 that look like cell references. Making LET so long that it should have been a helper column or a LAMBDA.