A named range in Excel 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 an address such as B2:B20. In Microsoft Excel for Microsoft 365 on Windows, select the cells and type a name into the Name Box, or use Formulas > Define Name; then use that name in formulas and other workbook features.
What a named range means
Excel normally identifies cells by column letters and row numbers. A reference such as B2:B20 tells Excel exactly which cells to use, but it does not explain what the cells represent. Naming that range Sales makes the reference more readable and can make a workbook easier to maintain. A formula containing Sales can be more meaningful to a colleague than one containing B2:B20, especially in a large model.
“Named range” is the everyday term, but an Excel defined name does not have to refer to a range of cells. It can refer to one cell, a constant value, or a formula. For example, a name might refer to a tax-rate cell, or to a calculation used repeatedly. Names are stored in the workbook and can be used in formulas, charts, data validation, and other features that accept references. A name is not a replacement for a cell address in every context, but it gives a useful label to a reference or calculation.
Create a named range with the Name Box
The Name Box is the small field to the left of the formula bar. It normally displays the active cell address, such as A1. To create a basic named range in Excel for Microsoft 365 on Windows:
- Select the cell or range you want to name, such as B2:B20.
- Click the Name Box, type a valid name such as MonthlySales, and press Enter.
- Click another cell, then open the Name Box list to check that the name appears. Selecting it should take you back to the named cells.
Press Enter after typing the name; otherwise Excel may treat the text as an edit to the active cell rather than save it as a name. If you select one cell, the name refers to that cell. If you select multiple cells, it refers to the selected range. The Name Box is quick for straightforward names, but it does not let you choose a worksheet-level scope or enter a more complex formula for the name.
Create or edit a name with the ribbon
For more control, select the cells and choose Formulas > Define Name. In the New Name dialog, enter a name, confirm the reference in the Refers to field, and choose the scope. The default scope is usually Workbook. Select OK to save it. You can also create a name without first selecting cells by opening Formulas > Name Manager and choosing New.
Name Manager is the central place to review existing names. It shows details such as each name’s scope and what it refers to, and lets you create, edit, filter, or delete names. To change a name, select it in Name Manager and choose Edit; check the Refers to field as well as the spelling. Deleting a name does not delete the cells it referred to, but formulas that use the deleted name can show a #NAME? error. On Mac, the ribbon also provides name-management tools, though exact placement and dialog appearance can vary by Excel version. The Name Box method works there too.
Excel naming rules to know
A name must follow Excel’s naming rules or Excel will reject it. In particular, names:
- Must start with a letter, an underscore (_), or a backslash (\).
- Can contain letters, numbers, periods, and underscores, but cannot contain spaces. Use a name such as FirstQuarterSales rather than First Quarter Sales.
- Cannot look like a cell reference. For instance, A1 is a cell address, not a suitable defined name.
- Are not case-sensitive. Excel treats MonthlySales and monthlysales as the same name.
- Can be up to 255 characters long, though short, descriptive names are easier to read and manage.
Names can include periods, which can be useful for a compact style such as Sales.Q1, but periods are not a substitute for spaces in a label. Avoid names that are too generic, such as Total or Value, when the workbook contains several different totals or values. A clear name describes the data or purpose, such as OrderDate or TaxRate, and helps a reader understand what a formula is intended to calculate.
Use names in formulas
Once a name refers to the right cells, enter it in a formula where you would otherwise use the range address. Excel can often suggest defined names as you type. The name must be spelled correctly and available in the current formula’s scope.
With monthly sales values of 120, 180, and 200 in cells B2:B4, and a workbook-level name MonthlySales referring to B2:B4, enter this formula in a result cell:
=SUM(MonthlySales)
The formula returns 500, the sum of 120 + 180 + 200. If the range later moves and you update the name’s Refers to setting, formulas that use MonthlySales can continue to work without changing every formula individually. Names can also be used in calculations with ordinary cell references. For example, with a tax rate of 8% in B1, a name TaxRate referring to B1, and a price of 50 in A2, the formula =A2*TaxRate returns 4. The cell layout and values matter: the calculation is 50 × 0.08.
When a formula is difficult to interpret, give key inputs and ranges names that express their roles. Still, names do not make a formula correct by themselves. Check that each name refers to the intended cells, that the range includes the right rows, and that the formula uses the appropriate function. In a shared workbook, use a consistent naming style so that readers can distinguish inputs, totals, and source data.
Workbook scope and worksheet scope
Scope controls where a name can be used. A workbook-scoped name is available throughout the workbook and is generally the simplest choice for a shared input or a range used on multiple sheets. A worksheet-scoped name is available only on the sheet where it was created. In Name Manager, the Scope column identifies whether a name belongs to the workbook or to a particular worksheet.
Scope matters when two names have the same spelling. A worksheet-scoped name can coexist with a workbook-scoped name of the same name; in a formula on that worksheet, the local name takes precedence over the workbook-level name. This can make a formula behave differently from what a reader expects. If you see an unexpected result, inspect the name’s scope in Name Manager and check whether the current sheet has a same-named local definition. Choose worksheet scope only when the sheet-specific behavior is deliberate. Scope is chosen when the name is created; to change an existing name’s scope, create a new name with the desired scope and update references as needed.
Fixed ranges, growing data, and Excel Tables
A name that refers to B2:B20 continues to refer to those cells; it does not automatically expand just because you type new data in B21. This is a common source of incomplete totals. If a dataset will grow over time, consider converting it to an Excel Table: select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows. Tables expand as rows are added and provide structured references based on column headings, such as Table1[Sales]. That behavior is often easier to maintain than manually adjusting a fixed named range.
For a spill result in Microsoft 365, a defined name can refer to the spill range by using the spill-reference operator (#), provided the formula is anchored in the cell that produces the spill. This is useful for a list whose size changes as its source changes. Dynamic array functions such as FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later, while some newer functions have more limited version availability. If a workbook will be opened in older Excel versions, check function compatibility before relying on newer dynamic-array behavior. For many ordinary lists, a Table is a clearer and more robust choice.
Common problems and how to fix them
If Excel rejects a name, first check for spaces, a leading number, or a spelling that resembles a cell reference. If a formula using a name returns #NAME?, check the spelling, confirm that the name still exists in Name Manager, and verify that its scope makes it visible from the formula’s sheet. A typo in a formula can produce the same error, so inspect the entire formula rather than assuming the defined name is the only cause.
If a formula returns a plausible but incorrect total, the name may point to the wrong cells or omit new rows. Select the name in Name Manager and review Refers to. If it refers to a fixed address, compare that address with the actual data. Also look for hidden or filtered rows if the chosen calculation should treat them differently: SUM includes values in hidden rows, while SUBTOTAL can be used for calculations that respond to filtered lists, depending on its function number and the situation.
Another frequent source of confusion is that a name can refer to a formula rather than a visible range. In Name Manager, the Refers to field is the best place to see what Excel is evaluating. A name can also contain an incorrect sheet reference after a worksheet is renamed or a workbook is reorganized. Edit the reference carefully and test a formula that uses the name before relying on it across the workbook. Keep a backup when making extensive changes to a workbook’s names.
When named ranges are useful—and when they are not
Named ranges are especially useful for important assumptions, repeated inputs, and calculation areas whose cell addresses are hard to remember. A financial forecast might use names such as GrowthRate, StartDate, and UnitsSold. A workbook with several sheets can use a workbook-scoped name for a central input that many formulas need. In a small one-off calculation, however, a direct cell reference may be perfectly clear; adding dozens of names can create another layer to maintain.
Names also differ from Tables. A named range gives a label to a reference or formula, but a basic fixed range does not automatically grow with added rows. A Table provides a structured data object with named columns and typically expands when new adjacent rows are entered. Neither option is universally better: use a named range when you want a meaningful label for a particular cell or range, and consider a Table when you are managing records that will grow and need column-based references.
Frequently Asked Questions
What is the difference between a named range and a cell reference?
A cell reference identifies a location, such as B2:B20. A named range is a label, such as MonthlySales, that points to a cell, range, or formula. The name can make formulas more readable, while the underlying reference still determines what data Excel uses.
Can an Excel name contain spaces?
No. Defined names cannot contain spaces. Use a compact name such as AnnualRevenue or separate words with underscores, as in Annual_Revenue.
How do I see all named ranges in a workbook?
In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. It lists defined names and lets you inspect their scope and Refers to details. The Name Box dropdown is useful for selecting a name, but Name Manager is better for reviewing and editing the full list.
Does a named range automatically include new rows?
Not if it points to a fixed address such as B2:B20. To make growing data easier to manage, use an Excel Table or deliberately update the name’s reference when the data expands.
Can I rename or delete a named range?
Use Formulas > Name Manager to edit or delete a name. Before deleting one, check whether formulas, charts, or other workbook features use it. Removing the name itself does not remove its cells, but dependent formulas may stop working.
Related
- Excel Slicer Examples: PivotTables, Tables, and Tips
- Excel Slicer Formatting: Styles, Size, Layout, and Use
- Excel Slicers: What They Do and How to Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Import Fonts in Excel for Windows: Install and Use ThemTo use a font in Excel for Windows, install its font file in Windows, close and reopen Excel,…
- Deli Inventory Spreadsheet: Track Stock, Waste, and CostA deli inventory spreadsheet is a workbook for recording what you have on hand, what comes in, what…
- Excel Color-Coded Drop-Down Lists: Setup and RulesTo make a color-coded drop-down in Excel for Microsoft 365 on Windows, create a list with Data Validation,…
- How to Edit Custom Number Formats in Excel Step by StepTo edit a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1,…
- Excel Power Query: Remove Leading ZerosIn Excel Power Query, select the column and choose Transform > Data Type > Whole Number to remove…
- Excel Table Data Validation: Setup and TroubleshootingTo add data validation to an Excel table, select its data cells, choose Data > Data Validation, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.