Excel LET Function Not Working? Fix Common Errors

If Excel LET is not working, first confirm you are using Excel 2021 or Microsoft 365, then check the formula’s argument order, variable names, and list separator. These checks resolve many #NAME? and #VALUE! errors, as well as formulas that appear not to calculate.

What the LET function does

LET assigns names to values or calculations inside a formula. You can then use those names later in the same formula instead of repeating a long expression. This can make a formula easier to read and can avoid recalculating the same expression multiple times. The names exist only within that LET formula; they do not create workbook-wide named ranges.

For example, a formula might calculate a discount once, call it discount, and then use it in the final calculation. LET is useful for multi-step formulas, but it does not change the underlying arithmetic. If a formula gives the wrong result without an error, check the calculation and cell references as well as the LET syntax.

Check whether your Excel version supports LET

LET is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual releases such as Excel 2019 do not support it. If you send a LET formula to someone using an unsupported version, their Excel may display an unsupported-function error, a name error, or an _xlfn. prefix in the formula. The exact display can vary by version and how the workbook was saved.

On Windows, check the installed release through File > Account > About Excel. On Mac, use Excel > About Microsoft Excel. If you have a Microsoft 365 subscription but an older build, install available updates; on Windows, check File > Account > Update Options. If your organization manages updates, an administrator may control when they appear. Excel for the web supports LET, although its menus and available features can differ from the desktop app.

Also check the file format. Save a modern workbook as .xlsx or, if it contains macros, .xlsm. The older Excel 97–2003 .xls format does not support newer formula features in the same way, so saving a workbook in that format can create compatibility problems. Keep a modern-format copy before converting a file.

Confirm the LET syntax and argument order

LET starts with a name, then the value assigned to that name, followed by any additional name-and-value pairs, and ends with the calculation to return. The final calculation is required. In other words, the structure is name, value, optional name, value, and final result. A missing value, an extra comma, or a missing final calculation can make the formula invalid.

With the product price in B2, where B2 contains 25, and an 8% tax rate, this formula returns 27:

With the price in B2: =LET(price,B2,taxRate,0.08,price*(1+taxRate))

Here, price is assigned the value in B2, and taxRate is assigned 0.08. The final expression uses both names. Notice that the formula does not put an equals sign between a name and its value; LET uses comma-separated arguments in this example.

Each name must be paired with a value. If you add a name but forget its value, or leave off the final calculation, count the arguments from left to right. For more complicated formulas, temporarily simplify the calculation to check whether Excel accepts each name/value pair before restoring the full expression.

Use valid, consistent variable names

LET names follow Excel’s naming rules. Use a name that begins with a letter or underscore, has no spaces, and does not look like a cell reference. Names such as unitPrice, taxRate, and rowTotal are generally clearer than short or ambiguous names. Avoid names that could be interpreted as references, such as B2, and do not use a name that conflicts with a valid range reference in your workbook.

Variable names are local to the formula, but Excel still checks that they are valid names. An invalid name can cause an error even when the rest of the arithmetic is correct. Also check spelling and consistency: if you define unitPrice but later type unitprice or unitCost, Excel may not recognize the reference as intended. Reusing the exact same spelling throughout makes formulas easier to audit.

Names are evaluated in order. A later name/value pair can use a name defined earlier in the same LET function, but it cannot refer to a name that has not yet been defined. Arrange the pairs so each calculation only depends on names that come before it. The final calculation can use all the names established earlier in the formula.

Match the formula separator used by your Excel

Many English-language Excel installations use commas between function arguments, as in the examples here. Some regional settings use semicolons instead. If Excel rejects a formula even though its structure looks right, check the separator used by another working formula on the same computer and replace the LET separators consistently. Do not mix commas and semicolons in one formula.

The argument separator is separate from the decimal separator. A regional setup may use a comma for decimals and a semicolon between arguments. If you copy a formula from a website or a colleague, its separators may not match your system. Excel may show a formula-entry warning when it cannot parse the pasted formula, so adapting the separators can be the fix rather than changing the calculation.

Diagnose the error or unexpected result

