Excel Named Range Formula: How to Create and Use One

Formulas & functions · Updated November 21, 2026

In Excel, a named range is a meaningful name assigned to a cell or range, and its formula is entered in the Refers to box in Formulas > Name Manager > New. For a fixed range, the reference might be =Sheet1!$A$2:$A$6; for a range that should expand as entries are added, you can define a dynamic reference instead.

What a named range means in Excel

A named range is a label that stands in for a cell reference. Instead of writing a formula that refers to a block of cells by its address, you can give that block a name such as Sales, ProductList, or TaxRate. Excel stores the name and what it refers to in the workbook, so you can reuse it in formulas and other features.

The phrase “name range formula” can mean two related things: the formula or reference that defines a name, or a worksheet formula that uses a name. A named range itself is not an Excel function. You create it by defining a name and setting its Refers to entry to a cell, range, constant, or formula.

For example, if your worksheet has sales amounts in B2:B6, a name called Sales can refer to that range. With sales amounts in B2:B6 and the name Sales defined to refer to those cells, enter this in a worksheet cell to total them: =SUM(Sales). The result is the same as summing the cell addresses directly, but the named version can be easier to understand when reading a large model.

Create a named range with Name Manager

In Excel for Microsoft 365 on Windows, use the ribbon to define a name. The Scope setting determines whether the name is available throughout the workbook or only on one worksheet.

  1. Select Formulas > Name Manager, then select New. You can also choose Formulas > Define Name to open the New Name dialog directly.
  2. Enter a clear label in the Name box. For example, use ProductList for a list of products.
  3. Choose Workbook for a name you want to use across sheets, or select a worksheet for a sheet-level name.
  4. In Refers to, enter the cell or range reference, beginning with an equals sign. For a list in A2:A6 on Sheet1, use =Sheet1!$A$2:$A$6.
  5. Select OK, then close Name Manager. Use the name in a worksheet formula or select it from the Name Box to navigate to the referenced cells.

The example assumes the list is in cells A2:A6 on a worksheet named Sheet1. If the sheet name contains spaces, surround it with apostrophes in the reference: for example, a range on a sheet named Sales Data should be qualified as ='Sales Data'!$A$2:$A$6. The apostrophes are part of Excel’s sheet-reference syntax.

You can also select the cells first and type a name into the Name Box, located to the left of the formula bar. That is a quick way to create a simple range name, but Name Manager is better when you need to choose scope, inspect the exact reference, or edit an existing name.

How the Refers to formula works

The Refers to entry tells Excel what the name represents. It can point to one cell, a rectangular block, a whole column, or a formula that returns a reference or value. Fixed references commonly include dollar signs so the row and column stay fixed if the reference is copied or edited.

For instance, suppose a workbook has a rate in B2 and you want a workbook-level name called DiscountRate for that cell. With the rate stored in Sheet1!B2, set Refers to to =Sheet1!$B$2. If B2 contains 0.10, a formula using the name can refer to the 10% rate without embedding the cell address in every calculation.

A name can also represent a calculation rather than a range. With the tax rate in B2 and a pre-tax amount in A2 on Sheet1, define a name called TaxOnCurrentSale that refers to =Sheet1!$A$2*Sheet1!$B$2. This name represents the calculated tax amount for those cells; it is not a range you can select. Names that calculate values are often called named formulas, which is why the wording can be confusing.

Excel names have rules. They cannot contain spaces, cannot be written like a cell address such as A1, and must begin with a letter, underscore, or backslash. Names are generally not case-sensitive, so Sales and sales are treated alike. Use underscores or mixed capitalization to make longer names readable, such as Annual_Sales.

Choose fixed or expanding references

A fixed named range is appropriate when the data size is known and unlikely to change. It is simple to inspect and avoids including blank cells. Its drawback is that newly added records will not be included unless you edit the name or insert rows in a way that updates the referenced area.

If a list grows regularly, one option is a dynamic named range. For a list with a header in A1 and a continuous set of entries beginning in A2 on Sheet1, create a name called ProductList and use this in Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)). If A1 is the header and A2:A6 contains five nonblank entries, the reference resolves to A2:A6. When another nonblank item is added directly below, the range expands to include it.

This dynamic example assumes there are no blank cells inside the list and that the header and list occupy column A. COUNTA counts nonempty cells in the entire column; because the header is included in that count, the ending row number matches the last entry when the list is continuous. A blank within the list can make the calculated endpoint too early, and unrelated content elsewhere in column A can make it too large. Keep the data column dedicated to the list and avoid gaps if you use this approach.

For regularly maintained data, an Excel Table is often a simpler alternative. Select the data and choose Insert > Table, making sure the header option reflects your data. Tables expand when you add rows and offer meaningful column references. They are often easier to maintain than a formula-built dynamic name, particularly when your dataset has several related columns.

Use a named range in formulas

