Add a Named Range in Excel: Steps, Uses, and Helpful Tips

How-to basics · Updated November 18, 2026

To add a named range in Excel for Microsoft 365 on Windows, select the cell or range, click the Name Box to the left of the formula bar, type a valid name, and press Enter. You can also create one through Formulas > Define Name, which lets you choose its scope and confirm exactly what it refers to.

What a named range does

A named range gives a cell, a group of cells, or another valid reference a readable label. Instead of remembering that a list of prices is in cells B2:B20, you might name those cells Prices. You can then use the name in formulas, move to the range quickly, or refer to it from other parts of a workbook.

Names are stored in the workbook, not just displayed as labels on the worksheet. Creating a name does not change the cells, their values, or their formatting. It creates a reference that Excel can resolve when you use the name. A defined name can also represent a constant or a formula, though most people begin by naming a cell or range.

Named ranges are especially useful in workbooks with many sheets, long formulas, or repeated inputs such as tax rates, product lists, and monthly totals. They are optional: ordinary cell references continue to work, and a name is most helpful when it makes a workbook easier to understand or maintain.

Add a range with the Name Box

The Name Box is the compact field at the left end of the formula bar. It normally shows the active cell address, such as A1. To create a name for a simple range:

  1. Select the cell or cells you want the name to refer to. For a multi-cell range, select the entire range before creating the name.
  2. Click the Name Box. Do not type the name into a worksheet cell or into the formula bar.
  3. Type a valid name, such as Prices, and press Enter. Clicking elsewhere instead of pressing Enter may leave the entry uncommitted.
  4. Check the Name Box: when you select another cell, you can type the new name there and press Enter to jump back to the named range.

This is the quickest method when you are happy with Excel’s default workbook-wide scope. If you select B2:B20 first and name it Prices, the name refers to that selected block. A name created this way is generally workbook-scoped; use the Define Name dialog when you need to control scope or inspect the reference.

Create a name with Define Name

For more control, select the target cells and choose Formulas > Define Name. In the New Name dialog, enter a name, check the Scope field, and review the Refers to field. The dialog usually fills Refers to with the selected range. Choose OK to save the name. You can also open the dialog without first selecting a range and enter a reference manually.

For example, suppose Sheet1 contains product prices in B2:B4 and you want to call that range UnitPrices. In the Refers to field, enter =Sheet1!$B$2:$B$4. The dollar signs make the cell addresses absolute, so they do not shift if a formula containing the reference is copied. If the worksheet name contains spaces, Excel surrounds it with apostrophes; for a sheet named Price List, the reference would be ='Price List'!$B$2:$B$4.

The dialog’s Scope setting determines where the name can be used. Workbook is the usual choice when the name should work from any worksheet. You can instead select a particular worksheet to make a sheet-level name. Before saving, confirm the Refers to entry points at the intended cells; an incorrect reference can create a valid name that nevertheless returns the wrong data.

Choose a valid name

Excel applies naming rules so it can distinguish defined names from cell addresses and other syntax. A name must start with a letter, an underscore, or a backslash; the rest can include letters, numbers, periods, and underscores. Spaces are not allowed, so use a style such as Quarter_Total or QuarterTotal. Names can be up to 255 characters and are not case-sensitive: Excel treats Revenue and revenue as the same name.

A name cannot look like a cell reference. For example, Q1 is not a suitable name because it can be read as a cell address. Names also should not contain spaces or punctuation such as a hyphen. If Excel rejects a name, simplify it, begin with a letter, and replace spaces with underscores. A clear, specific name is easier to recognize later than a short label such as Data or Range.

Use a named range in a formula

Once a name exists, you can use it in formulas just as you would use a cell reference. This can make the purpose of a calculation more obvious to someone reviewing the workbook. For a concrete example, suppose UnitPrices refers to the three values 12, 18, and 20 in Sheet1!B2:B4. In any worksheet cell, enter =SUM(UnitPrices). Excel returns 50, the total of those three values.

Names can also help when a value is reused in several calculations. If you name a cell containing a discount rate DiscountRate, formulas can refer to DiscountRate rather than repeating that cell’s address. When a named range is used in a formula, the result still depends on the referenced cells and the function; naming a range does not itself calculate or alter the data.

You can use a name to navigate as well: open the Name Box’s drop-down list, choose the name, and Excel selects the cells it refers to. Names are also useful in places where Excel accepts a range reference, such as a chart’s data selection or a data-validation list. If a dialog requires a particular kind of reference, check that the named range points to the right shape and contents—for instance, a one-column list for a drop-down list.

