How to Use Named Ranges in Excel (and When a Table Works Better)

Mark Foeller
·
10 min read

Help Desk Geek is reader-supported. We may earn a commission when you buy through links on our site. Learn more.

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.

  1. Select the cell or range you want to name, such as the cell holding your tax rate.
  2. Click the Name Box. It’s the small field to the left of the formula bar that normally shows the cell address.
  3. Type the name, for example TaxRate.
  4. Press Enter.
Current Excel window with a single cell selected and the Name Box left of the formula bar showing the typed name "TaxRate"

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.

  1. Select the cell or range.
  2. Go to Formulas > Defined Names > Define Name. You can also open Name Manager on the same tab and click New.
  3. Type the name in the Name box.
  4. Pick a Scope. Workbook makes the name available everywhere. Picking a sheet name limits it to that sheet.
  5. Check the Refers to box. It’s filled in with your selection, but you can edit it.
  6. Add a comment if you want one, then click OK.
Excel New Name dialog showing the Name, Scope drop-down, Comment, and Refers to fields with OK and Cancel buttons

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.

  1. Select the range, including the labels.
  2. Go to Formulas > Defined Names > Create from Selection, or press Ctrl + Shift + F3 on Windows.
  3. Tick where the labels are: Top row, Left column, Bottom row, or Right column.
  4. 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, and R1C1 are 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.)

Sheet1 of the order form workbook showing line items, subtotal, the shipping method dropdown in cell B13, tax, and total
Sheet2 of the order form workbook showing the shipping methods and costs table and the tax rate cell

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 1 order form with the tax cell selected and the formula bar showing an A1-style cross-sheet reference

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.

Version 2 order form with a formula being typed and the Formula AutoComplete list showing a named range such as TaxRate

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.

  1. Select the shipping methods on Sheet2 and name them ShippingMethods using the Name Box.
  2. Go back to Sheet1 and select cell B13.
  3. Go to Data > Data Validation.
  4. On the Settings tab, set Allow to List.
  5. In Source, type =ShippingMethods.
  6. 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.

Excel Name Manager window listing named ranges with Name, Value, Refers To, Scope, and Comment columns, plus New, Edit, Delete, and Filter buttons

The list shows each name’s value, what it refers to, its scope, and any comment. From here you can:

  1. Select a name and click Edit to change what it refers to or update its comment.
  2. Select a name and click Delete to remove it.
  3. Click Filter to show only workbook-level names, sheet-level names, or names with errors.
  4. 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, if SalesTotal refers to that cell
  • Range("TaxRate").Value
  • ThisWorkbook.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 needBest option
A fixed assumption, like a tax rateWorkbook-level named range
A growing list or transaction logExcel Table with structured references
A sorted or filtered list generated by a formulaDynamic-array formula plus a # spill reference
Temporary names inside one formulaLET
Reusable custom calculationLAMBDA saved as a name
Dropdown or chart source that growsTable 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.

  1. Open Formulas > Name Manager.
  2. Find the name and look at the Scope column.
  3. 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.

  1. Open Name Manager.
  2. Click Filter > Names with Errors.
  3. 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.

  1. Click inside the list.
  2. Press Ctrl + T and confirm the headers.
  3. 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.

  1. Convert the source data to a Table, as in Fix #3.
  2. Update anything that used the old name to use the table column.
  3. 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.