Excel Named Ranges: How They Work and When to Use Them

How-to basics · Updated November 19, 2026

Excel named ranges are labels assigned to a cell, a group of cells, or a formula, so you can refer to something like TaxRate instead of B2. In Microsoft Excel for Microsoft 365 on Windows, create or manage them from Formulas > Define Name or Formulas > Name Manager, then use the name in formulas just as you would a cell reference.

What a named range is

A named range is a defined name that points to a cell or range, such as Sheet1!$B$2:$B$13. Despite the phrase “named range,” a name can also refer to a single cell, a constant value, or a formula. For example, a name might identify a tax-rate cell, the monthly sales column, or a calculation used in several places.

Names are saved with the workbook. A workbook-level name is generally available throughout the file, while a worksheet-level name is limited to the sheet where it was created. Names are not just visual labels: Excel can evaluate them inside formulas, data validation lists, and other features that accept references or formulas.

For instance, a formula that uses AnnualSales is easier to interpret than one that contains several unexplained cell addresses. The name does not change the underlying values or make a calculation inherently more accurate. It provides a clearer way to identify and reuse the relevant cells.

Why use named ranges

The main benefit is readability. A formula such as =Revenue-Expenses communicates its purpose more clearly than a formula made from cell coordinates. This is especially useful in workbooks that are shared, reviewed, or maintained over time. A reader can also inspect the name’s definition to find the cells behind it.

Names can make formulas easier to maintain. If a named range points to the correct input cell, a formula using that name does not need to be rewritten simply because someone has to understand what the input represents. You can also use one name in multiple formulas, reducing the chance of typing different references for the same input.

They are most helpful when the label adds information a cell address cannot. Good candidates include key assumptions, calculation inputs, and clearly defined data columns. Naming every cell in a workbook, on the other hand, can create clutter and make names harder to find. A consistent, selective naming approach is usually more useful.

How to create a named range

In Excel for Microsoft 365 on Windows, you can create a name for a cell or range using the ribbon:

  1. Select the cell or range you want the name to represent.
  2. Choose Formulas > Define Name.
  3. Enter a clear name in the Name box. Check the Scope setting, and add a description if useful.
  4. Check the Refers to box to confirm that it points to the intended sheet and cells, then select OK.

For a quick name, select a cell or range, click the Name Box to the left of the formula bar, type a valid name, and press Enter. The Name Box can also take you to a name already in the workbook: select its drop-down list and choose the name. On Excel for Mac, use the Formulas tab’s name tools; ribbon labels can vary slightly by release. The steps above describe desktop Excel, not Excel for the web.

You can also create names from column or row headings. Select a range that includes the labels and the adjacent cells, then choose Formulas > Create from Selection and indicate where the labels are. Check the resulting names in Name Manager, particularly if the selection contains blank cells or duplicate headings.

Workbook scope and worksheet scope

When you create a name, its scope determines where Excel recognizes it. Workbook scope makes the name available across worksheets. Worksheet scope restricts it to one sheet, which is useful when a name is meaningful only in a particular worksheet or when different sheets need to use the same short label for different ranges.

For example, two worksheets could each have a worksheet-level name called Target, with each name referring to a different cell on its own sheet. A formula on a worksheet uses a local name from that sheet when one exists. To refer explicitly to a sheet-level name from another sheet, qualify it with the sheet name, such as Budget!Target. If the sheet name contains spaces, Excel encloses it in single quotes, as in 'North Region'!Target.

Choose the scope before creating a name. Excel does not let you simply switch an existing name from worksheet scope to workbook scope in Name Manager; if you need a different scope, create a new name with the intended scope and update formulas as needed. Be cautious when a local name and a workbook name share a label, because the local name can take precedence on its sheet.

Using names in formulas

Once a name exists, use it in formulas as a reference. Suppose the workbook has a name called Revenue that refers to a cell containing 125,000, and a name called Expenses that refers to a cell containing 83,000. With those two named cells defined, enter this formula in the cell where you want the difference: =Revenue-Expenses. It returns 42,000. The example assumes both names refer to single numeric cells in the same workbook.

