Excel Named Ranges: How to Create and Use Them

How-to basics · Updated November 19, 2026

To use named ranges in Excel, assign a descriptive name to a cell or range, then use that name in formulas instead of cell addresses. For example, if cells B2:B13 contain monthly sales and you name that range MonthlySales, the formula =SUM(MonthlySales) adds those values.

What a named range is

A named range is a label that points to one or more cells, or to a formula or constant. Instead of remembering that a calculation uses cells B2:B13, you can refer to MonthlySales. Excel resolves the name to its underlying reference when it calculates the formula. Names can be used in formulas across a workbook, and you can also use them to jump to cells or identify ranges in charts and other workbook features.

Names are most useful when they communicate what data means. A reference such as C5:C40 gives little context by itself; a name such as SurveyScores makes a formula easier to understand and review. Names can also make formulas less fragile to read when a worksheet grows or its layout changes, although a name does not automatically make every referenced range expand when you add data.

Excel names are not case-sensitive, cannot contain spaces, and generally must begin with a letter, an underscore, or a backslash. After the first character, names can include letters, numbers, periods, and underscores. A name cannot look like a cell reference such as A1. Use short, specific names such as TaxRate or NorthRegionSales rather than vague labels such as Data.

Create a named range in Excel

In Microsoft Excel for Microsoft 365 on Windows, the quickest option is the Name Box, to the left of the formula bar. Select the cell or range, click the Name Box, type a valid name, and press Enter. For example, select B2:B13, type MonthlySales, and press Enter. The selection is now identified by that name.

For more control, use the ribbon:

  1. Select the cell or range you want to name.
  2. Choose Formulas > Define Name.
  3. Enter the name in the Name box and check the Refers to box to confirm the intended cell reference.
  4. Choose Workbook or a worksheet in the Scope list, add an optional comment, and select OK.

You can also create names from labels already in a worksheet. Select a rectangular block containing both the labels and the data, then choose Formulas > Create from Selection. Indicate whether the labels are in the top row, left column, bottom row, or right column. Excel creates names from the selected labels, adjusting them to follow name rules. Check the results in Name Manager, especially if labels repeat or contain spaces.

Use names in formulas and navigation

Once a range has a name, use it wherever a cell or range reference is accepted. If B2:B13 is named MonthlySales, =AVERAGE(MonthlySales) returns the average of those 12 values. If D2 contains a tax rate and is named TaxRate, and B2 contains a price, =B2*TaxRate calculates the tax amount for that row. In the second example, copy the formula down to apply it to other rows; B2 changes to B3, while the named rate stays the same because the name points to D2.

You can also select a named range without searching through the sheet. Click the Name Box drop-down and choose a name, or type the name into the Name Box and press Enter. The named cells become selected. The Go To dialog, opened with Ctrl+G on Windows, also accepts defined names.

Names can make formulas easier to audit, but they do not explain every calculation on their own. A name such as Rate may be unclear if a workbook has several kinds of rates. Prefer names that identify the subject and role, such as AnnualGrowthRate or ShippingCost, and keep the naming approach consistent across related sheets.

Workbook scope and worksheet scope

Every defined name has a scope: either the entire workbook or one worksheet. A workbook-scoped name is available from formulas on any worksheet. A worksheet-scoped name is local to its sheet and can be useful when several sheets use the same label for different local inputs, such as a separate Target value on each region’s sheet.

If a worksheet-scoped name and a workbook-scoped name share the same spelling, the local name takes precedence in formulas on that worksheet. Elsewhere, Excel uses the workbook-scoped name. This can make a formula’s meaning less obvious, so avoid duplicate names unless there is a clear reason for them. To refer to a local name from another sheet, qualify it with the sheet name, for example Sheet1!Target; sheet names containing spaces need single quotation marks around the sheet name.

Scope is selected when you create a name and cannot be changed directly afterward in the Name Manager. If you discover that a name has the wrong scope, create a replacement with the intended scope, update formulas that use the old name, and remove the old definition when it is no longer needed.

Review, edit, and delete names

Use Formulas > Name Manager to inspect the workbook’s defined names. The manager shows each name, its value or reference, its scope, and any comment. Select a name to edit its reference or comment, or delete a definition that is no longer useful. Be cautious before deleting: formulas that depend on a deleted name can return the #NAME? error.

