You keep typing the same tax rate or the same long calculation into a dozen Excel formulas, and you want to store it once and refer to it by name. Excel does this two ways: a defined name (Name Box or Formulas > Define Name) works across the whole workbook, and the LET function creates variables that live inside a single formula.
Defined names work in every current desktop version. LET needs Excel for Microsoft 365, Excel 2024 or Excel 2021.
What “Variables” Mean in Excel
Excel has no “declare a variable” command for worksheet formulas the way a programming language does. Instead, it gives you two features that do the same job:
- Defined names. A name is a label such as
TaxRatethat stands for a cell, a range, a fixed value or a formula. Once it exists, any formula in the workbook can use it, and changing the name’s value updates every formula that uses it. - LET variables. The LET function lets you name an intermediate result inside one formula, then reuse that result later in the same formula. Microsoft describes it as a function that “assigns names to calculation results,” and those names only exist within that LET formula.
Two other meanings show up in searches. If you write macros, “variables” means VBA variables declared with Dim, which has its own short section near the end. If you’re doing regression analysis, a “dummy variable” is a 0 or 1 column of data, which is a statistics topic rather than an Excel feature, and it isn’t covered here.
Here is how the two compare:
| Defined name | LET variable | |
|---|---|---|
| Where it lives | The workbook (or one sheet), listed in Name Manager | Inside one formula only |
| Who can use it | Any formula in its scope | Only the rest of that same formula |
| Best for | Values many formulas share, such as a tax rate or a lookup table | A calculation you would otherwise repeat two or more times in one formula |
Name a Cell With the Name Box
The Name Box is the small field to the left of the formula bar that normally shows the address of the selected cell, such as F1. It’s the quickest way to create a name.
- Select the cell or range you want to name. For a tax rate, that’s the single cell that holds the rate.
- Click inside the Name Box. The cell address is highlighted.
- Type the name, for example
TaxRate, using no spaces.

- Press Enter to save the name.
To check that it worked, click any other cell, then open the Name Box drop-down. Your name appears in the list, and choosing it jumps straight to the named cell.
Create a Name With Define Name
The Define Name dialog takes a few more clicks, but it lets you set the scope, add a comment, and point the name at a fixed value or a formula instead of a cell.
- Go to the Formulas tab and, in the Defined Names group, select Define Name.
- In the Name box, type the name (up to 255 characters).
- Leave Scope set to Workbook (the default) unless the name should only work on one sheet.
- Optionally type a Comment, such as “Sales tax for the 2027 price list.” Comments can be up to 255 characters.
- In Refers to, enter a cell reference, a constant, or a formula that starts with an equal sign.

- Select OK.
Name Several Cells at Once With Create From Selection
When your sheet already has labels next to the values, Excel can turn those labels into names for you. Say cells E1:F3 hold this small settings block:
| E | F |
| TaxRate | 0.0825 |
| ShippingFee | 12 |
| BulkDiscount | 0.05 |
- Select the labels and the values together (E1:F3 in this example).
- On the Formulas tab, select Create from Selection.
- Check the box that matches where your labels sit: Top row, Left column, Bottom row or Right column. Here, that’s Left column.
- Select OK. Excel creates three names, each pointing at the value cell beside its label.
This also works for whole columns of data. With a header such as Price in the top row, Create from Selection names the cells beneath it, so a formula can read =SUM(Price) instead of =SUM(B2:B6).
Rules Excel Applies to Names
If Excel rejects a name, one of these rules from Microsoft’s names in formulas page is usually the reason:
- First character: a letter, an underscore (_) or a backslash (\). The rest can be letters, numbers, periods and underscores.
- No spaces. Use an underscore or a period instead, as in
Tax_RateorTax.Rate, or run the words together asTaxRate. - Not a cell reference. A name can’t look like an address, such as
Z$100orR1C1. That’s whyQ1fails as a name, whileSales_Q1works. - Not C, c, R or r on their own. Excel reserves those single letters.
- Up to 255 characters.
- Not case-sensitive. Excel treats
TaxRateandTAXRATEas the same name, so you can’t create both in the same scope. - Unique within its scope. Two workbook-level names can’t share a spelling.
Worked Example: Store a Tax Rate as a Named Constant
Here is a small order sheet for a fictional shop, Harlow Garden Supply. Columns A to C hold the item, unit price and quantity:
| A: Item | B: Price | C: Qty | D: Total with tax |
|---|---|---|---|
| Hand trowel | 14.50 | 4 | 62.79 |
| Seed tray | 3.25 | 20 | 70.36 |
| Hose reel | 89.00 | 1 | 96.34 |
Without a name, the total in D2 might be =B2*C2*(1+$F$1), with the rate in F1. That works, but the formula doesn’t say what F1 is, and the dollar signs are easy to forget when you copy it. (If you want the background on that, see how percentage formulas in Excel add or remove a rate.)
You can store the rate as a named constant, with no cell at all:
- Select Formulas > Define Name.
- Type
TaxRatein Name. - In Refers to, replace whatever is there with
=0.0825. - Select OK.
- In D2, enter
=B2*C2*(1+TaxRate)and fill it down to D4.

