Your Excel formula reads =B13*Sheet2!$C$11, and nobody remembers what’s in C11. A named range lets you write =Subtotal*TaxRate instead, and it takes about 10 seconds to set up.
Create a named range
There are three ways to create a name. Each works in current Microsoft 365 Excel and Excel 2024 on Windows, and the ribbon commands are the same in Excel for Mac.
Type a name in the Name Box
This is the fastest method. It creates a name that works anywhere in the workbook.
- Select the cell or range you want to name, such as the cell holding your tax rate.
- Click the Name Box. It’s the small field to the left of the formula bar that normally shows the cell address.
- Type the name, for example
TaxRate. - Press
Enter.
Click any other cell, then open the Name Box drop-down. TaxRate should be in the list, and picking it jumps straight to that cell.
Use Define Name to choose the scope
Use this method when you want a name that only works on one sheet, or when you want to add a comment explaining what the name is for.
- Select the cell or range.
- Go to Formulas > Defined Names > Define Name. You can also open Name Manager on the same tab and click New.
- Type the name in the Name box.
- Pick a Scope. Workbook makes the name available everywhere. Picking a sheet name limits it to that sheet.
- Check the Refers to box. It’s filled in with your selection, but you can edit it.
- Add a comment if you want one, then click OK.
The Refers to box isn’t limited to cell ranges. A name can hold a constant like =0.23 or a formula like =Sheet1!$B$2*Sheet1!$B$3, which is handy for assumptions that never live in a cell.
Create names from row or column labels
If your data already has headings, Excel can turn them into names in one go.
- Select the range, including the labels.
- Go to Formulas > Defined Names > Create from Selection, or press
Ctrl + Shift + F3on Windows. - Tick where the labels are: Top row, Left column, Bottom row, or Right column.
- Click OK.
Microsoft’s guide to creating names from a selection covers the same steps. On a Mac, use the ribbon command, since keyboard shortcuts vary between Excel and macOS versions.
Naming rules that trip people up
Excel rejects a name or quietly behaves oddly if you break these rules:
- No spaces. Use underscores or periods instead, like
Tax_Rate. - Start with a letter (or an underscore), never a number.
- Don’t mimic a cell reference.
Dog26,A10, andR1C1are all invalid because Excel reads them as addresses. - Names can be up to 255 characters, but short ones are easier to type.
Names aren’t case-sensitive. Writing them as joined capitalized words, like TaxRate or December2018Sales, makes them easier to read anyway.
Use named ranges in formulas
A simple order form shows the difference well. Our example file has a fillable order form with a shipping-method dropdown on Sheet1, plus a second sheet holding the shipping cost table and the tax rate. (This workbook is provided as an illustrative example; formula behavior may vary by Excel version and workbook setup.)
Version 1 of the form uses plain A1-style references. Click the tax cell and the formula bar shows something like =D20*Sheet2!$C$11, which tells you nothing without flipping sheets.
Version 2 uses names, so the same formula reads like =Subtotal*TaxRate. Typing is easier too. Start typing a name in a formula and Excel shows a pick list of matching names and functions. Double-click the one you want to insert it.
Names work anywhere a range does:
=SUM(Sales)=AVERAGE(Sales)=COUNTIF(Sales,">100")
Put quotation marks around text values, never around a name. "TaxRate" is just the word TaxRate. Microsoft’s page on names in formulas has more examples.
Point a dropdown at a list on another sheet
This is where names earn their keep. In the order form, the shipping dropdown in cell B13 on Sheet1 feeds a lookup against the cost table on Sheet2.
If you type the dropdown choices by hand, you’ve entered every shipping method twice. One typo in either copy and the shipping formula returns #N/A whenever someone picks that option. Naming the Sheet2 list ShippingMethods and pointing the dropdown at it fixes both problems.
- Select the shipping methods on Sheet2 and name them
ShippingMethodsusing the Name Box. - Go back to Sheet1 and select cell B13.
- Go to Data > Data Validation.
- On the Settings tab, set Allow to List.
- In Source, type
=ShippingMethods. - Click OK.
The dropdown now reads from the same cells the lookup uses, so the two can’t drift apart. So far so good. One catch: a fixed name doesn’t grow when you add a new shipping method below the list. The Table option further down handles that.
Manage names in the Name Manager
Every name in the workbook lives in one place. Go to Formulas > Defined Names > Name Manager.
The list shows each name’s value, what it refers to, its scope, and any comment. From here you can:
- Select a name and click Edit to change what it refers to or update its comment.
- Select a name and click Delete to remove it.
- Click Filter to show only workbook-level names, sheet-level names, or names with errors.
- Click Close when you’re done.
Microsoft’s Name Manager documentation lists every filter option.
How scope works
Each name has a scope. Workbook scope means any sheet can use it. Sheet scope means only that sheet sees it.
Sheet scope lets you reuse a name. Say you have a sales file with a sheet for each month, and each sheet has a cell named MonthlySales scoped to that sheet. Then =ROUND(MonthlySales,0) returns February’s sales on the February sheet and March’s on the March sheet.
That’s clever, but it gets confusing fast. In a workbook with dozens of names, I’d put the sheet name in the name itself, like January_MonthlySales or Order_Date. Every name stays unique and can stay workbook-scoped.
Two scope habits will save you grief:
- Typing a name in the Name Box creates a workbook-level name. That’s usually what you want, but if scope matters — for example, you need the same name on multiple sheets — use Define Name instead, where you can set the scope explicitly.
- If a name ends up with the wrong scope, delete it in Name Manager and recreate it with Define Name. That’s the dependable route.
Use named ranges in VBA
If you write macros, names make your code much easier to read. Instead of ThisWorkbook.Sheets(1).Cells(2,3), you can use any of these:
[SalesTotal], the bracket shortcut, ifSalesTotalrefers to that cellRange("TaxRate").ValueThisWorkbook.Names("TaxRate").RefersToRange.Value
RefersToRange only works when the name points at cells. If the name holds a constant like =0.23 or a formula, that line throws an error.
For data stored in a Table, use the table object instead: ThisWorkbook.Worksheets("Data").ListObjects("SalesTable").DataBodyRange.
When a Table, LET, or LAMBDA is the better choice
Named ranges are great for fixed values and small reference lists. For data that grows, current Excel has tools that do the job with less upkeep. Dynamic arrays, LET, and LAMBDA need a current Microsoft 365 subscription or Excel 2024, so check what the people opening your file are running.
| What you need | Best option |
|---|---|
| A fixed assumption, like a tax rate | Workbook-level named range |
| A growing list or transaction log | Excel Table with structured references |
| A sorted or filtered list generated by a formula | Dynamic-array formula plus a # spill reference |
| Temporary names inside one formula | LET |
| Reusable custom calculation | LAMBDA saved as a name |
| Dropdown or chart source that grows | Table column or spill reference |
Excel Tables
Select your data and press Ctrl + T (or Insert > Table), confirm the headers, then give it a name under Table Design > Table Name. Formulas then read like =SUM(SalesTable[Revenue]), and the reference expands as you add rows. Microsoft recommends structured references where possible for exactly that reason.
For the order form, you can turn the Sheet2 shipping list into a Table so that new methods are automatically included. However, the Data Validation Source box does not accept structured references like =ShippingTable[Method] directly. The supported approach is to define a workbook-level name — for example, ShippingMethods — that refers to the Table column (=ShippingTable[Method]), and then use =ShippingMethods as the dropdown source. When you add a new row to the Table, the name updates automatically and the dropdown reflects the change without any further edits.
Spill references
If D2 contains =SORT(UNIQUE(A2:A100)), then =D2# refers to the whole result, however long it gets. You can also define a name like DepartmentList that refers to =Sheet1!$D$2# and use that as a dropdown source.
LET and LAMBDA
LET names values inside a single formula, so they don’t clutter Name Manager:
=LET(revenue, B2:B100, costs, C2:C100, SUM(revenue)-SUM(costs))
LAMBDA turns a formula into your own function. Define a name AddTax that refers to =LAMBDA(price, rate, price*(1+rate)), and you can write =AddTax(A2,TaxRate) anywhere in the workbook.
Fix common named range problems
Fix #1: Check the scope when a name seems missing
A formula returns #NAME? or the name isn’t in the pick list on one sheet.
- Open Formulas > Name Manager.
- Find the name and look at the Scope column.
- If it shows a sheet name, the name only works on that sheet. Recreate it with Workbook scope using Define Name.
Also check for duplicates. A workbook name and a sheet name can share the same text, and the sheet-level one wins on its own sheet.
Fix #2: Repair names that show #REF!
Deleting rows, columns, or a whole sheet can leave a name pointing at nothing.
- Open Name Manager.
- Click Filter > Names with Errors.
- Select each broken name and click Edit to point it at the right cells, or Delete if it’s obsolete.
Check the Refers to column for file paths too. Copying sheets between workbooks can leave names linked to the other file, and you don’t want those in something you’re emailing out.
Fix #3: Make a list that grows automatically
A name like =Sheet1!$A$2:$A$100 won’t include row 101 when you add data there.
- Click inside the list.
- Press
Ctrl + Tand confirm the headers. - Point your formulas, dropdowns, and charts at the table column instead of the fixed name.
New rows should now appear in every place that uses the list.
Fix #4: Replace slow OFFSET-based names
Older workbooks often build a growing range with =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). OFFSET is volatile, so it recalculates constantly and can drag down large files. COUNTA also miscounts when the column has blank cells or notes below the data.
- Convert the source data to a Table, as in Fix #3.
- Update anything that used the old name to use the table column.
- Delete the old name in Name Manager.
If you need a defined name, Microsoft’s performance guidance describes a non-volatile INDEX-based alternative.
Conclusion
Naming your fixed values, like tax rates and shipping lists, is the single change that makes the biggest difference to a messy workbook, and it’s worth trying on your next file. For anything that grows, a Table is the better option, so I’d recommend named ranges with that one condition. If Name Manager fills up with #REF! errors or names like Range2 nobody can explain, the workbook needs a cleanup more than more names.