Microsoft Excel Named Ranges: How They Work and Help

Microsoft Excel named ranges are labels you assign to cells or ranges so you can refer to them by name instead of by cell address. In Excel for Microsoft 365 on Windows, create one by selecting cells, typing a name in the Name Box beside the formula bar, and pressing Enter; you can then use that name in formulas, such as a total that refers to Revenue rather than B2:B4.

What a named range is

A named range is a defined name that points to a cell, a group of cells, or—in some cases—a formula or constant. For example, a name called Revenue could refer to three monthly sales figures. A formula using Revenue is easier to interpret than one containing only cell addresses, particularly when a workbook has many sheets or is used by other people.

Despite the familiar term “named range,” a name does not have to refer to a range of multiple cells. It can refer to one cell, an entire row or column, or a calculation. Names can be used in formulas, charts, data validation, and other Excel features that accept a cell reference or formula. Excel also has table names and column names; those are related, but they are not the same thing as a manually defined named range.

How to create a named range

For a straightforward range on a worksheet, use the Name Box. Select the cells, click the Name Box to the left of the formula bar, type a valid name, and press Enter. The selected cells now have that name. If you click the Name Box’s drop-down arrow later, you can choose a defined name to navigate to its reference.

For more control, use the ribbon. Select the range, then choose Formulas > Define Name. In the New Name dialog, enter a name, check the Refers to field, and choose the intended scope. Select OK to save it. The Refers to field should point to the correct cells; it often contains an absolute reference, such as =Sheet1!$B$2:$B$4.

When the data has labels in a first row or column, Excel can create names from those labels. Select the labeled cells and the adjacent data, then choose Formulas > Create from Selection. In the dialog, indicate where the labels are—for example, Top row—and confirm. Review the resulting names, especially if the headings contain punctuation or similar wording, because Excel adjusts headings to make them usable as names.

For example, suppose B2:B4 contains monthly revenue of 1,200, 1,500, and 1,300. Give B2:B4 the name Revenue. With that cell layout and name in place, enter =SUM(Revenue) in another cell. The result is 4,000, the same total as summing the three values by cell address.

Workbook scope and worksheet scope

Every defined name has a scope that determines where Excel recognizes it. Workbook scope makes the name available throughout the workbook. Worksheet scope limits it to one particular sheet. The scope is set when you create the name in the New Name dialog, and it cannot be changed for an existing name; to change it, delete and recreate the name, taking care to update formulas that use it.

Workbook scope is generally the simpler choice for a value or range that should be shared across sheets. Worksheet scope can be useful when each sheet has a local name for a different purpose—for example, a separate name called Target on each regional sheet. Local names can coexist with a workbook-level name of the same spelling. In a worksheet, Excel uses the local name in preference to the workbook-level one. That convenience can also make formulas confusing if readers do not know which scope applies.

To inspect a name’s scope, choose Formulas > Name Manager and look at the Scope column. A worksheet-scoped name may need a sheet qualifier when referenced from elsewhere. If a workbook-level name will work, using it consistently is often easier to maintain than relying on several local names with identical names.

Rules for naming ranges

Excel names have formatting rules. A name must start with a letter, an underscore, or a backslash; after that, it can contain letters, numbers, periods, and underscores. It cannot contain spaces, and it cannot be written in a way Excel interprets as a cell reference. Names are not case-sensitive, so Revenue and REVENUE refer to the same name.

Choose names that describe the contents or purpose, such as AnnualSales, InputRate, or ProductList. Avoid short, ambiguous labels that could be mistaken for ordinary words or formulas. Because spaces are not allowed, use a consistent style such as underscores or internal capital letters. Clear names matter most in shared workbooks, where someone else may need to understand a formula without tracing every cell reference.

Use names that describe the data or calculation, not a temporary position such as LeftSide.
Check spelling and scope before using a name in several formulas.
Keep names distinct from table names and other workbook terminology.
Use absolute references for fixed ranges unless you intentionally need a relative reference.

Using names in formulas

Once defined, a name can replace a cell address in many formulas. With B2:B4 containing 1,200, 1,500, and 1,300 and the range named Revenue, the formula =SUM(Revenue) returns 4,000. If a single cell C1 contains a tax rate of 8% and is named TaxRate, and B2 contains a taxable amount of 100, then =B2*TaxRate in another cell returns 8. These examples assume the named range and cell layout described immediately before each formula.