D2 returns 62.79 (14.50 times 4 is 58.00, plus 8.25 percent). Because a name doesn’t shift when you copy a formula, there are no dollar signs to manage. When the rate changes, open Name Manager, edit TaxRate, and every total recalculates.
Which is better, a named cell or a named constant? A named cell shows the rate on the sheet where anyone can see and change it. A named constant is tucked away in Name Manager, so it’s harder to overwrite by accident but also easier to forget. For a shared file, a clearly labeled settings cell is usually the friendlier choice.
Name a Formula You Use Often
The Refers to box also accepts a formula. That lets you give a recurring calculation a short name. For the order sheet above, you could define OrderTotal with Refers to set to:
=SUM(Sheet1!$D$2:$D$4)
Now =OrderTotal in any cell shows the sum, and =OrderTotal+ShippingFee reads like a sentence. Keep named formulas simple and use absolute references (with dollar signs) in them, so they always point at the same cells. If you find yourself wanting a named formula that takes inputs, that’s what LAMBDA is for (covered below).
Use Names in Your Formulas
You don’t have to remember exact spellings. Microsoft’s define and use names page describes two ways to insert one:
- Type the first letters. After the equal sign, type
Taxand Excel’s formula autocomplete lists matching names. Select the one you want. - Formulas > Use in Formula. While you’re editing a formula, open this menu on the Formulas tab and pick a name from the list.
Names work anywhere a cell reference would. A named lookup range makes VLOOKUP formulas easier to read and to copy, for example =VLOOKUP(A2, PriceList, 2, FALSE), and the same idea applies to the arrays you pass to XLOOKUP.
Workbook Scope, Sheet Scope and Other Workbooks
Every name has a scope, which Microsoft defines as the place where the name is recognized without qualification.
- Workbook scope (the default): every sheet in the file can use the name by itself, such as
=TaxRate. - Worksheet scope: only that sheet recognizes the bare name. From another sheet, put the sheet name in front, as in
=Sheet1!Budget_FY08, which is Microsoft’s own example.
Sheet scope is handy when several sheets need the same name with different values, for example a Target name on a January sheet and another on a February sheet. If a sheet-level name and a workbook-level name share a spelling, Microsoft says you can reach the workbook-level one by putting the workbook name in front, as in WorkbookFile!GrossProfit.
Can you use a name from a different workbook? Yes, but not by the bare name. A workbook-scoped name isn’t recognized by other workbooks on its own, so you write an external reference: the workbook name, an exclamation point and the name. Microsoft’s workbook links page gives =SUM(Budget.xlsx!Sales) as the pattern. While the source file is open, the link shows just the workbook name; when it’s closed, Excel shows the full path. Linked files can break when someone moves or renames the source, so for a single shared value, copying the name into each workbook is often simpler.
Edit, Rename or Delete Names in Name Manager
Name Manager is the control panel for every name in the workbook. Open it from Formulas > Defined Names > Name Manager (Microsoft’s Name Manager page describes each column). Its columns show each name’s Name, Value, Refers To, Scope and Comment, and the Filter drop-down narrows the list when a workbook has dozens of names.
To edit or rename a name:
- In Name Manager, double-click the name, or select it and choose Edit.

