An Excel named range is a descriptive name assigned to a cell, a group of cells, or a formula, so you can refer to it by name instead of by a cell address. In Microsoft Excel for Microsoft 365 on Windows, create one by selecting the cells, typing a valid name in the Name Box to the left of the formula bar, and pressing Enter.
What an Excel named range means
Cell references such as B2:B20 tell Excel where data is, but they do not explain what the data represents. A name such as MonthlySales can make a formula easier to read and help someone understand the workbook without first tracing its cell addresses. A named range can refer to one cell, a rectangular block of cells, a nonadjacent selection, or a value or formula.
For example, a name called TaxRate might refer to a cell containing a rate, while ProductList might refer to a column of product names. When you use a name in a formula, Excel resolves it to the defined reference. The name does not replace the cells or change their contents; it provides another way to identify them.
Names are useful in small worksheets, but they can be especially helpful in larger workbooks with many formulas, repeated inputs, or several sheets. They can also make workbook maintenance simpler: if a named reference is updated, formulas using that name can use the revised reference without each formula being rewritten.
How names and scope work
Every defined name has a name, a scope, and a reference or formula. Scope determines where the name can be used. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available only on its particular sheet, unless a formula explicitly qualifies it with the sheet name.
If a workbook-scoped name and a sheet-scoped name have the same spelling, Excel gives the local worksheet name precedence in formulas on that sheet. This can make duplicate names confusing, so use unique names unless there is a specific reason to create sheet-level versions. The Scope column in Name Manager shows where each name applies.
Excel names are not case-sensitive, so Sales and sales refer to the same name. A name must start with a letter, an underscore, or a backslash; later characters can include letters, numbers, periods, and underscores. Spaces are not allowed, and a name cannot be indistinguishable from a cell reference such as A1. Use an underscore or a descriptive compound word instead of spaces, such as North_Region or NorthRegion.
When a name is created for a cell range, Excel normally records absolute cell addresses, such as $B$2:$B$20. That means the named range continues to point to those cells when a formula containing the name is copied elsewhere. A name can instead refer to a formula or constant, not just a fixed block of cells.
Ways to create a named range
The Name Box is the quickest way to assign a simple name to a selected range. Select the cells, click the Name Box at the left end of the formula bar, type the name, and press Enter. Selecting a range first matters: if you type a name while a single cell is selected, the name will refer to that cell rather than the larger area you may have intended.
For a more explicit setup, use Formulas > Defined Names > Define Name. In the New Name dialog, enter the name, choose its scope, and check the Refers to field. You can type a reference there or use the range selector to pick cells. Select OK to save it. This method is useful when you need to choose worksheet scope or define a name that refers to a formula.
If a table already has clear column headings, Excel can create names from those labels. Select the cells, including the label row or column, then choose Formulas > Defined Names > Create from Selection. In the dialog, select the location of the labels, such as Top row, and confirm. Check the resulting names in Name Manager, especially if headings include punctuation or duplicate wording.
To navigate to a named range, open the Name Box drop-down and select its name. Excel jumps to the referenced cells. You can also type a name in the Name Box and press Enter. In Excel for Microsoft 365 on Windows, Ctrl+F3 opens Name Manager; on Mac, use Formulas > Name Manager if that shortcut differs in your keyboard setup.
Using names in formulas
A name can replace a cell reference inside many ordinary formulas. The benefit is not that the calculation changes, but that its purpose can be easier to see. A formula like =SUM(B2:B5) identifies a location; =SUM(QuarterlySales) communicates what is being added. Names can also be used in formulas on other worksheets when they have workbook scope.
For this example, suppose cells B2:B5 contain 120, 180, 240, and 460, and the workbook-scoped name QuarterlySales refers to B2:B5. The formula is:
=SUM(QuarterlySales)
The result is 1,000 because 120 + 180 + 240 + 460 equals 1,000. The range name must already exist; typing an unfamiliar name in a formula does not automatically create a defined name.
With the same values in B2:B5 and QuarterlySales still referring to that range, you can calculate its average:
=AVERAGE(QuarterlySales)
The result is 250, since 1,000 divided by four cells is 250. If QuarterlySales is later changed in Name Manager to refer to a different set of cells, formulas using it will use the updated range.
Names can also refer to a single input. For example, a name DiscountRate could point to a cell containing a percentage, and formulas can use that name wherever the rate is needed. This keeps a shared assumption visible and central rather than repeating a hard-coded number in several formulas. Clearly label the input cell and make sure the name refers to the intended value.
Choosing between a fixed range and a table
A regular named range is appropriate when the relevant cells are stable or when you want to name a particular input area. But if rows are routinely added to a list, a fixed reference such as $B$2:$B$20 may not include new entries automatically. You can edit the name when the data grows, but forgetting to do so can leave totals incomplete.
For growing datasets, consider converting the data to an Excel table with Insert > Table, after checking that the range includes the headers. Tables expand when new rows are added immediately below them, and their structured references use meaningful labels such as SalesTable[Amount]. You can use a table column directly in many formulas, or create a defined name that refers to that column. A table is often a better fit for changing records; a named range is often a better fit for a specific input, output, or fixed calculation area.
These options solve different problems. A named range gives a reference a chosen label. A table adds a structured data object with column headers, filters, and behavior that adapts as records are added. You can use both in the same workbook, but avoid creating extra names for every table column unless those names improve readability or are needed by another part of the workbook.
Managing, changing, and deleting names
Open Formulas > Defined Names > Name Manager to review existing names. The dialog lists details such as each name’s scope and its Refers to expression. Select a name to inspect it, then use Edit to change its reference or name. If the cells have moved or the range has grown, updating Refers to can repair formulas that already use that name.
Changing a name’s scope is not a simple edit in Name Manager. If you need to change a worksheet-scoped name into a workbook-scoped one, create a new name with the desired scope, update formulas as needed, and remove the old name only after checking for dependencies. Deleting a name does not delete the cells it referred to, but formulas that depended on it can return a #NAME? error.
Before removing a name, consider searching the workbook for its spelling and checking formulas, charts, data validation, and other workbook features that may use it. Name Manager helps identify what the name points to, but it does not always make every use immediately obvious. Keeping a short note in a nearby cell or a documentation sheet can help explain names whose purpose is not self-evident.
Common problems and misconceptions
- The name is rejected: Check for spaces, a leading number, or a spelling that looks like a cell address. Use a clear name such as Unit_Cost instead of Unit Cost.
- A formula returns #NAME?: Confirm the name exists, is spelled correctly, and is in scope for that formula. Also check whether the name was deleted or whether a copied workbook is missing a required definition.
- A total omits newly added rows: The name may refer to a fixed range that ends before the new data. Expand its Refers to range or use a table-based reference for a list that grows.
- The wrong cells are selected: Inspect Refers to in Name Manager. A name can point to a valid range that is simply not the range you intended.
- Similar names behave differently across sheets: Check scope. A worksheet-scoped name can override a workbook-scoped name of the same spelling on that sheet.
A common misconception is that a named range automatically expands whenever adjacent cells are filled. A standard name pointing to a fixed address usually does not do that. Another misconception is that names are only for formulas; they can also make it easier to navigate to important ranges and identify inputs in a workbook.
When named ranges are most useful
Use names where they make a workbook easier to read, review, or maintain—not simply because Excel offers the feature. Good candidates include assumptions used in several calculations, a clearly defined input or output area, and a fixed range whose meaning is not obvious from its address. A name such as AnnualTarget is more informative than a bare reference when the same input appears in many formulas.
Use concise, specific names that distinguish similar concepts. If a workbook contains several measures, names like GrossRevenue and NetRevenue are clearer than Revenue1 and Revenue2. For a small one-off calculation, a direct cell reference may be simpler than creating and documenting a name. Too many vague names can make a workbook harder to maintain, not easier.
Frequently Asked Questions
How do I find a named range in Excel?
Use the Name Box drop-down to the left of the formula bar and select the name. Excel selects or navigates to the range. You can also review names and their references in Formulas > Defined Names > Name Manager.
Can a named range refer to one cell?
Yes. Select one cell and assign it a name, or define the name through the Define Name dialog. A name for a single input can make formulas more understandable when that value is used repeatedly.
Do named ranges expand automatically?
Not usually when the name refers to a fixed cell address. If the data is a growing list, an Excel table is generally more suitable because its structured references can include added table rows.
Can I use a named range on another worksheet?
Yes, if its scope is the workbook. A worksheet-scoped name is limited to its sheet unless you explicitly qualify it. Check the Scope column in Name Manager when a name cannot be resolved where you expect.
What happens if I delete a named range?
The referenced cells remain, but formulas that use the deleted name can show #NAME?. Check for formula and workbook-feature dependencies before deleting a name, and update affected formulas or definitions first.
Related
- How to Compare Two Excel Sheets
- Excel Not Responding: How to Unfreeze It Safely
- How to Undo a Circular Reference in Excel Step by Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Embed a Video in PowerPoint: Steps, Formats & FixesTo embed a video in PowerPoint for Microsoft 365 on Windows, open the slide, choose Insert > Video…
- Flash Fill in Excel: How to Fill Data by Pattern CorrectlyFlash Fill in Excel detects a pattern in examples you type and fills matching values into nearby cells;…
- Wedding Budget Spreadsheet: Download and CustomizeYes—an Excel wedding budget spreadsheet can be saved as a downloadable .xlsx workbook. The layout below tracks planned…
- Filtering in Google Sheets: How to Narrow and Sort DataTo filter data in Google Sheets, select a cell in your table and choose Data > Create a…
- Excel Sheet Dropdown Lists: Create One From Another TabTo make a dropdown in Excel that uses choices stored on another worksheet, create a workbook-level named range…
- Import CSV Into Excel Without Losing Leading ZerosTo import a CSV into Excel without losing leading zeros, use Data > From Text/CSV in Excel for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.