Named Ranges in Excel: How They Work and How to Use Them

A named range in a spreadsheet is a descriptive label assigned to a cell or group of cells; in Excel, it is usually called a defined name. Instead of referring to cells such as A2:A10 in a formula, you can refer to a name such as Prices, which can make formulas easier to read, enter, and maintain.

What a named range means

A range is one cell or a block of cells. A named range gives that reference a useful label. For example, you might name B2:B20 MonthlySales, or name a single cell containing a tax rate TaxRate. The name does not change the cells or their contents: it is another way to refer to them.

Excel’s broader term, defined name, covers more than multi-cell ranges. A defined name can refer to one cell, a range, a constant value, or a formula. People often say named range even when the name refers to only one cell. Names can be used in formulas, and typing a name into Excel’s Name Box can also take you directly to the cells it refers to.

Names are particularly useful when a workbook has formulas that will be read or updated by someone else. A reference such as =SUM(B2:B20) tells you what Excel is adding only if you know what column B contains. A name such as MonthlySales can make the purpose clearer at a glance.

How Excel uses names

When Excel encounters a name in a formula, it looks up the name’s definition and uses the associated reference or value. If MonthlySales refers to Sheet1 cells B2:B20, a formula using MonthlySales works with those cells. The name is a reference, not a copy of the data, so changing a value in the referenced cells changes the result of formulas that use the name.

A name can also make a formula easier to reuse. If TaxRate refers to a cell containing 8%, formulas can use TaxRate rather than repeating a particular cell address. If you later move the tax-rate value, Excel can update a reference defined through the interface, while the formula can continue to use the same name. The name itself does not make a calculation more accurate; the reference and the underlying data still need to be correct.

Names are not limited to the worksheet where you first create them. Their scope determines where Excel recognizes them, as explained below. A name that exists in a workbook can also be used in formulas on other sheets if its scope allows it.

How to create a named range in Excel

In Excel for Microsoft 365 on Windows, you can create a name with the Name Box or the ribbon. To use the Name Box, select the cells, click the box to the left of the formula bar, type a valid name, and press Enter. For instance, selecting B2:B20 and entering MonthlySales assigns that name to the selected cells.

For more control, select the cells and choose Formulas > Define Name. In the New Name dialog, enter a name, check the Scope setting, and confirm the Refers to box points to the intended cell or range. Choose OK to create it. To review, edit, or remove names later, use Formulas > Name Manager.

A practical sequence is:

Select the exact cell or range you want the name to represent.
Create the name with the Name Box or Formulas > Define Name.
Check the name and its reference in Formulas > Name Manager.
Use the name in a formula, then verify the result against the underlying cells.

In Excel for Mac, the equivalent ribbon commands are also available on the Formulas tab, though ribbon layout and dialog appearance can vary by release. Excel for the web can work with many existing names, but the desktop app provides the fuller set of name-creation and management controls. If a command is not available in the web interface, open the workbook in the desktop app.

Using names in formulas

Once a name exists, you can type it into a formula just as you would a cell reference. Suppose UnitSales refers to A2:A5 and those cells contain 12, 8, 15, and 5. With those values in A2:A5 and UnitSales defined as that range, this formula returns 40:

=SUM(UnitSales)

Names can also clarify formulas that combine values. Imagine that A2 contains a price of 125 and B2 contains a tax rate of 8%; define TaxRate as a reference to B2. With the price in A2 and TaxRate referring to B2, this formula returns 10:

=A2*TaxRate

A name can be used in functions such as SUM, AVERAGE, and COUNT when the defined reference is suitable for that function. A name can also be entered through Excel’s formula autocomplete suggestions. For formulas that will be copied down a column, check whether the name refers to the intended fixed range or value; a named reference generally remains the same name as the formula is copied, unlike a relative cell reference that can shift.

Workbook scope and worksheet scope

When you create a name, Excel gives it a scope. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available only on the sheet named in its scope. Workbook scope is a sensible choice for a label intended to be shared across several sheets, while sheet scope can be useful when a label is meaningful only in one part of a workbook.

