What are variables in Excel formulas? Definition and how to use them

Microsoft draws the comparison directly for LET: "Similar to variables in programming, LET is accomplished through Excel's native formula syntax." The motivation is the same as in code — a value written out four times is four places to be wrong, and an expression repeated twice is calculated twice.

Three scopes, three mechanisms

The word "variable" covers three different things in spreadsheet work, and the useful distinction between them is scope — who else can see the name.

Ablebits puts the first two side by side: "Essentially, the concept is the same as naming cells, ranges and formulas in the Name Manager. What makes the LET function different is that the declared names only exist in the scope of a given formula and nowhere else."

Named ranges have their own page and their own trade-offs. The short version for this purpose: a name in the workbook is durable and visible to anyone who opens the file, which is exactly right for an assumption other people need to find, and exactly wrong for a scratch value that only one formula cares about.

LET: variables inside a single formula

Microsoft's description is precise: "The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function."

The syntax is pairs, then a result. Exceljet's minimal examples show the shape — =LET(x,10,x+1) returns 11, and =LET(x,10,y,x*2,x+y) returns 30, because later variables can refer to earlier ones. Microsoft's constraints: "You must define at least one name/value pair (a variable), and LET supports up to 126", and "the last argument must be a calculation which returns a result."

Google Sheets has the same function with the same shape, spelling the arguments LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression). Google adds two details worth knowing: name usage "is case-insensitive", and "the value_expressions evaluates only once in the LET function, even if the next value_expressions or the formula_expression uses them multiple times."

What it buys

Two things, and Microsoft names both. Performance: "If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once." And legibility: "With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula."

Exceljet's example makes the first concrete. A lookup that has to be tested before it is returned naively runs twice — =IF(XLOOKUP(G5,B5:B16,D5:D16)="","",XLOOKUP(G5,B5:B16,D5:D16)) — and collapses to =LET(x,XLOOKUP(G9,B5:B16,D5:D16),IF(x="","",x)), which runs it once. "Programmers call this idea the DRY principle, short for 'Don't Repeat Yourself': define each piece of logic in one place only."

Exceljet is equally clear about when not to: "For short, simple formulas, LET just adds overhead. A formula like =B5*C5 does not benefit from variables."

The naming rules that bite

LET names follow the same rules as workbook names, and one of them causes most of the failures. Names begin with a letter or underscore, contain only letters, numbers and underscores, and are not case-sensitive — but they "must not conflict with Excel cell references like A1". Exceljet spells out the trap: "you can use names that contain numbers like count1 and count2, but a name like ct1 will fail, because Excel interprets CT1 as a valid cell address." Microsoft's version is shorter: "'a' is valid but 'c' is not because it conflicts with R1C1 style references."

The error message does not help. Exceljet: if a later argument turns out to be a cell reference, "you'll get a generic 'There's a problem with this formula' error", with nothing pointing at the name. A name you never declared behaves better, returning a plain #NAME?.

One collision is worth planning around: a LET name shadows a workbook name. Ablebits states it as a rule — "if the same name is assigned inside LET and defined in the name manager, the name manager version will be ignored." Avoid overlapping the two vocabularies.

On availability, Ablebits lists LET as present in Excel 365 for Windows and Mac, Excel 2021 for Windows and Mac, and Excel for the web; Exceljet's note is "LET requires Excel 2021+ or Excel 365."

Seeing what a variable holds

There is no inspector. Exceljet: "intermediate values are hidden: there is no direct way to inspect the value assigned to a variable while the formula runs", Evaluate Formula "does not work well with LET", and F9 on a LET name "will only return #NAME?". The workaround is to temporarily replace the final argument with the variable you want to see, then undo. Planning for it helps — assign the last calculation to a variable called result and return result, so troubleshooting only ever edits the final line.

How Formula Foundry handles this

Formula Foundry adds a third layer, and it is honest to say up front that it is the add-on's own, not a spreadsheet feature. Global @@variables let you define a value once in the sidebar — a tax rate, an FX rate, a cut-off date — and write @@TaxRate inside a formula. The value is substituted before the formula reaches the cell, and changing it once updates every formula that uses it. On team plans the approved set is shared, alongside the snippet library.

That is a different mechanism from both native options, with a different trade-off. A LET name lives inside one formula and is invisible outside it. A workbook name is a spreadsheet object anyone opening the file can see. An @@variable is managed by the add-on: colleagues who open the file without Formula Foundry see the substituted value in the cell, not the name. It is the right tool for the constant that is currently typed into eleven formulas, and the wrong one when the file has to be self-describing on its own.

For LET itself, what the editor offers is reading and writing help rather than execution. Formulas open across multiple lines with indentation, so each name/value pair sits on its own line, and functions, ranges, constants and named references are colour-coded. Parsing is locale-aware, errors are collected in one pass with targeted messages rather than a single generic complaint, and unknown names get a "did you mean" against the LET and LAMBDA names in scope plus your Formula Foundry variables — deliberately silent about workbook names the add-on cannot see. What it will not do is evaluate: it cannot tell you what a LET variable currently holds. The features overview covers the editor and the variables panel in Google Sheets and Excel.

FAQ