An Excel named data range is a descriptive name assigned to a cell or range of cells, so you can refer to it by name instead of by address. In Excel for Microsoft 365 on Windows, select the cells and enter a name in the Name Box beside the formula bar, or choose Formulas > Define Name; afterward, you can use that name in formulas, navigation, and other workbook features.
What a named range means
Cell addresses such as B2:B50 are precise, but they do not explain what the cells contain. A name such as MonthlySales makes the purpose easier to recognize in a formula or when reviewing a workbook. Named ranges are also called defined names in Excel’s interface and documentation.
A name can refer to a single cell, a block of cells, a constant value, or a formula. In everyday use, “named range” usually means a name that points to one or more cells. For example, you might name a list of product prices Prices, a target cell MonthlyTarget, or a tax-rate cell TaxRate. The name does not change the cells or their values; it gives the reference a label.
Names are stored with the workbook, not as formatting applied to the cells. A name can make formulas easier to read and maintain, but it is not a substitute for a clear worksheet layout. A good workbook still needs meaningful column headings, consistent data, and explanations for assumptions that are not obvious from the name alone.
How to create a named range
The Name Box is the quickest way to assign a simple name to a selected range. It is to the left of the formula bar and normally displays the active cell address, such as A1. To create a name on Windows:
- Select the cell or range you want to name. For example, select B2:B5 if those four cells contain sales amounts.
- Click the Name Box, type a name such as QuarterSales, and press Enter. Do not press Enter while the cursor is still in the worksheet or formula bar.
- Click an empty cell and test the name in a formula, or choose it from the Name Box list to select the range again.
You can also create a name through the ribbon. Select the target cells, then choose Formulas > Define Name. In the New Name dialog, enter a name, check the Scope setting, and review the Refers to field before selecting OK. The field should point to the intended cell or range. Use Formulas > Name Manager to inspect, edit, or delete names later. Ctrl+F3 opens Name Manager in Excel for Windows; on Mac, use Formulas > Name Manager because keyboard shortcuts can differ by keyboard and Excel settings.
If a worksheet has labels next to its data, Excel can make names from those labels. Select the label and the adjacent cells, then choose Formulas > Create from Selection. In the dialog, choose whether the labels are in the top row, left column, bottom row, or right column. Check the resulting names in Name Manager: Excel adjusts labels to fit naming rules, and a label that is blank or duplicated may not produce the name you expected.
Rules for choosing a name
Names work best when they are short enough to read and specific enough to avoid confusion. Revenue, StartDate, and WestRegionSales are generally more useful than Data1 or Range2. Decide on a consistent style for multiword names—such as WestRegionSales or West_Region_Sales—and apply it throughout the workbook.
Excel names cannot contain spaces. They can use letters, numbers, underscores, and periods, but the first character must be a letter, an underscore, or a backslash. A name cannot look like a cell reference such as A1, and R and C by themselves are reserved. Names are not case-sensitive, so TaxRate and taxrate refer to the same name. A name can be up to 255 characters, though a shorter name is easier to read and less likely to be mistyped.
Avoid names that are already used by another name at the same scope, and avoid terms that may be mistaken for ordinary labels. A name should describe what its cells represent rather than where they happen to be. For example, JanuaryPrices is more informative than ColumnB if the worksheet layout may change.
Workbook scope and worksheet scope
When you create a name, Excel assigns it a scope: either the whole workbook or one worksheet. Workbook scope makes a name available throughout the file. Worksheet scope limits it to that sheet, which can be useful when separate worksheets use the same label for different data.
For instance, two department sheets might each have a local name called MonthlyTotal, with each name referring to that sheet’s own total cell. A workbook-level name cannot be duplicated by another workbook-level name. A worksheet-level name may share its text with a workbook-level name; in that situation, the local name takes precedence on its own sheet. This can be confusing, so use local names only when there is a clear reason and document the distinction.
Scope is selected in the New Name dialog and is not generally changed by simply editing the name’s reference later. If a name has the wrong scope, you may need to create a replacement with the desired scope and update formulas that relied on the old one. Review the Scope column in Name Manager before deleting or recreating names, especially in a workbook used by other people.
Using names in formulas and navigation
Once a name exists, enter it in a formula in place of the cell address. Suppose B2:B5 contains 120, 85, 140, and 55, and that range has been named QuarterSales. With those values in B2:B5 and QuarterSales referring to B2:B5, enter =SUM(QuarterSales) in an empty cell. The result is 400, because 120 + 85 + 140 + 55 equals 400.
Names are useful in formulas that would otherwise contain several hard-to-interpret addresses. If a workbook has named ranges called ActualRevenue and RevenueTarget, a comparison can be expressed as a formula rather than a reference to two unrelated-looking cells. With ActualRevenue and RevenueTarget each referring to one cell, enter =IF(ActualRevenue>=RevenueTarget,"Met","Below"). It returns Met when actual revenue is equal to or greater than the target, and Below otherwise.
Excel often suggests a recognized name as you type a formula. You can also use the Name Box to navigate: click it, type an existing name, and press Enter to select the referenced cell or range. If you want to insert a name into a formula, press F3 on Windows to open the Paste Name dialog when the dialog is available. Names can also be used in chart series, data validation, and conditional formatting, although the exact entry field and reference requirements depend on the feature.
When a formula refers to a name, Excel follows the name’s definition. If you change the range in Name Manager, formulas using that name use the revised reference without needing each formula to be edited. That central control is one of the main benefits of names in a workbook with repeated calculations.
Fixed ranges, growing data, and Excel Tables
A name that points to B2:B100 refers to those cells; it does not automatically expand just because you type new data in B101. This fixed-range behavior is appropriate when the data has a known boundary, but it can cause new records to be left out of totals or reports. A common misconception is that naming a range makes it automatically dynamic. The name itself does not guarantee that.
For a list that grows over time, an Excel Table is often a simpler choice. Select the data, then choose Insert > Table and confirm whether it has headers. Tables expand when you add rows directly beneath them, and their column names can be used in structured references. You can also create a defined name that refers to a table column: with a table named SalesTable and a column headed Amount, the Refers to field can use =SalesTable[Amount]. This gives the column a reusable name while retaining the table’s expanding behavior.
Tables are particularly helpful when data has consistent headings and rows. They also provide built-in filtering and formatting options. A named range may be preferable for a small fixed block, a single input cell, or a formula-defined reference that is not naturally tabular. Choose based on how the data changes, not simply on which option sounds more advanced.
Excel also supports names that refer to formulas, including formulas that produce a changing result. These can be useful for advanced models, but they require careful testing: the reference must return the shape and type of result expected by the formula or feature using it. For ordinary growing lists, a Table is usually easier for another person to understand and maintain than a complex formula-defined name.
Common problems and how to fix them
- The name is rejected. Remove spaces, check that it does not resemble a cell address, and make sure its first character is allowed. If a name already exists at the same scope, choose a different one or edit the existing definition.
- A formula returns an error or an unexpected value. Open Formulas > Name Manager and inspect the Refers to field. Confirm that the name points to the intended sheet and cells, and check whether it refers to one cell, a range, or a formula.
- The name selects the wrong cells. Check whether the name uses a relative reference. Names created while a particular cell is active can contain relative references, which may behave differently when used from another location. For ordinary fixed data, absolute row and column references such as $B$2:$B$5 are usually easier to reason about.
- New rows are missing from a total. The name may refer to a fixed-size range. Expand its definition in Name Manager or use an Excel Table column that grows as rows are added.
- A name works on one worksheet but not another. Check its scope and look for a worksheet-level name that has the same text as a workbook-level name. A local name can take precedence on its own sheet.
- A copied workbook has broken names. Look for names whose Refers to field contains a #REF! error, which can result when referenced cells or sheets are deleted. Correct the reference or remove the obsolete name after confirming it is no longer needed.
Before deleting a name, use Name Manager to check its scope and reference. A name may be used in formulas, charts, validation rules, or other parts of the workbook, and deleting it can cause dependent content to stop working. When troubleshooting a shared or important workbook, save a separate copy before making broad changes.
Named ranges compared with cell references and Tables
Ordinary cell references are direct and familiar: A formula using B2:B5 makes the location explicit. That can be useful in a small worksheet or when a formula is easiest to understand through its layout. The drawback is that a formula full of addresses can be hard to audit, especially if the same input appears in many places.
Named ranges trade some location detail for meaning. A name such as QuarterSales is easier to interpret, and its reference can be updated centrally. The trade-offs are that names require maintenance, scope can cause confusion, and a vague name may hide which data is actually being used. Name Manager is the place to investigate that hidden definition.
Tables are designed for structured rows and columns that may grow. Their headings provide context, and their references can expand with the data. They are not a universal replacement for names: a single assumption cell or a non-tabular range may be clearer as a named range. Many workbooks use both, with Tables for records and names for important inputs or reusable ranges.
Frequently Asked Questions
Is a named range the same as an Excel Table?
No. A named range is a label attached to a cell, range, constant, or formula. A Table is a structured data feature with headers and rows that can expand as data is added. A name can refer to a Table column, but the concepts are distinct.
How do I find all named ranges in a workbook?
Choose Formulas > Name Manager. The list shows names and their references, and includes scope information. You can select a name to review or edit its definition.
Can I use spaces in an Excel range name?
No. Use an underscore or combine the words, such as Sales_2026 or Sales2026. Names also cannot be formatted like cell references.
Why does my named range not include new rows?
It may be a fixed-size reference, which does not expand automatically when you add data below it. Update the reference or convert the data to a Table if the list is expected to grow.
Can two worksheets have a name with the same text?
Yes, worksheet-level names can use the same text on different sheets. A workbook-level name may also coexist with a worksheet-level name, but the local name takes precedence on its sheet. This is why checking scope matters when a formula behaves unexpectedly.
Do named ranges work in Excel for Mac and Excel for the web?
Named ranges are supported in current Excel versions across platforms, though the interface and keyboard shortcuts can differ. On Mac, use the Formulas tab to access name commands and check the shortcut shown for your Excel setup. In Excel for the web, look for name commands on the Formulas tab; if a command is unavailable in your current web experience, open the workbook in the desktop app to manage it.
Related
- Excel: Import Multiple CSV Files Into One Worksheet
- Excel Insert Blank Row Every Other Row: Methods That Work
- Excel Import CSV With Headers: Steps, Options, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Goal Seek in Excel: How to Find a Target ValueTo use Goal Seek in Excel for Microsoft 365 on Windows, open Data > What-If Analysis > Goal…
- 0 in Excel Conditional Formatting: 10 Practical RulesTo highlight zero values in Excel for Microsoft 365 on Windows, select the cells, then choose Home >…
- How to Delete Blank Rows at the End of an Excel SheetTo delete blank rows beneath your data in Excel for Microsoft 365 on Windows, find the last row…
- Excel Autofill and Flash Fill: How Each Feature WorksExcel Autofill extends values, dates, formatting, or formulas from a selection; Flash Fill detects a text pattern from…
- Excel Time Duration Format: Display Hours Beyond 24To show an elapsed time in Excel without resetting the hours after 24, apply the custom number format…
- How to Create a 4 x 6 Label in WordTo create a 4 x 6 label in Microsoft Word, set the document paper size to 4 inches…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.