Names can make complex formulas more readable, but they do not change the underlying calculation. A name can also be edited to point somewhere else, which means formulas using that name may then refer to different data without changing their displayed text. For that reason, review definitions after moving, adding, or reorganizing workbook content.

Excel can also define a name that represents a formula rather than a block of cells. This is sometimes called a named formula. For example, a name can store a repeated calculation so that formulas can refer to the name instead of repeating the calculation. Named formulas are useful in specialized models, but document what they calculate and verify their references, especially if they depend on relative cell addresses.

Managing names and fixing common problems

Choose Formulas > Name Manager to review, edit, create, or delete names. The manager lists each name, its value or reference, its scope, and any comment. Editing the Refers to field can repair a name whose range has moved. Deleting a name removes its definition, not the cells it pointed to; formulas that still use it may display a #NAME? error.

A #NAME? error often means Excel cannot resolve a name in the formula. Check for a typo, a deleted definition, or a name that exists only on another worksheet. Also confirm that the name was entered as intended and is not being confused with a text label. If a formula returns an unexpected value rather than an error, inspect the name’s Refers to field: it may point to the wrong cells, include an unintended blank cell, or have a scope that causes a different definition to be used.

Names are not always adjusted as a user might expect when a workbook is reorganized. Excel generally updates references when cells are inserted or deleted within a referenced area, but moving or copying formulas and ranges can still produce confusing results. Check Name Manager after major structural changes. Avoid defining a range with a relative reference unless that behavior is deliberate: relative references in names can depend on the active cell used when the name was created and may behave unexpectedly when called from elsewhere.

When a name has been deleted, repair or replace the formulas that used it. When a name is merely incorrect, edit its reference rather than creating several near-duplicate names. In a workbook maintained by a team, a short note in the Name Manager’s Comment field can explain why a name exists or which sheet owns the relevant inputs.

Named ranges compared with Excel tables

Named ranges work well for fixed inputs, assumptions, and clearly bounded data areas. Excel tables are often better for lists that grow over time, such as transactions or inventory. To make a table, select the data and choose Insert > Table. A table expands as rows are added, and formulas can refer to its columns by heading. For example, a formula can use a table’s Sales column rather than a fixed address that may not include newly added rows.

A named range can still be useful alongside a table. You might name one assumption cell TaxRate while keeping a changing list of orders in a table. This separates a single input from a growing dataset. Avoid naming a table column range manually if the table’s structured references already provide a clear and expanding way to refer to it.

The choice depends on how the data changes. Use a named range when the cells represent a stable, meaningful unit or a reusable input. Consider a table when rows are regularly added, filtered, sorted, or analyzed as records. Neither approach automatically makes a workbook correct: verify that formulas include the intended data and that names are understandable to the people who will maintain the file.

Excel for Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also supports defined names and a Name Manager; ribbon labels and dialog layouts can differ slightly, so look under the Formulas tab for name creation and management. The Name Box is also available for assigning a name to a selected range.

Excel for the web supports using names in formulas, and its name-management controls may differ from the desktop dialogs as Microsoft updates the web app. If a workbook requires detailed scope changes or name cleanup and the web interface does not expose the needed control, open it in the Excel desktop app. Names are stored in the workbook, so a properly saved workbook can be opened in supported desktop or web versions, though availability of particular editing controls can vary.

Frequently Asked Questions

How do I see all named ranges in Excel?
Choose Formulas > Name Manager. It lists defined names and their references and scopes. You can also open the Name Box drop-down to navigate to a name, but Name Manager is more useful for inspecting and editing definitions.

Can a named range include spaces?
No. Use a name such as Monthly_Sales or MonthlySales instead. Spaces are not allowed in defined names, although worksheet headings and table column headings can contain spaces.

What is the difference between a named range and a table name?
A named range is a defined label for a cell reference or formula. A table is a structured data object with named columns and built-in support for expanding as records are added. Table formulas use structured references; named ranges use the name you define.

Can I rename a named range?
In Name Manager, select the name and choose Edit to change its spelling or reference. If the name itself cannot be edited in the dialog, create a new name and update formulas that use the old one before deleting it. Check the workbook afterward for #NAME? errors.

Why does Excel show #NAME? after I use a name?
Excel may not recognize the spelling, the name may have been deleted, or its worksheet scope may make it unavailable in the formula’s location. Check the exact name and its scope in Name Manager, then correct the formula or definition.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.