- Change the Name, the Comment or the Refers to value.
- Select OK, then Close.
Renaming is safe: Microsoft says that when you modify a defined name, all uses of that name in the workbook change too.
Deleting is not as forgiving. Before you delete a name, use Find (search formulas for the name) to see where it’s used, because any formula that still refers to a deleted name shows #NAME?. To delete:
- Select the name in Name Manager. Hold Shift to select a block of names, or Ctrl to pick several separate ones.
- Select Delete, then confirm with OK.
Name Manager doesn’t show every name. Names created by VBA code and hidden names don’t appear in the list, so a file can contain names you can’t see there.
Use LET for Variables Inside One Formula
Sometimes you only need a variable for the length of one formula, because the same piece of math appears in it more than once. That’s the LET function. The syntax from Microsoft’s LET function page is:
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
In plain terms, you list pairs of a name and its value, and the last argument is the calculation that uses them. You need at least one pair, and LET supports up to 126. The simplest example, =LET(x, 5, SUM(x, 1)), returns 6.
Back at Harlow Garden Supply, say orders over 500 get the 5 percent bulk discount. Without LET, the line total appears three times:
=IF(B2*C2>500, B2*C2*(1-BulkDiscount), B2*C2)
With LET, you calculate it once and give it a name:
=LET(subtotal, B2*C2, IF(subtotal>500, subtotal*(1-BulkDiscount), subtotal))
You can chain pairs, and later values can use earlier names. This version adds tax as a second step:
=LET(subtotal, B2*C2, discounted, IF(subtotal>500, subtotal*(1-BulkDiscount), subtotal), discounted*(1+TaxRate))
The formula is easier to read, and Excel works out subtotal once instead of three times. Microsoft notes that an expression written several times is calculated several times, which matters most with heavy functions such as FILTER on large ranges.
A few LET rules to know:
- LET names must start with a letter and otherwise follow the same rules as Name Manager names. So
aorsubtotalworks, butcor a name likeR1C1doesn’t. - The last argument must be a calculation, not another name and value.
Which versions have it? Microsoft lists Excel for Microsoft 365 (Windows and Mac), Excel 2024 and Excel 2021 (Windows and Mac). Older perpetual versions such as Excel 2019 aren’t on that list, so if LET returns #NAME?, check which version of Excel you have. Excel for the web isn’t named on Microsoft’s LET page either, so test it there before you rely on it in a shared online file.
Turn a Calculation Into a Reusable Function With LAMBDA
LAMBDA goes one step further: it lets you build a custom function, then save it as a defined name so the whole workbook can call it. The syntax is =LAMBDA([parameter1, ...,] calculation), with up to 253 parameters. According to Microsoft’s LAMBDA function page, it’s available in Excel for Microsoft 365 and Excel 2024, but not Excel 2021.
- Go to Formulas > Name Manager and select New.
- Enter a name such as
AddTax, keep the scope at Workbook, and add a comment describing what it does. - In Refers to, enter
=LAMBDA(amount, amount*(1+TaxRate)). - Select OK. Now
=AddTax(B2*C2)works in any cell.
On a Mac, Microsoft says to use Formulas > Define Name for this step instead of Name Manager. Microsoft also recommends testing a LAMBDA in a cell before you name it, by adding the inputs in a second set of parentheses: =LAMBDA(number, number + 1)(1) returns 2.
If You Meant VBA Variables
Macros written in Visual Basic for Applications have true variables. You declare them with a Dim statement, such as Dim taxRate As Double. If you don’t give a type, the variable is a Variant. A variable declared inside a procedure only exists while that procedure runs, one declared at the top of a module is available to every procedure in the module, and the Static keyword lets a procedure variable keep its value between calls while the code is running. Microsoft’s VBA variables page covers declaring, typing and scope in more depth.
Macros are code, so read up on how to enable macros safely before you run one you didn’t write. For everyday calculations, names and LET do the job without any macro at all.
Fix #NAME? and Other Name Problems
Most problems with names come down to a handful of causes. Microsoft’s #NAME? error page lists the main ones.
- The name doesn’t exist. A formula that refers to a name not defined in the workbook shows
#NAME?. Define the name again in Name Manager, or change the formula. - The name is misspelled.
=B2*(1+TaxRat)fails. Use Formulas > Use in Formula or the autocomplete list so Excel inserts the exact spelling. - Text without quotation marks. If you meant a word as text, not a name, wrap it in quotes. Excel reads an unquoted word as a name and returns
#NAME?when no such name exists. - A sheet-level name used on the wrong sheet. Add the sheet prefix, such as
Sheet1!Target, or recreate the name with workbook scope. - LET or LAMBDA shows #NAME? Your version may not include the function. Confirm against the version lists above.
Excel Names and LET Questions
Is there a limit to how many names a workbook can have?
Microsoft documents a 255-character limit on the length of each name but doesn’t give a cap on how many names you can create.
Can I name a whole table instead of a range?
An Excel table already has a name (Table1, Table2 and so on), and it appears in Name Manager alongside defined names. Microsoft’s Name Manager page notes that changing a table name updates its uses in the workbook, just as with a defined name, so a well-named table can stand in for a named range.
A good first step is to pick the one value you type most often, give it a name with the Name Box, and swap it into your existing formulas. Once that feels natural, try rewriting your longest formula with LET.

Matthew Burleigh is the founder, head writer, and editor of Solve Your Tech, which he launched in 2012. He has written technology tutorials since 2008 and worked in IT since 2003, including small-business support, IT management, and consulting. He holds Bachelor’s and Master’s degrees in Computer Science from Arizona State University.
Matthew writes practical guides for Windows, iPhone and iPad, Microsoft Office, Outlook, Google apps, and Android. Solve Your Tech’s guides are checked step by step against the device or app maker’s own documentation and tested on his own devices whenever he has the hardware or software, with clear steps, sources, and update dates.
Read more about Matthew and how Solve Your Tech writes and updates its guides, or report a correction.