Write Readable Formulas by Naming Values with LET

Excel & Google Sheets

How it works

LET works in name/value pairs: total is defined as SUM(B2:B10), count as COUNTA(B2:B10), and the final argument is the calculation that uses them — total/count, the average order value. Each piece is computed once and reused by name, which makes long formulas dramatically easier to read and often faster, because the spreadsheet no longer recalculates the same expression several times. Names must start with a letter and can't look like a cell reference — dealTotal works, D1 doesn't. LET needs Excel 365 or Excel 2021; anything older shows #NAME?. Google Sheets added LET in 2023 with identical syntax, so current Sheets runs the exact same formula unchanged.

total,SUM(B2:B10)

First pair: the name total now stands for the sum of the order values.

count,COUNTA(B2:B10)

Second pair: count stands for how many orders there are. Add as many pairs as you need.

total/count

The last argument is the result — the calculation, written with the names instead of repeated ranges.

When to use it

Use it whenever a formula repeats an expression — a lookup used in both an IF test and its result, one total feeding several ratios — or when a formula has grown too dense for the next person to follow.

Common mistakes

• ✕Forgetting the final calculation.

  ✓=LET(total,SUM(B2:B10)) errors — LET needs pairs of name/value, then one last unpaired argument that produces the result.

• ✕Naming a value like a cell reference.

  ✓=LET(Q1,SUM(B2:B10),Q1*2) fails because Q1 is a cell address. Use words: qtr1Total.

• ✕#NAME? in older versions.

  ✓LET needs Excel 365/2021 (Google Sheets has had it since 2023). In Excel 2019 and earlier, the fallback is helper cells.