After defining a range name, use it wherever the corresponding type of reference is accepted. The name can make a formula more descriptive, but the underlying calculation still depends on the referenced cells and the function’s rules.

  • Summarize a list: With the name Sales referring to B2:B6, use =SUM(Sales) to add those five values.
  • Find an average: With TestScores referring to C2:C11, use =AVERAGE(TestScores) to calculate the mean of the ten cells.
  • Use a named input: With DiscountRate referring to B2, use =A2*(1-DiscountRate) to calculate the price after applying the rate in B2 to the amount in A2.

Each example assumes the stated name has already been created with the specified reference. Named ranges can also be used in chart series, data validation sources, and conditional formatting formulas, although the exact entry method depends on the feature. A name that refers to a single cell is useful for a setting or assumption; a name that refers to many cells is useful for a list or calculation input.

When entering a name in a formula, Excel may show it in the autocomplete list. You can also select Formulas > Use in Formula to choose from defined names. This can help avoid spelling mistakes in a workbook with many names.

Workbook scope, worksheet scope, and name conflicts

A workbook-scoped name is available from any worksheet in the workbook. A worksheet-scoped name is available only on its designated sheet, and two different worksheets can have local names with the same spelling. This can be useful when each sheet has a separate range called Input, but it can also make a formula harder to interpret.

If a workbook-level name and a sheet-level name share a label, Excel resolves the unqualified name according to scope rules, with the local worksheet name taking precedence on its sheet. You can qualify a worksheet-level name when needed by using the sheet name and an exclamation point, such as Sheet1!Input. Before creating a name, check Name Manager for existing names to reduce accidental conflicts.

Names are stored with the workbook, not just with the visible cell. Renaming a worksheet generally updates references to it, but deleting cells or sheets can leave a name referring to an invalid location. Name Manager displays the references and lets you edit or delete names. Review it when a formula returns a reference error or when a workbook has been copied from another file.

Common problems and how to fix them

  • The name is rejected: Check for spaces, a cell-address-like name, or a leading character that is not allowed. Use an underscore instead of a space.
  • The formula says the name is not recognized: Confirm that the name exists in Name Manager, check its scope, and verify the spelling. A worksheet-level name will not necessarily be available on another sheet.
  • The result includes too many or too few cells: Inspect Refers to in Name Manager. Fixed ranges do not always grow as expected, and dynamic formulas can be affected by blank cells or unrelated data in the source column.
  • The reference shows an error: Check whether its worksheet or cells were deleted or moved. Edit the name to point to a valid location.
  • A copied formula behaves unexpectedly: Check whether the name was intended to be workbook-scoped or worksheet-scoped. Also inspect the name’s reference for absolute or relative references; relative references can behave differently depending on where the name is used.

When debugging, select Formulas > Name Manager and inspect the name, scope, and Refers to value. Selecting a name in the list can also reveal the cells it targets. Fix the definition rather than replacing the name in every formula if the same mistake affects many places.

Named ranges compared with direct references and tables

Direct cell references are compact and familiar, and they are often best for a small one-off calculation. Their weakness is readability: a formula containing several addresses can be difficult to audit later. Named ranges add meaning, though they also create another workbook object to maintain. A name is most useful when it clarifies an important input, a repeated range, or a formula used in several locations.

Tables are a strong choice for lists that change size or contain multiple fields. They automatically extend as records are added and can use column headers in formulas. Named ranges are more flexible for a single input cell, a fixed block, or a custom reference that is not naturally a table. A table can also coexist with names; they solve related but not identical problems.

Another useful option in Microsoft 365 is a name that refers to a spilled dynamic-array result. If a formula in A2 spills results into the cells below it on Sheet1, a name can refer to that spill with =Sheet1!$A$2#. The hash sign refers to the entire spill range. This is convenient when the number of results changes, but it requires a formula in the anchor cell that actually returns a spill range. Dynamic-array behavior is available in Microsoft 365 and newer Excel versions that support it.

Frequently Asked Questions

Is there an Excel function called NAMERANGE?
No. Excel does not have a worksheet function named NAMERANGE. You create a defined name in Name Manager or the Name Box, then use that name in formulas where appropriate.

How do I make a named range expand automatically?
Use an Excel Table for a growing dataset, or define a dynamic reference in Name Manager. A formula-based range needs assumptions about where the data begins and whether blanks can occur, so check the reference after changing the list structure.

Can a named range refer to one cell?
Yes. Set Refers to to the absolute reference for that cell, such as =Sheet1!$B$2. A single-cell name is often used for a rate, assumption, or other value reused in formulas.

Can a named range contain spaces?
No. Use a single word, an underscore, or mixed capitalization, such as MonthlySales or Monthly_Sales.

How do I edit or remove a named range?
Open Formulas > Name Manager, select the name, and choose Edit to change its definition or Delete to remove it. Check formulas that use the name before deleting it, since those formulas may stop working.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.