How to Create a Named Range in Excel (and the Naming Rules)

A named range turns a cell reference like B2:B9 into a word you can read, like Sales or Leads. Formulas become easier to check, they're harder to break, and you can jump to the cells from anywhere in the workbook. It takes about ten seconds to set up, and the only things that trip people up are Excel's naming rules.

How to create a named range with the Name box

1. Select the cells you want to name.
2. Click the Name box, the small box to the left of the formula bar that normally shows the cell address.
3. Type your name, for example Leads_Q1.
4. Press Enter. If you click away without pressing Enter, the name isn't saved.

How to create a named range with Define Name

1. Select the cells.
2. Go to the Formulas tab and click Define Name.
3. Type the name.
4. Choose the Scope: the whole workbook, or one sheet.
5. Add a comment if you want to remind yourself what it's for.
6. Check the Refers to box shows the right cells, then click OK.

Define Name is the better choice when you want a name that only works on one sheet, or a note explaining it, because the Name box can't do either.

Excel's naming rules

Rule	Allowed	Not allowed
First character	A letter, an underscore or a backslash	A number
Spaces	Underscores or full stops instead	Spaces
Looks like a cell	Names like Region_A	Names like A1 or Q1
Single letters	Most letters	C, c, R or r on their own
Length	Up to 255 characters	Longer

The cell-reference rule is the one that surprises people. Q1 looks like a sensible name for first-quarter data, but it's also the address of a cell, so Excel refuses it. Leads_Q1 works.

The catch: names aren't case-sensitive

Excel treats Sales, sales and SALES as the same name. Microsoft's warning is that if you name one range Sales and then name a different range SALES in the same workbook, the second replaces the first. Formulas that pointed at your first range now quietly point somewhere else. Use distinct names, not different capitalisation.

How to use a named range in a formula

1. Start typing a formula, for example =SUM(.
2. Type the first letter of your name and pick it from the list that pops up.
3. Close the bracket and press Enter.

Or choose Formulas, then Use in Formula, and pick the name from the list. Named ranges work in any formula, in data validation drop-down lists and as chart sources.

Workbook scope or sheet scope?

A name with workbook scope works on every sheet in the file. A name with sheet scope only works on its own sheet unless you put the sheet name in front, such as Sheet2!Leads. Sheet scope is useful when you've got the same layout on several sheets, like one for each month, and want the same name on each without them clashing.

How to edit or delete a named range

1. Go to Formulas and click Name Manager.
2. Select the name.
3. Click Edit to rename it or change the cells it refers to, or Delete to remove it.

Name Manager also shows names with errors, which is the quickest way to tidy up a workbook you've inherited. Use the filter to show only names with errors.

Fixing a #NAME? error

A #NAME? error usually means a formula uses a name that doesn't exist or is spelt differently. Check the spelling in Name Manager, define the name if it's missing, and insert it with Use in Formula next time so typos can't creep in.

Named ranges that grow with your data

A plain named range covers a fixed block of cells. Add a new row below it and the name doesn't stretch to include it, which is how totals quietly stop being right. The simplest fix is to turn your data into an Excel table first, with Insert, then Table. Tables expand as you add rows, and Excel gives each table its own name that you can use in formulas just like a named range, and you'll find it listed in Name Manager too. For a list that lives outside a table, edit the name in Name Manager whenever the list grows, or make the range a little larger than you need.

Ideas for using named ranges

• Constants: name a cell holding a rate or target, so every formula uses the same value.
• Drop-down lists: name a list of options and use it in data validation.
• Navigation: pick a name from the Name box to jump straight to those cells.
• Dashboards: name the source data for charts so they're easy to update.

If you're building reports from your spreadsheets, my guide to turning data into dashboards with AI shows another way to present them.

My verdict

Select the cells, type a name in the Name box and press Enter: that's all a named range needs. Use Define Name when you want sheet scope or a comment, keep names free of spaces and cell-like codes, and never rely on capitals to tell two names apart. Check Name Manager now and then for broken names. For more on organising business data, see my list of customer database software.

Frequently asked questions

How do I create a named range in Excel quickly?

Select the cells, type a name in the Name box to the left of the formula bar, and press Enter.

Why won't Excel accept my range name?

It may contain a space, start with a number, look like a cell reference or be a single C or R.

Are Excel range names case-sensitive?

No. Sales and SALES are the same name, and the second replaces the first.

How do I see all named ranges in Excel?

Go to Formulas and open Name Manager.

Can a named range work on only one sheet?

Yes. Use Define Name and set the Scope to that sheet.

What does #NAME? mean in Excel?

The formula uses a name that isn't defined or is misspelt.