The error message is a useful clue, but it does not always identify the exact cause. Check the formula bar to see the full formula, then inspect it from left to right. These are common patterns:

#NAME?: Excel may not recognize LET because the installed version is too old, or it may not recognize a variable because of a spelling mistake. Check for an _xlfn. prefix when a workbook is opened in an older release.
#VALUE! or an argument warning: Check for an invalid name, an incomplete name/value pair, or a missing final calculation. The exact message depends on the particular mistake.
Formula appears as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, edit the formula, and press Enter to make Excel interpret it as a formula.
Formula displays instead of its result: Check whether formula display is enabled. On Windows, Ctrl+` toggles Show Formulas; the backtick key is usually near the top-left of the keyboard. You can also check Formulas > Show Formulas.
Result is stale: Check Formulas > Calculation Options and select Automatic if appropriate. In Manual calculation mode, formulas may not update until Excel recalculates.

When a formula returns a plausible but incorrect number, verify the referenced cells, percentages, and parentheses. LET does not automatically treat a number as a percentage: a tax rate of 8% should be represented as 0.08 or a cell containing 8%. If a cell contains 8 and the formula multiplies by it directly, the result will use 800%, not 8%.

Test a small example before rebuilding a long formula

A short working example can separate a LET compatibility or syntax problem from a problem in a larger formula. With a price of 25 in B2 and a rate of 0.08 in C2, this formula returns 2:

With the price in B2 and the rate in C2: =LET(net,B2,rate,C2,net*rate)

If this simple formula works, LET itself is available and the basic syntax is accepted. The problem in the longer formula is more likely to involve a name, reference, separator, or argument pair. If the small example fails, check the Excel version and regional separator before investigating the original calculation.

You can also use LET to name intermediate results. With values in B2:B10, this formula returns their average; if there are no numeric values, it returns 0 rather than dividing by zero:

With the values in B2:B10: =LET(total,SUM(B2:B10),itemCount,COUNT(B2:B10),IF(itemCount=0,0,total/itemCount))

For example, if B2:B4 contain 10, 20, and 30 and the remaining cells are blank, the sum is 60 and the numeric count is 3, so the result is 20. The names total and itemCount make the final expression easier to understand than repeating the SUM and COUNT calculations.

When to use LET, and what to do without it

LET is most useful when a formula repeats the same calculation, has several clear intermediate steps, or is difficult to read as one long expression. It is not necessary for a short formula that is already easy to follow. It can improve readability, but it will not automatically fix incorrect references, inconsistent units, or a mistaken business rule.

If a workbook must work in an older Excel version, consider replacing LET with ordinary formulas or helper cells. Helper cells can show each intermediate result separately, which is often easier for other users to inspect and troubleshoot. The trade-off is that the worksheet may need more cells, and the calculation is less self-contained. Before replacing LET, confirm the oldest Excel version your recipients actually use; upgrading may be simpler if they have access to a supported release.

Frequently Asked Questions

Why does Excel show #NAME? for LET?
Most often, the Excel version does not support LET or a name inside the formula is misspelled. Check the version through the About screen, then compare each variable name with its definition. An _xlfn. prefix can indicate that the workbook is open in an older Excel release.

Does LET work in Excel 2019?
No. LET requires Excel 2021 or later, or Microsoft 365. A person using Excel 2019 may need an updated Excel version, a formula rewritten without LET, or helper cells that calculate the intermediate steps.

Can LET use semicolons instead of commas?
Yes, when the Excel installation’s regional settings use semicolons as function-argument separators. Use the separator your Excel expects throughout the formula. Decimal and argument separators are separate regional settings, so check a working formula in the same installation.

Can a LET variable be used in another cell?
No. A LET name is available only inside the LET formula where it is defined. To reuse a value in other formulas, use a worksheet cell or create a workbook-level name through Excel’s naming features.

Why does my LET formula show the formula instead of a result?
The cell may be formatted as Text, the formula may begin with an apostrophe, or Show Formulas may be on. Set the cell to General and re-enter the formula; if formulas are being displayed across the sheet, turn off Formulas > Show Formulas.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.