Before making broad changes, check whether a name is used in formulas, charts, data validation, or other workbook features. Name Manager does not always provide a complete dependency map for every kind of use, so search the workbook where appropriate and review important calculations after edits. If a range was moved or a worksheet was renamed, inspect the Refers to entry to make sure it still points to the intended cells.

Excel for Mac also provides defined-name tools from the Formulas tab, although ribbon layout and keyboard shortcuts can differ from Windows. Excel for the web can work with names in formulas, but the available name-management controls may differ from the desktop app; use Excel for Microsoft 365 on Windows for the menu steps above.

Named ranges compared with Excel tables

A named range and an Excel table solve related but different problems. A name gives a readable label to a reference. A table is a structured data object with headers, filters, and formulas that can fill down as rows are added. For a short fixed input area, a name such as DiscountRate can be convenient. For a growing list of orders, a table is often more suitable than naming a fixed block of cells.

For example, a name that refers to B2:B100 does not normally include a new value entered in B101 just because it is directly below the range. You can edit the name to include the new row or define a name using a dynamic formula, but both require deliberate setup. A table expands as you add data directly beneath it in typical use, and formulas can refer to table columns by their headers. Names and tables can be used together: a workbook can have a table for records and a named cell for an assumption used in calculations.

Common problems and practical fixes

  • The formula shows #NAME?. Check the spelling, confirm the name exists in Name Manager, and check its scope. A worksheet-scoped name may not be available from another sheet without qualification.
  • The name does not appear in the Name Box list. Confirm it was created successfully and that its scope is appropriate. Also check that you are not confusing a table name with a defined name; they are managed differently.
  • The range leaves out new rows. A fixed reference does not automatically grow just because more data is entered beside or below it. Extend the reference, use a table, or deliberately create a dynamic definition.
  • Two names seem to mean different things. Look for a worksheet-scoped definition that overrides a workbook-scoped one on that sheet. Rename or remove duplicate definitions if they cause confusion.
  • A name is rejected. Remove spaces and invalid punctuation, do not start with a number, and avoid text that could be read as a cell address.

When a formula returns an unexpected result rather than an error, inspect the Refers to field. The name might point to the wrong cells, or to a single cell when you expected a range. Select the name in Name Manager and verify the highlighted reference before changing a formula that may be correct.

When named ranges are worth using

Names are especially helpful for important assumptions and frequently reused inputs: interest rates in an illustrative model, a tax percentage, a reporting date, or a lookup list. They can also clarify long formulas by replacing repeated cell addresses with meaningful labels. Use them sparingly in small, one-off calculations where a name adds more maintenance than clarity.

A useful naming system has three qualities: names describe one purpose, similar items follow the same pattern, and scope is predictable. For instance, use names such as BasePrice and TaxRate for workbook-wide assumptions, and avoid reusing generic labels such as Value across sheets. Add comments in Name Manager when the definition’s purpose or calculation would not be clear from its name.

Frequently Asked Questions

Do named ranges update when I add rows?

Not necessarily. A name that points to a fixed range usually continues to refer to that defined range; adding data immediately outside it does not by itself guarantee expansion. Use an Excel table for a growing dataset, or update the name’s reference when the range changes.

Can a named range contain spaces?

No. Defined names cannot contain spaces. Use an underscore or combine words, such as Monthly_Sales or MonthlySales. Names are not case-sensitive, so MonthlySales and monthlysales are treated as the same name.

Can I use a name in a formula on another worksheet?

Yes, if the name has workbook scope. A worksheet-scoped name is local; to reference it from another sheet, include its sheet qualification, such as Sheet1!Target. Check for same-named local and workbook definitions if a formula seems to use the wrong value.

How do I find all the named ranges in a workbook?

Choose Formulas > Name Manager to review the defined names, their references, and their scopes. The Name Box is useful for selecting a known name, but Name Manager is the better place to inspect or edit the full list.

What happens if I delete a named range?

Deleting a name removes that definition, not the cells it referred to. Formulas that use the deleted name may show #NAME? until you restore the name or replace it with a valid reference. Check dependent formulas before deleting a definition.

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.