A named range in Excel is a descriptive name assigned to a cell, a group of cells, or another value that you can refer to in formulas. For example, naming cells B2:B5 QuarterSales lets you write a formula using that name instead of repeating the cell address.
What a Named Range Means
Excel calls a name attached to a cell or range a defined name. People often use “named range” as a general term, although a defined name can refer to more than a range of cells. It can point to one cell, a multi-cell range, a constant value, or a formula. The name is a label for the reference or value; it does not move or replace the underlying data.
For example, if B2:B5 contains quarterly sales figures, you can define the name QuarterSales for those cells. If D1 contains a tax rate, you might name that cell TaxRate. A defined name can make a workbook easier to read because a formula can communicate what its inputs represent rather than relying only on coordinates.
Names are not the same as worksheet tab names, table names, or labels typed into cells. A worksheet name identifies a sheet, while a defined name identifies a reference or value that formulas can use. Excel tables have their own names and structured references, which are related tools but not identical to named ranges.
What a Name Can Refer To
A defined name may refer to a single cell, such as a tax rate, or to a rectangular block of cells, such as a monthly expense list. It can also represent a fixed value or a calculated expression. You can inspect what a name points to in the Refers to field in Name Manager.
Names can also make a workbook easier to navigate. Selecting a name in the Name Box can take you to its associated range. This is useful in large sheets where a relevant section is far from the currently visible cells. A name can therefore serve both as a formula reference and as a convenient way to identify an area of a workbook.
How to Create a Named Range
In Excel for Microsoft 365 on Windows, the quickest way to name a range is with the Name Box, the small box to the left of the formula bar that normally displays the active cell address.
- Select the cell or range you want to name.
- Click the Name Box, type a valid name such as QuarterSales, and press Enter.
- Use the name in a formula or select it from the Name Box to return to the range.
You can also select the cells, open Formulas > Define Name, enter a name in the New Name dialog, check the Refers to range, and choose the intended scope. To review, edit, or remove names, use Formulas > Name Manager. The dialog lets you see each name’s scope and reference, which is especially helpful when a workbook contains many names.
If a range has clear row or column headings, Excel can create names from those labels: select the labels and associated data, then choose Formulas > Create from Selection and indicate where the labels are. Check the result in Name Manager, because Excel derives names from the selected headings and may adjust characters that are not allowed in names.
Name Rules and Scope
A name must follow Excel’s naming rules. It cannot contain spaces, and it cannot be identical to a cell address such as A1. Names generally begin with a letter, an underscore, or a backslash; after that, they can include letters, numbers, periods, and underscores. Excel treats names as case-insensitive, so QuarterSales and quartersales are not separate names.
Each defined name has a scope. Workbook scope makes the name available throughout the workbook. Worksheet scope limits it to a particular sheet. The scope is chosen when the name is created and is visible in Name Manager. A worksheet-scoped name can be useful when the same label needs to mean different things on different sheets, but it can also cause confusion if a workbook-scoped name has the same spelling. On a sheet with a same-named local definition, the local name takes precedence in that sheet’s formulas.
For most general-purpose inputs and ranges, workbook scope is the simpler choice. Use sheet scope only when the name is deliberately specific to one worksheet or when separate sheets need independent definitions. You cannot change a name’s scope directly in Name Manager; creating a replacement with the desired scope and updating references may be necessary.
Using Names in Formulas
Once defined, a name can be used in formulas in place of its cell reference. With the name QuarterSales assigned to B2:B5, and values 120, 180, 150, and 250 in those cells, enter =SUM(QuarterSales) in any result cell; it returns 700. The function is summing the cells the name refers to, just as it would sum a directly written range.
Names can also make a calculation easier to interpret. If Subtotal refers to a cell containing 250 and TaxRate refers to a cell containing 8%, enter =Subtotal*TaxRate in a result cell to get 20. The formula works because each name resolves to its associated value. When typing a name into a formula, Excel may offer matching names in its formula autocomplete list.
A name does not automatically make a formula more accurate. It still points to a particular reference or value, so confirm that the definition covers the intended cells. If a formula returns an unexpected result, checking the name’s Refers to entry is often a good first step.
Why Use a Named Range?
The main advantage is readability. Compare a formula using a meaningful label with one containing several unexplained addresses: names can make the purpose of inputs more apparent to the workbook’s next reader. They are particularly useful when a range is used repeatedly, when formulas are long, or when a calculation refers to important assumptions such as a rate or target.
- Clarity: A name such as MonthlyCosts communicates more than a range address on its own.
- Convenient navigation: Choose a name in the Name Box to jump to its cells.
- Consistency: Reusing the same named input can reduce the risk of typing different cell addresses into related formulas.
- Easier review: Name Manager provides a central view of names, scopes, and references.
Names are most helpful when they are specific and consistently applied. A vague name such as Data may not explain what the cells contain, while a name like 2026BudgetActuals is more informative if it accurately describes the range.
Fixed Ranges and Expanding Data
A named range usually refers to the cells specified in its definition. If it refers to B2:B5, adding a new value in B6 does not necessarily mean the name now includes B6. Excel may adjust a reference when rows or columns are inserted within or around a range, but simply typing data beneath a range should not be treated as a reliable way to expand it. Check the Refers to entry after changing a workbook’s layout.
If a list is expected to grow, an Excel table is often a practical alternative. Tables expand as data is added directly beneath them and provide structured references based on column headings. A defined name can also be made to refer to a formula rather than a fixed address, but that approach needs careful setup and maintenance. For many ordinary growing lists, a table is easier for other users to understand.
There is a related distinction between a named range and a dynamic-array result. A name can refer to a range or formula, but the name itself does not guarantee that new source data will be included. Decide whether the reference should stay fixed or grow, then choose a fixed range, a table, or a suitable formula-based definition accordingly.
Common Problems and How to Fix Them
If Excel rejects a name, look for a space, an invalid first character, or a spelling that resembles a cell address. Replace spaces with underscores or use a clearer word combination. If a name already exists at the relevant scope, choose a distinct name or review the existing entry in Name Manager.
If a formula using a name gives an error or an unexpected answer, open Formulas > Name Manager and check the name’s scope and Refers to field. Confirm that the reference points to the expected sheet and cells, and that a worksheet-scoped name is being used on the appropriate sheet. A copied workbook can also contain names that point to old locations or external workbooks, so inspect unfamiliar names before relying on them.
Names can make a formula less transparent if they are undocumented or inconsistently named. Use labels that describe the contents, avoid near-duplicates, and remove obsolete definitions after confirming they are no longer used. Before deleting a name, check formulas and other definitions that may depend on it; deleting a definition can cause formulas that use it to stop working.
Named Ranges Compared With Cell References and Tables
Ordinary cell references such as B2:B5 are direct and familiar, and they are often best for a short formula used once. A named range adds a meaningful label, which is useful when the same reference appears repeatedly or when the formula should explain itself. There is no universal rule that every range should be named; too many unnecessary names can make a workbook harder to manage.
Tables are better suited to organized datasets that grow over time and have column headings. Their structured references identify table columns and generally adapt as table data changes. Named ranges are more flexible for inputs, single values, and selected blocks that are not naturally a table. A workbook can use both: a table for transaction records and named ranges for key assumptions or summary inputs.
Frequently Asked Questions
Is a named range the same as a cell reference?
A named range is a label that points to a cell or range; a cell reference is the address itself, such as B2:B5. Formulas can often use either, but the name can make the formula easier to interpret.
Can a named range refer to one cell?
Yes. A name can refer to a single cell, several cells, a constant, or a formula. The term “named range” is commonly used even when the definition refers to just one cell.
How do I see all named ranges in Excel?
In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. The list shows names, scope, and the reference or value each name uses. Select a name to review or edit its definition.
Can I use spaces in an Excel name?
No. Defined names cannot contain spaces. Use an underscore or combine words, such as Quarter_Sales or QuarterSales, instead.
Will a named range grow when I add data?
Not automatically in every case. A fixed name may continue to refer only to its original cells. For a list that should expand as records are added, consider formatting it as an Excel table or carefully defining a formula-based reference.
Related
- Add a Jigsaw Sudoku Solver to Excel
- Add Quadrants to an Excel Scatter Plot: Step-by-Step
- How Excel Files Become Corrupted: Causes and Risks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Merge Day, Month, and Year in ExcelTo merge separate day, month, and year values into a real Excel date, enter =DATE(C2,B2,A2) in a new…
- Correlation Table in Excel: Create and Read OneIn Excel for Microsoft 365 on Windows, create a correlation table by choosing Data > Data Analysis >…
- How to Open Version History in ExcelTo open version history in Excel for Microsoft 365 on Windows, open the workbook saved in OneDrive or…
- How to Duplicate Microsoft Excel Files, Sheets, and CellsTo duplicate something in Microsoft Excel, first decide whether you need a copy of the entire workbook, one…
- How to Select a Column in Excel Using the KeyboardIn Excel for Microsoft 365 on Windows, click a cell in the column you want and press Ctrl+Space…
- Wrap Text in Excel: Keep Long Cell Entries VisibleTo keep text wrapped in Excel, select the cell or range, then choose Home > Alignment > Wrap…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.