Excel Named Ranges: The Complete Guide to Creating, Finding, and Fixing Them

What Are Named Ranges?

A named range is simply a human-readable label attached to a cell, range of cells, formula, or constant. Instead of referring to $B$7, you refer to TaxRate. Instead of $A$2:$A$1000, you refer to ProductList.

Named ranges have two scope levels:

• Workbook scope — The name works from any sheet. Just type =TaxRate anywhere.
• Sheet scope — The name only works on its own sheet, or requires the sheet name prefix (Sheet1!TaxRate) from other sheets.

Most names are workbook-scoped, which makes them globally accessible but also means name collisions are possible.

Creating Named Ranges

There are several ways to create named ranges:

The Name Box (fastest): Select your cells, click the Name Box (left of the formula bar where it shows the cell address), type your name, press Enter. Done. This creates a workbook-scoped name.

Define Name dialog (more control): Formulas tab → Define Name. Here you can set the scope, add a comment explaining the name's purpose, and see exactly what the name refers to.

Create from Selection (bulk creation): If you have data with headers, select the headers and data together, then Formulas → Create from Selection. Excel creates names using your header text.

Naming rules: Names must start with a letter or underscore. No spaces (use underscores instead). Can't look like cell references (you can't name something “A1” or “R1C1”). Max 255 characters.

The Name Manager

Formulas tab → Name Manager (or Ctrl+F3) opens the central hub for all named ranges in your workbook.

Here you can see every name, its scope, what it refers to, and its current value. You can edit names, delete them, or create new ones.

The filter button lets you show only names with errors, names scoped to a specific sheet, or names defined in tables.

The limitation: Name Manager shows names in a flat list. With 50+ names, finding the one you need means scrolling or using the search filter. With 200+ names, it becomes genuinely painful.

Common Named Range Problems

#REF! errors: The most common problem. A name refers to cells that no longer exist — usually because rows or columns were deleted, or a sheet was removed. The name still exists but points to nothing valid.

Orphaned names: Names that aren't used anywhere but weren't deleted. They clutter the Name Manager and make it harder to find what you need.

Scope confusion: You have both a workbook-scoped “Total” and a sheet-scoped “Total” on Sheet2. Which one does a formula use? The rules are complex and lead to unexpected results.

Hidden names: Some names are marked as hidden and don't appear in Name Manager's default view. These are often created by Excel itself or by add-ins. They exist but you can't see them without VBA.

Names from deleted external links: If your workbook once linked to another file, names from that file might still linger. They show paths like ‘C:\Old\Path\[File.xlsx]Sheet1'!$A$1 and are usually broken.

Finding and Fixing Named Range Issues

Find all #REF! names: In Name Manager, click Filter → Names with Errors. This shows only broken names so you can delete or fix them.

Finding unused names: Excel doesn't tell you which names are actually used in formulas. You'd need to search each name manually (Ctrl+F, search in Formulas) or use VBA to automate the check.

Changing scope: You can't directly change a name's scope. You have to delete the name and recreate it with the new scope. Yes, this is annoying.

Mass deletion: In Name Manager, you can select multiple names (Ctrl+click or Shift+click) and delete them together. Useful for cleanup.

Best Practices for Named Ranges

Use prefixes: Group related names with prefixes. cfg_TaxRate, cfg_FiscalYearStart for configuration. rng_Products, rng_Customers for data ranges. This makes names easier to find and sort.

Add comments: When creating names via Define Name, fill in the Comment field. Future you (and your colleagues) will thank you.

Prefer workbook scope: Unless you specifically need the same name to mean different things on different sheets, use workbook scope. It's simpler and avoids confusion.

Clean up regularly: When you delete data or restructure a workbook, check Name Manager for broken references. Don't let #REF! names accumulate.

Official Resources

• Define and use names — Microsoft named ranges guide
• Name Manager — managing named ranges