Excel Named Range: How to Create and Use One

How-to basics · Updated November 21, 2026

To create a named range in Microsoft Excel for Microsoft 365 on Windows, select the cell or range, click the Name Box to the left of the formula bar, type a name, and press Enter. You can also use Formulas > Define Name to set the name, scope, and exact cells it refers to.

What a named range does

A named range gives a cell, group of cells, or other reference a readable label. Instead of remembering that a list of quarterly figures is in B2:B5, you could name those cells QuarterSales. The name can then be used in formulas, making a calculation easier to understand and maintain.

Despite the word “range,” a defined name does not have to refer to multiple cells. It can refer to one cell, a range, a constant value, or a formula. For example, a name might refer to an input cell containing a tax rate, or to a formula that calculates a result. In ordinary spreadsheet use, though, the most familiar case is a name assigned to one or more worksheet cells.

Names are stored with the workbook, so they can make a file more understandable to someone who opens it later. They do not change the cell contents or prevent the cells from being edited. Think of a name as an additional label for a reference, not as a replacement for the worksheet grid.

Create a named range with the Name Box

The Name Box is the small field immediately left of the formula bar. It normally displays the address of the active cell, such as A1. This is the quickest way to assign a straightforward name to a selection.

  1. Select the cell or range you want to name. For example, select B2:B5.
  2. Click the Name Box, not the formula bar.
  3. Type a valid name, such as QuarterSales.
  4. Press Enter. The name is assigned to the selected reference.

To check the result, click the arrow at the right edge of the Name Box and look for the name in the list. Selecting it should take you to the named cells. You can also type the name into the Name Box and press Enter to navigate to the reference.

Be sure to press Enter after typing. If you type a name while a cell is selected but do not commit it from the Name Box, Excel may treat the text as an entry in the worksheet instead. If you accidentally enter the name into a cell, undo the edit or clear it, then try again with the Name Box.

Create a name through the Formulas tab

Use the ribbon method when you want to choose a scope, add a descriptive comment, or confirm the reference before saving it. It is also useful when a name needs to refer to a formula rather than just the selected cells.

  1. Select the cell or range, if you want Excel to fill in its reference automatically.
  2. Choose Formulas > Define Name.
  3. In the New Name dialog box, enter a name and review the Refers to field.
  4. Choose the scope, if appropriate, and optionally enter a comment.
  5. Choose OK.

The Refers to field shows the address or expression the name represents. For a range on a sheet called Data, it may contain an absolute reference such as =Data!$B$2:$B$5. Check that the sheet and cells are right before confirming; a name that points to the wrong range can produce a valid-looking but incorrect calculation.

You can also choose Formulas > Create from Selection when cells have labels beside or above them. Select the label-and-data area, choose the command, then indicate whether the labels are in the top row, left column, bottom row, or right column. Excel creates names from those labels. Review the resulting names and references afterward, especially if labels contain spaces or duplicates, because Excel may adjust labels to make usable names.

Rules for choosing a name

Excel names have rules that ordinary labels in worksheet cells do not. A name cannot contain spaces, and it cannot be written in a way that Excel interprets as a cell reference. Names are not case-sensitive, so QuarterSales and quartersales are treated as the same name.

  • Start with a letter, an underscore, or a backslash.
  • After the first character, use letters, numbers, periods, or underscores.
  • Use an underscore or period in place of a space, such as Quarter_Sales.
  • Choose a specific label that will still make sense if the workbook is shared.

For instance, 2026Sales is not a suitable name because it starts with a number. Sales2026 or Sales_2026 is clearer and follows the naming rules. Avoid names that are almost indistinguishable from one another, such as Sales1 and Salesl, since the number 1 and lowercase letter l can be hard to tell apart.

Workbook scope and worksheet scope

A name has a scope: the part of the workbook in which Excel recognizes it. Workbook scope makes the name available throughout the file. Worksheet scope makes it available only on the specified sheet. The scope is chosen when a name is created in the New Name dialog, and it is visible in the Name Manager.

Workbook scope is usually the simplest choice for a range used by formulas on several sheets. Worksheet scope can be useful when different sheets need to use the same short name for different references. For example, each monthly sheet might have a local name called Input that refers to that sheet’s own input cell.

Scope can also explain why a name appears not to work. A worksheet-scoped name may not be recognized in the way you expect from a formula on another sheet. If a workbook-scoped name and a sheet-scoped name share a name, the local name can take precedence on its sheet. When names behave unexpectedly, inspect the Scope column in Formulas > Name Manager rather than creating another similar name without checking.

