In Excel, a named range is a meaningful name assigned to a cell, range, constant, or formula. A valid name must start with a letter, underscore, or backslash; cannot contain spaces or look like a cell reference; and can be up to 255 characters long. Names are case-insensitive, and their scope can apply to a whole workbook or to one worksheet.
Rules for valid Excel names
Excel applies naming rules so it can distinguish a name from a cell address or a formula operator. These rules apply whether you create a name through the Name Box, the ribbon, or Name Manager.
- First character: Use a letter, underscore (_), or backslash (\). A name cannot begin with a number.
- Other characters: Use letters, numbers, periods, and underscores. Spaces and hyphens are not allowed. For example, use
Quarter_SalesorQuarter.Salesrather thanQuarter SalesorQuarter-Sales. - Length: A name can contain up to 255 characters, although shorter names are easier to read in formulas.
- Cell references: A name cannot be identical to a cell reference, such as
A1orXFD1048576. Excel also reserves the single letters R and C, in either case, because they have special meaning in reference entry. - Capitalization: Names are not case-sensitive.
NetSalesandnetsalesare treated as the same name within the same scope.
Names such as 2026Sales, Gross Margin, and R are therefore invalid. Suitable alternatives include Sales2026, Gross_Margin, and RowLabel. A period is allowed in a name, but using underscores often makes names easier to recognize and avoids confusion with other Excel naming conventions.
What a named range can refer to
The phrase named range is commonly used for a name that points to cells, but an Excel defined name can represent more than a simple block of cells. It can refer to one cell, a multi-cell range, a constant value, or a formula. This lets a workbook use descriptive labels instead of repeating hard-to-read references.
For example, suppose Sheet1 contains sales figures in B2:B4: 120, 150, and 130. You could define the name QuarterSales to refer to =Sheet1!$B$2:$B$4. With that layout and definition, the formula =SUM(QuarterSales) returns 400. The name does not change the underlying data; it gives that range a reusable label.
A name can also refer to a calculation rather than stored cells. This is useful for a repeated calculation or a workbook-wide assumption, though a name that hides a complicated formula can make a workbook harder for another person to understand. Include clear labels and document unusual definitions.
Workbook scope and worksheet scope
Scope determines where Excel recognizes a name. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available on the particular sheet where it is defined. Scope matters when you reuse a label, particularly in workbooks with repeated layouts.
For instance, you could define a worksheet-scoped name MonthlyTotal separately on January and February sheets, with each name pointing to that sheet’s total. Both sheets can use the same label for their own data. A workbook-scoped name, by contrast, is intended to identify the same item wherever a formula uses it.
If a local worksheet name and a workbook name share the same spelling, the worksheet-scoped name takes precedence in formulas on its sheet. That overlap can be confusing: a formula may use a different range than a reader expects. Avoid duplicate names unless there is a clear reason, and check the Scope column in Name Manager when a formula behaves unexpectedly. Scope is set when the name is created; changing it usually means creating a replacement with the intended scope and updating formulas.
How to create a name in Excel for Microsoft 365
For a quick workbook-scoped name, select the cell or range, click the Name Box to the left of the formula bar, type a valid name, and press Enter. The selection remains the target of the name. The Name Box is useful for quick work, but it does not provide the same control over scope and definition as the Define Name dialog.
To choose scope or enter a formula directly, use these Windows steps:
- Select the target cell or range, if the name will refer to cells.
- Go to Formulas > Define Name.
- Enter the name, choose the intended scope, and check the Refers to field.
- Select OK, then test the name in a formula or select it from the Name Box.
To review or edit existing names, choose Formulas > Name Manager. From there, you can inspect each name’s scope and definition, create or edit names, and delete names that are no longer needed. Deleting a name does not delete the cells it referred to, but formulas that used the name can then return a #NAME? error.
Excel for Mac also provides name creation and management through the Formulas tab, though exact ribbon layout can differ by release. In Excel for the web, available name-management options can differ from the desktop app; use the desktop version when you need controls that are not shown in your browser.
Using names in formulas
When a valid name refers to a range, you can use it in formulas in place of the cell address. For example, with the earlier example data in Sheet1!B2:B4 and QuarterSales defined as that range, =AVERAGE(QuarterSales) returns 133.33, repeating to two decimal places. Using the name can make the formula’s intent clearer than using a series of cell coordinates.
Excel usually offers a name as an autocomplete suggestion while you type a formula. You can also type the name directly. If the name is not recognized, check its spelling, scope, and definition in Name Manager. A worksheet-scoped name may not be available to a formula on a different sheet. Names also do not need to be used only in formulas: typing one in the Name Box and pressing Enter selects the range it refers to.
Absolute references are often appropriate for named ranges intended to keep pointing to the same cells. Relative references can be useful in specialized cases, but their result depends on the active cell context in which the name is used. If a name seems to point somewhere unexpected, inspect the Refers to field and look for missing dollar signs in a cell reference.
Do named ranges expand when data grows?
A name that refers to a fixed range does not automatically include rows added below that range. If QuarterSales points to B2:B4 and you enter another value in B5, the defined name still points to B2:B4 unless you update its definition. This is a frequent source of totals that omit new data.
For lists that grow over time, an Excel Table is often a practical alternative. Select the data and choose Insert > Table, then use the table’s name and column references in formulas. Table data and its structured references adjust as rows are added to the table. If you keep a named range instead, review its definition when the source data changes, or deliberately define a dynamic reference that fits the workbook’s needs.
Dynamic-array formulas in current Excel versions can also return changing results, and a defined name can refer to a formula. That can be useful for specialized models, but it is different from a fixed named range and may depend on the Excel version used by people opening the file. For a simple growing dataset, a Table is often easier for others to maintain.
Common name problems and how to fix them
- Excel rejects the name: Check for spaces, a leading number, a hyphen, or a spelling that matches a cell reference. Replace spaces with underscores and begin with a letter when in doubt.
- A name is already in use: Names are case-insensitive, so changing capitalization does not create a distinct name. Check for an existing name with the same spelling and scope in Name Manager.
- A formula uses the wrong range: Look for a worksheet-scoped name that shares its spelling with a workbook-scoped name. Confirm the Scope and Refers to entries rather than assuming the label is unique across the file.
- A formula returns #NAME?: Check for a typo, a deleted name, or a name that is out of scope on the formula’s sheet. Also confirm that the name was entered as a defined name rather than as text.
- New records are missing: A fixed reference does not automatically expand. Extend the definition in Name Manager or use a Table when the list is expected to grow.
- A name appears to shift: Inspect whether its definition uses relative rather than absolute references. A relative definition can resolve differently depending on where the formula is entered.
Names can also become stale when sheets or source ranges are changed. After restructuring a workbook, review Name Manager for references that point to deleted sheets or outdated cells. A name with a broken reference may remain in the workbook even after its original data has been removed.
Choosing useful names and keeping a workbook maintainable
Good names describe the data or purpose without making formulas cumbersome. Choose a consistent style, such as AnnualRevenue or Input_TaxRate, and use names that distinguish inputs, calculations, and outputs when that distinction matters. Avoid vague labels such as Data or names that could be mistaken for a worksheet or table label.
Before creating a name, decide whether it should apply to the whole workbook or only one sheet. Prefer workbook scope for shared assumptions and central data; use worksheet scope only when the same label genuinely refers to different sheet-specific items. Keep names short enough to read in formulas, and periodically remove obsolete names after checking that no formulas depend on them.
Named ranges and Tables solve related but different problems. A named range gives a selected cell reference or formula a readable label; it does not, by itself, provide a structured data container. A Table provides column headers, table-specific references, and convenient expansion as rows are added. Use the feature that matches how the data will be maintained rather than naming every range automatically.
Frequently Asked Questions
Can an Excel named range contain spaces?
No. Use an underscore or period instead, such as Sales_Data or Sales.Data. Names also cannot contain hyphens.
Are Excel named ranges case-sensitive?
No. Excel treats capitalization as equivalent, so BudgetTotal and budgettotal conflict within the same scope.
Can two sheets use the same named range?
Yes, if each name is worksheet-scoped. A separate name can exist on each sheet. Be cautious if a workbook-scoped name has the same spelling, because the local name takes precedence on its sheet.
Do named ranges automatically include new rows?
Not when they refer to a fixed cell range. Expand the reference or use an Excel Table if new records should be included automatically.
Where can I see all names in a workbook?
In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. Review the Name, Scope, and Refers to columns to understand what each name means and where it applies.
Related
- Excel Import CSV from Clipboard? Methods That Work
- Excel Import CSV: Open Comma-Separated Files Correctly
- Excel Import CSV as Text: Keep Every Value Unchanged
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Count Excel Rows While Excluding Hidden Rows with SUBTOTALTo count visible Excel rows while excluding both filtered-out and manually hidden rows, use =SUBTOTAL(103,A2:A100) when every record…
- Workout Log Template for Google Sheets: Setup & TipsA useful workout log template for Google Sheets can be as simple as a table with the workout…
- Outlook: How to Export Another User’s ContactsTo export another person’s Outlook contacts, you need permission to view their Contacts folder; the simplest option is…
- How to Remove a Horizontal Page Break in Excel QuicklyTo remove a manual horizontal page break in Excel for Microsoft 365 on Windows, select the row immediately…
- Excel Freeze Panes in the Middle of a Sheet: How It WorksYes. In Excel for Microsoft 365 on Windows, select the cell immediately below and to the right of…
- AG Grid Merge Cells: Row and Column Spanning ExplainedYes—AG Grid can make a cell span adjacent rows or columns, but it does not provide a general-purpose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.