How-ToAugust 2026 · 9 min read

Excel Named Ranges: How to Create, Use, and Manage Them

Q: How do I create a named range in Excel?

Select the cells you want to name. Click the Name Box (the cell address field at the top-left of the spreadsheet, which shows the current cell address like "A1"). Type the name you want — no spaces, must start with a letter — and press Enter. The range is now named. To manage all named ranges, go to Formulas → Name Manager.

Q: What is the difference between a named range and an Excel Table?

A named range is a static label for a cell or range — it does not change size automatically unless you update it. An Excel Table (Ctrl+T) is a structured object that auto-expands when you add rows or columns, has built-in filters, and uses structured references (column names). For data that grows over time, Excel Tables are better. For lookup lists, constants, or fixed reference ranges, named ranges are simpler.

Q: Can named ranges span multiple sheets?

Named ranges defined at workbook scope (the default) can reference any sheet in the workbook, including multiple sheets with 3D references like =SUM(Jan:Dec!B5). Named ranges can also be scoped to a specific sheet — a sheet-scoped name only works on that sheet and can have the same name as a range on another sheet.

Named ranges replace =$A$2:$A$500 with =ProductList. Formulas become readable, references never break when rows are inserted, and you can reuse a range name across dozens of formulas.

Create in 3 Seconds

Select the range → click the Name Box (top-left corner, shows current cell address) → type your name → press Enter. Done. Now use that name in any formula in the workbook.

4 Ways to Create a Named Range

Name Box (fastest)

1.Select the cell or range you want to name
2.Click the Name Box at the top-left (shows the current cell address)
3.Type the name and press Enter

Best for quickly naming a single range. The name must start with a letter, contain no spaces, and not look like a cell address.

Define Name dialog

1.Select the range
2.Formulas → Define Name
3.Set name, scope (Workbook or specific sheet), and an optional comment
4.Click OK

Gives you control over the scope. Use sheet scope when you need the same name on different sheets for different ranges.

Create from Selection

1.Select your data including its row or column headers
2.Formulas → Create from Selection (or Ctrl+Shift+F3)
3.Tick which row/column contains the labels
4.Click OK

The fastest way to name multiple ranges at once. Excel creates one named range per header label.

Name Manager

1.Formulas → Name Manager
2.Click New to create a new name
3.Fill in Name, Scope, Comment, and the Refers To range
4.Click OK → Close

The best place to review, edit, and delete all named ranges. Use it to keep your workbook tidy.

Using Named Ranges in Formulas

Once a range is named, type the name anywhere you would normally type a cell reference:

=SUM(MonthlySales) — sums the named range
=XLOOKUP(A2, ProductID, ProductName, "Not found") — lookup using named source ranges
=COUNTIF(StatusList, "Complete") — count matching items in a named list
=VLOOKUP(B2, PriceTable, 2, FALSE) — lookup table reference that won't break if columns are inserted

When you type a named range in a formula, it appears in the autocomplete dropdown. You can also use F3 to open the Paste Name dialog and pick from all defined names.

Naming Rules

Rule	Valid	Invalid
Must start with a letter or underscore	SalesData, _TaxRate, Revenue2026	2026Revenue, 123List
No spaces	SalesData, Tax_Rate, MonthlyRevenue	Sales Data, Tax Rate
Cannot look like a cell address	MyRange, DataA1	A1, B2, R1C1
Max 255 characters	Any reasonable name	Names longer than 255 chars
Case-insensitive but case-preserving	SalesData and salesdata are the same range	Cannot have two names differing only by case

Dynamic Named Ranges (Auto-Expanding)

A static named range stays the same size — if you add rows to the data, the name still points to the original range. To create a named range that grows automatically with your data, use one of these approaches:

Option 1 — Use an Excel Table (Recommended). Format your data as a Table (Ctrl+T) and name it. Table columns like SalesData[Revenue] always refer to the entire column, including new rows. This is cleaner than formula-based dynamic ranges.

Option 2 — OFFSET + COUNTA formula. In the Name Manager, set "Refers To" to:

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

This always spans from A1 down to the last non-blank cell in column A. The drawback: OFFSET is volatile and recalculates on every change, which can slow large workbooks.

Option 3 — Entire column reference. Name the entire column: =Sheet1!$A:$A. Simple and always current, though it includes blank cells below the data. Works well when used inside XLOOKUP, MATCH, or COUNTIF which can handle empty cells gracefully.

Scope: Workbook vs Sheet

Named ranges have a scope — either the whole workbook or a specific sheet:

Workbook scope (default): the name works in any formula on any sheet. Use this for most names.
Sheet scope: the name only works on that specific sheet. Useful when you have the same logical name (like "Data") referring to different ranges on different sheets. Access it as SheetName!RangeName from other sheets.

You cannot have two workbook-scoped names with the same spelling. You CAN have the same name at sheet scope on multiple sheets, or the same name at both workbook and sheet scope (sheet scope wins on that sheet).

Editing and Deleting Named Ranges

Go to Formulas → Name Manager. Select a name to edit its range or delete it. When you delete a named range, any formula that uses it will return #NAME? — Excel will not automatically update those formulas. Always run Find & Replace to check for references before deleting.

Common Uses for Named Ranges

Dropdown list source: Data Validation → List → type the range name. The dropdown always shows the named list.
Constants: Name a single cell containing a tax rate or threshold. =Revenue * TaxRate is self-documenting.
Cross-sheet references: Instead of =Sheet3!$B$2:$B$50, name the range and use the name directly.
Chart data series: Point a chart series to a named range — the chart updates automatically when the range updates.

Frequently Asked Questions

How do I create a named range in Excel?

Select the range → click the Name Box (top-left, showing current cell address) → type the name → press Enter. Or use Formulas → Define Name for more control over scope.

What is the difference between a named range and an Excel Table?

A named range is a static label that does not auto-expand. An Excel Table auto-expands when rows are added, has built-in filters, and uses structured references. Use Tables for data that grows; named ranges for fixed lists, constants, and lookup sources.

Can named ranges span multiple sheets?

Yes — workbook-scoped named ranges can reference any sheet. You can also create 3D references like =SUM(Jan:Dec!B5) and name them. Sheet-scoped names only work on their assigned sheet.

Related Guides

Excel Tables Guide

The modern alternative to dynamic named ranges — Tables auto-expand and use structured references.

Read →

XLOOKUP Guide

XLOOKUP with named ranges gives you the most readable lookup formula possible.

Read →

Excel Dropdown Lists

Named ranges are the best source for Data Validation dropdown lists.

Read →

Excel Formulas Hub

Named ranges work in any formula — explore what else you can build.

Read →