A name can also represent a range. With a name called MonthlySales referring to cells containing 120, 160, and 120, enter =SUM(MonthlySales) in a result cell. It returns 400. The name must refer to the intended cells; a name does not automatically include new rows just because they are added nearby.

Names can be used in other features that accept a range or formula, including the source for a data validation list. If a formula is unclear, select the name in Name Manager to inspect its Refers to definition. When entering formulas, Excel may show recognized names in its autocomplete suggestions. A name can improve readability, but it does not replace the need to check that the definition points to the correct data.

Fixed ranges, expanding data, and Excel tables

A name that refers to a fixed address stays tied to that address. If MonthlySales refers to A2:A13, entering a new value in A14 does not necessarily make A14 part of that name. This is a common reason a total appears to miss newly added records.

For lists that grow, an Excel table is often a clearer option than a manually maintained named range. Select the data and choose Insert > Table, or press Ctrl+T on Windows, then confirm whether the data has headers. Tables expand as rows are added and offer structured references based on table and column names.

For example, suppose a table named Sales has a column called Amount containing 400, 500, and 450. In a result cell, enter =SUM(Sales[Amount]). It returns 1,350, and the structured reference includes new table rows as the table grows. This is often easier to maintain than updating a fixed range name by hand. Tables and named ranges can also work together: a defined name can refer to a table column when a short reusable label is useful.

Reviewing, editing, and deleting names

Choose Formulas > Name Manager to see defined names, their values, their scope, and the cells or formulas they refer to. On Windows, Ctrl+F3 opens Name Manager. The dialog is the best place to review a workbook with many names because it brings their definitions together in one list.

Select a name and choose Edit to change its label, comment, or Refers to definition. Use Delete only after checking whether formulas still depend on it: removing a name does not remove the cells it referred to, but formulas that used that name may show a #NAME? error. Excel does not provide a simple guarantee that deleting a name is harmless throughout a complex workbook, so inspect important formulas and consider saving a copy before substantial cleanup.

When the Name Box is used to navigate, Excel selects the cells a name refers to. This is a useful quick check for whether a definition points to the expected location. For a formula-based name, review its definition in Name Manager; the name may not correspond to a single visible block of cells.

Naming rules and common problems

Excel names have rules that ordinary labels do not. A name must start with a letter, underscore, or backslash; it cannot contain spaces; and it cannot look like a cell reference such as A1. Names are not case-sensitive, so Excel treats TaxRate and taxrate as the same name. Use underscores or clear word combinations instead of spaces, such as Tax_Rate or QuarterlySales.

  • A formula returns an unexpected result: Check the name’s Refers to entry, scope, and any worksheet-level name with the same label.
  • A new row is missing from a total: Check whether the name uses a fixed range. Extend it or use a table that expands with new rows.
  • A formula shows #NAME?: Confirm that the name exists, is spelled correctly, and has not been deleted. Also check whether the name is available in the formula’s scope.
  • A name cannot be created: Remove spaces, avoid cell-reference-like labels, and check for an existing name that conflicts.

Names are also case-insensitive, so capitalization cannot distinguish two separate names. Descriptive labels help prevent accidental overlap with other names and make formulas easier to review. In a shared workbook, it is worth agreeing on a simple naming style so that similar concepts are labeled consistently.

Frequently Asked Questions

Are named ranges the same as Excel tables?
No. A named range is a label for a reference or formula, while a table is a structured data feature with named columns and rows that can expand as data is added. A name can point to a table column, but the two features are not interchangeable.

Do named ranges update when I insert rows?
It depends on how the name is defined and where rows are inserted. A fixed reference may not include data entered outside its bounds. Check the Refers to definition after structural changes, or use a table for a growing list.

Can a named range refer to a formula?
Yes. In Name Manager, a name’s Refers to entry can contain a formula rather than a direct cell address. Such a name can make a repeated calculation easier to reuse, but check the definition and scope if its result is unexpected.

How do I find all named ranges in a workbook?
Choose Formulas > Name Manager in desktop Excel. It lists the names Excel has defined, with their scope and references. Selecting a name there lets you inspect or edit its definition.

Can I rename a named range?
Yes. Open Name Manager, select the name, and choose Edit. If you are changing its scope rather than its label, create a new name with the desired scope and update formulas that need to use it.

Related

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.