Use a named range in a formula

Once a name exists, you can use it in formulas wherever its reference is valid. For example, a named range can be passed to a function just as a cell reference can. The benefit is readability: a formula that describes the data is often easier to review than one made only of addresses.

Assume B2:B5 contains the quarterly sales values 120, 80, 150, and 50, and the name QuarterSales refers to B2:B5. Enter this formula in any suitable result cell:

=SUM(QuarterSales)

The result is 400, because 120 + 80 + 150 + 50 = 400. The formula remains meaningful even if a reader does not know where the source cells are located. However, if you move or change the cells, verify that the name still refers to the intended data.

Names can also help explain assumptions. A single input cell named DiscountRate, for example, is easier to identify in a formula than an unexplained address such as F2. Use a label that describes what the value means, and keep the input visibly documented on the worksheet so users can find and update it.

Static ranges, expanding lists, and Excel tables

A normal named range usually points to a particular reference. If it refers to B2:B5, entering a new item in B6 does not automatically mean the name now includes B6. A name can be made dynamic with a formula, but that adds complexity and can make a workbook harder to maintain.

If you are naming a list that will grow as rows are added, consider converting the data to an Excel table instead. Select the data and choose Insert > Table, or use Ctrl+T on Windows. Confirm whether the table has headers. Tables expand as data is added beneath them and provide structured references, such as a column name, in formulas. This is often more convenient for changing datasets than manually maintaining a fixed named range.

A table and a named range serve related but different purposes. A named range is a label for a reference or formula; a table is a structured data object with headers, filtering, and automatic expansion behavior. A workbook can use both: tables for growing records and names for key input cells or important fixed ranges. In Excel for Mac, the Table command is available from the Insert tab; the precise ribbon layout can vary by version. Excel for the web has its own ribbon layout, so command placement may differ from the Windows desktop steps above.

Review, edit, or remove names

To inspect names in Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. The Name Manager lists names, their values or references, scope, and comments. Select a name to review its Refers to entry. Use Edit to change its reference or other details, or Delete to remove a name you no longer need. Be careful when deleting: formulas that rely on the name may then return a name-related error.

If you change a name, Excel can update formulas that use it, but it is still sensible to review important calculations afterward. A name may be used in many places, including formulas on hidden sheets. Name Manager is more reliable than searching visible cells alone when you are checking where a workbook’s definitions are maintained.

On Windows, Ctrl+F3 opens Name Manager. In Excel for Mac, use Formulas > Name Manager if the shortcut differs on your keyboard or in your Excel configuration. Excel for the web supports many common named-range uses, but name-management options can differ from the desktop application; use the desktop version when you need the full set of management controls.

Common problems and how to fix them

  • The name is rejected: Remove spaces, begin with a letter or underscore, and check that the name does not resemble a cell address.
  • The range is wrong: Open Formulas > Name Manager and inspect Refers to. Correct the sheet name or cell addresses with Edit.
  • A formula does not recognize the name: Check its spelling and scope. A sheet-level name might not be available where you are using it.
  • The named list misses new rows: The reference may be fixed. Expand it, use a suitable dynamic definition, or convert the data to a table.
  • Excel changes a label created from a selection: Check the generated name in Name Manager. Labels with spaces or repeated text may need editing to become valid and distinct.

Another common misconception is that naming cells locks them or makes them safer from accidental edits. It does neither. Names improve identification and formula readability, but worksheet protection and file permissions are separate features. Likewise, assigning a name does not automatically make a formula dynamic; the behavior depends on what the name refers to.

Frequently Asked Questions

Can I name a single cell in Excel?
Yes. Select the cell and assign a name through the Name Box or Formulas > Define Name. A named cell can be used in formulas just like a named multi-cell range.

How do I find a named range in a workbook?
Open the Name Box drop-down and select the name to jump to its reference, or go to Formulas > Name Manager to review names and their references.

Can a named range include cells on different worksheets?
A simple range reference is normally a contiguous block on one worksheet. For data spread across sheets, create separate names or use a formula that explicitly combines the references, depending on the task.

Will a named range expand when I add rows?
Not necessarily. A fixed reference such as B2:B5 stays fixed unless its definition changes. An Excel table is often a more practical choice for a list that grows over time.

Can I use spaces in a range name?
No. Use an underscore or a period instead, such as Monthly_Sales. Worksheet labels can contain spaces, but defined names follow stricter rules.

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.