Names can be confusing if two scopes contain the same spelling. A worksheet-scoped name can take precedence in formulas on its own sheet, while the workbook-scoped name remains available elsewhere. If a formula appears to use the wrong cells, check the Scope column in Name Manager and look for duplicate names. Giving names distinct, descriptive labels is usually simpler than relying on scope precedence.

Scope is separate from the cells’ location. A workbook-scoped name may point to a range on one particular sheet; it does not automatically include equivalent cells on every sheet. Review the Refers to field to see the exact reference and use sheet names where needed.

Fixed ranges, growing data, and Excel tables

A name that refers to B2:B20 continues to refer to that range unless its definition changes. If new data is added in B21, it may not be included. This is a frequent source of incomplete totals: the name looks right, but the range has not expanded to include new rows.

For lists that regularly gain rows, an Excel table is often a more convenient alternative. Select the data and choose Insert > Table, making sure the header setting matches the selection. Tables expand as rows are added and offer structured references based on column headers. For example, if a table named Orders has a column named Amount containing 20, 30, and 50, the formula below returns 100:

=SUM(Orders[Amount])

With the Orders table and its Amount column containing those three values, the formula totals that table column. A named range is still useful for a small fixed input area or for a label such as TaxRate. For a changing data list, a table usually communicates the data structure more clearly and reduces the risk of leaving new rows out.

Some Excel users create names that refer to formulas rather than directly to a selected range. Those names can support more advanced workbook designs, but they require careful checking in Name Manager. A name is only as reliable as its definition, and a formula-based name may behave differently from a simple fixed range.

Name rules and common problems

Excel names have rules that differ from ordinary labels. A name cannot contain spaces, and it cannot be written like a cell address such as A1. Names can use letters, numbers, periods, and underscores, and they must begin with a letter, underscore, or backslash. For readability, use a short word or combine words with underscores, such as First_Quarter or TaxRate.

Invalid name: Sales Total contains a space. Use SalesTotal or Sales_Total instead.
Looks like a cell reference: B2 is already interpreted as a cell address, so it cannot serve as a useful defined name.
Wrong cells selected: Creating a name with the wrong selection can make a correct-looking formula return an incorrect result.
Range did not grow: A fixed reference may omit rows added after the name was created.
Unexpected scope: A sheet-level name with the same spelling as a workbook-level name can lead to surprising results.

If a formula returns an error or an unexpected value, open Formulas > Name Manager and inspect the name, scope, and Refers to field. Confirm that the referenced sheet and cell addresses are correct, and check whether the intended name was deleted or spelled differently in the formula. Also check for a name that refers to a blank cell or an outdated range.

Named ranges compared with cell references

Cell references are direct and compact. For a quick calculation in a small worksheet, =SUM(B2:B20) may be perfectly clear, especially when the column header explains what the values represent. Names add meaning and can reduce the effort of remembering important cell addresses, but they also introduce definitions that a reader may need to inspect.

A good rule is to name references that carry meaning beyond their coordinates, such as an assumptions cell, a key input range, or a commonly used list. There is little benefit in naming every small block in a simple worksheet. Too many vague names can make a workbook harder to understand than ordinary references.

Tables serve a different purpose: they organize related records with headers and expanding rows. A named range labels a reference; it does not turn cells into a structured data table. Choose the feature that matches the job, and use both where useful—for instance, a table for transaction records and a named cell for a workbook-wide assumption.

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 identifies the location directly. Both can be used in formulas, but a name can make the purpose easier to understand.

How do I see all named ranges in Excel?
Choose Formulas > Name Manager. It lists names and lets you review their scope and the cells or formulas they refer to.

Can a named range include more than one cell?
Yes. A name can refer to a single cell or a multi-cell range, including a row, column, or rectangular block.

Do named ranges update automatically when I add rows?
Not necessarily. A name defined for a fixed range can omit new rows. For data that grows regularly, consider using an Excel table, which expands as rows are added.

Can I use spaces in a range name?
No. Use letters, numbers, underscores, or periods within the name, and choose a form such as Annual_Sales instead of Annual Sales.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.