Set scope: workbook or worksheet

Workbook scope makes a name available throughout the workbook and is the simplest choice for shared inputs or ranges used on several sheets. Sheet scope limits a name to one worksheet. Sheet-level names are useful when the same label means something different on different worksheets, such as a name like MonthlyTotal on each department’s sheet.

Excel can contain a workbook-level name and a worksheet-level name with the same spelling. When a formula on a worksheet refers to that name without specifying a sheet, Excel generally resolves a matching local, sheet-scoped name in preference to the workbook-scoped name. This can surprise people who expect one workbook-wide definition to apply everywhere. If a formula behaves differently on different sheets, inspect the names and their scopes rather than assuming the spelling identifies only one definition.

For most individual workbooks, use workbook scope unless you have a clear reason to create a local name. If you share a workbook, consistent scope and descriptive naming reduce ambiguity for other editors. The Scope field in Define Name shows which level will apply when you create a new name.

Edit, inspect, or delete a name

To review existing names in Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. The manager lists names, their values or references, and their scopes. Select a name and choose Edit to change its name or Refers to reference; choose Delete to remove it. Inspect a name before deleting it, because formulas, charts, or validation settings may depend on it.

If you move or replace the data a name refers to, update the reference in Name Manager. Deleting a name does not delete the underlying cells, but formulas that used the name may show a name-related error until you repair them. Renaming a defined name through Name Manager can update some formula references in the workbook, but check dependent formulas and features after a substantial change, especially in a complex or shared file.

Excel for Mac also provides name-management tools on the Formulas tab, though the dialog layout and available controls can differ by release. Excel for the web supports many common uses of defined names, but its editing options may be more limited or presented differently than the desktop application. If you need to control scope or troubleshoot a complicated definition, open the workbook in desktop Excel.

Static ranges, expanding data, and tables

A named range that refers to B2:B20 is a fixed reference to those cells. If you add a new record in B21, the name may not include it automatically. This is a common reason a total, chart, or drop-down list appears to ignore newly added data. Check the Refers to field in Name Manager to see whether the named reference covers the new rows.

For data that grows over time, an Excel Table is often easier to maintain than a fixed named range. Select the data and choose Insert > Table, or use Home > Format as Table, then give the table a meaningful name through the Table Design tab. Table columns generally expand as you add rows, and formulas can refer to a column by its table and column names. A table is a structured data feature rather than simply another name for a range, so it is a useful alternative when the source is a growing list of records.

If you need a named range rather than a table, revise its reference when the data changes, or design a formula-based definition appropriate to the workbook. Formula-based names require care: relative references can behave differently depending on where they are used, and a definition that works in one workbook layout may not suit another. For a straightforward list or calculation, a Table or a clearly maintained fixed range is often more reliable.

Common problems and how to fix them

  • Excel rejects the name: Check for spaces, a leading number, or a name that resembles a cell address. Replace spaces with underscores and choose a distinct label.
  • The name selects the wrong cells: Open Formulas > Name Manager and verify both the Refers to address and the Scope. A correct-looking name can still point to the wrong sheet or range.
  • A formula does not recognize the name: Confirm that the name exists, is spelled correctly, and is in scope for the worksheet where the formula is entered. Also check for a local name that takes precedence over a workbook name.
  • New rows are missing: The reference may be fixed and may not have expanded. Update the range or consider converting the source data to a Table.
  • A name seems to disappear: Names are managed separately from worksheet tabs. Check Name Manager, including the scope column, before assuming the definition was deleted.

Frequently Asked Questions

Can I name a single cell in Excel?
Yes. Select the cell and use the Name Box or Formulas > Define Name. A single-cell name can make an important input, such as an exchange rate or reporting date, easier to identify in formulas.

Does a named range update when I add rows?
Not necessarily. A name referring to a fixed address may exclude rows added outside that address. Check its Refers to entry or use an Excel Table for data that will grow.

Can two worksheets use the same named range?
Yes, if the names are worksheet-scoped, each can belong to a different sheet. A workbook-scoped name is shared across the workbook, so it cannot simply be duplicated as another workbook-level name.

How do I find a named range?
Choose its entry from the Name Box drop-down list to select it. You can also inspect all defined names, their scopes, and their references through Formulas > Name Manager.

Can I delete a named range without deleting its cells?
Yes. Deleting the definition in Name Manager removes the name, not the worksheet data. Formulas or other workbook features that rely on that name may need to be updated afterward.

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.