Excel Named Range Formulas: Uses, Setup, and Examples

In Excel, a named range lets you use a meaningful name in a formula instead of a cell address: if the range B2:B5 is named Sales, =SUM(Sales) adds those cells. Define the name first, then use it anywhere a cell or range reference is accepted in a formula.

What a named range means

A named range is a label that points to a cell, a group of cells, or—in some cases—a calculated value. For example, Sales might refer to B2:B5, while TaxRate might refer to a single cell containing a percentage. The name acts as a reference: it does not copy the cells, change their values, or create a separate calculation by itself.

Without a name, a formula such as =SUM(B2:B5) tells you which cells Excel adds, but not what those cells represent. A formula such as =SUM(Sales) communicates the purpose more clearly. This is especially useful when a workbook has several sheets, long formulas, or ranges that are reused in many calculations.

Excel names are not case-sensitive, so Sales and SALES refer to the same name. A name cannot contain spaces, and it must follow Excel’s naming rules; for instance, a name cannot be written like a cell address such as A1. Use a short, descriptive label, and use an underscore if you need to separate words, as in Net_Sales.

How to create a named range

For a simple range in Excel for Microsoft 365 on Windows, select the cells, click the Name Box to the left of the formula bar, type a name, and press Enter. For example, select B2:B5, type Sales in the Name Box, and press Enter. The name now refers to the selected cells.

You can also create or inspect names through Formulas > Define Name, or open Formulas > Name Manager to review existing names. In the New Name dialog, enter the name, choose its scope, and check the Refers to field to confirm the address. For the example above, the field should point to the intended sheet and cells, typically using absolute references such as =Sheet1!$B$2:$B$5.

The Name Box is quick for a basic range; Name Manager is better when you need to choose a scope, edit a name, or create a name that refers to a formula. On Mac, Excel offers the same core naming features, though the ribbon and dialogs can look different. If you use Excel for the web, available name-management options may differ from the desktop app; check the current interface before relying on a desktop-only workflow.

Using names in everyday formulas

After defining Sales as B2:B5, you can use it inside common functions. With values 120, 150, 90, and 140 in B2:B5, the total is 500:

With Sales referring to B2:B5 and those four values in the cells, enter =SUM(Sales). The result is 500.

You can also calculate an average from the same range. With Sales still referring to B2:B5, enter =AVERAGE(Sales). The result is 125.

A name can be combined with other references and values, too. Suppose TaxRate is a separate name referring to a cell containing 8%. With Sales referring to B2:B5 and totaling 500, enter =SUM(Sales)*TaxRate. The result is 40, which is 8% of 500. This illustrates the main advantage: the formula describes the calculation without requiring the reader to interpret several cell coordinates.

Names can also be arguments in conditional functions. Suppose Region refers to C2:C5, where the entries are West, East, West, and North, and Revenue refers to D2:D5, where the corresponding values are 100, 80, 120, and 60. Enter =SUMIFS(Revenue,Region,"West"). The result is 220 because it adds the revenue values for the two rows marked West. For the formula to work as intended, the criteria range and sum range need matching dimensions.

Names that refer to a value or calculation

A name does not have to represent a block of cells. In Name Manager, you can create a name whose Refers to field contains a constant or a formula. For example, a name called TaxRate could refer to the constant =8%. Then a formula that uses TaxRate can be adjusted centrally by editing the name, rather than changing the rate in several formulas.

A formula-based name can also represent a repeated calculation. For example, a name might refer to a formula that calculates a target or threshold. This can make complex worksheet formulas easier to read, but it also adds another place to investigate when a result is unexpected. Document what the name represents, and check its Refers to field in Name Manager when reviewing a workbook.

When a name refers to a formula, the formula is evaluated in the context of the workbook and the name’s scope. Some functions that return arrays can behave differently depending on where and how the name is used. For important calculations, test the name in a visible worksheet formula before relying on it throughout a workbook.

Workbook scope and worksheet scope

Excel names have scope. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available only on the sheet where it was created, and it can take precedence over a workbook-scoped name with the same spelling when a formula is entered on that sheet.

For a value used in several sheets, workbook scope is usually the clearer choice. A local name can be useful when the same label has a different meaning on separate sheets, but it can make formulas harder to audit. If a formula appears to use the wrong range, look in Name Manager for duplicate names and compare their scopes, not just their labels.

Names are also easier to maintain when their meaning is consistent. Avoid using a familiar label such as Total for unrelated calculations on different sheets unless you deliberately need separate local names. If you inherit a workbook, inspect the scope and Refers to fields before changing formulas based on a name.

Fixed ranges, expanding data, and tables

A name defined as B2:B5 normally refers to those four cells. If you type new data in B6, the named range does not necessarily expand to include it. A total based on Sales may therefore omit new rows if the name still points only to B2:B5. This is one of the most common surprises with named ranges.

For data that grows over time, an Excel Table is often easier to maintain than a manually fixed range. Select the data and choose Insert > Table, or use Home > Format as Table. When you add rows directly to the table, its data range expands, and table formulas can refer to columns by their headers. A table is not the same thing as a named range, but it can be a better fit when the main need is a growing list of records.

If you keep a named range, update its reference when the data grows, or use a deliberately designed dynamic reference. Dynamic names can be more difficult to understand and troubleshoot than a table. Also consider blank rows, headers, and whether new data will always be added in the expected location before relying on a range that calculates its own endpoint.

Advantages and trade-offs

Named ranges can make formulas easier to read, reduce repeated cell-address interpretation, and centralize a frequently reused input such as a rate or target. They are particularly useful in models where one value feeds calculations on multiple sheets. A descriptive name can also help someone reviewing the workbook understand what a formula is intended to calculate.

The trade-off is that names add a layer between a formula and its cells. A reader may need to open Name Manager to find what Sales actually includes. Names can become stale when data moves or expands, and duplicated sheet-level names can cause confusion. Very short or generic labels may make a workbook less clear rather than more clear.

Use names where they improve meaning, not automatically for every cell. Naming a single input such as TaxRate can be helpful; giving every cell in a large grid a custom name is often harder to manage than ordinary references or table references. Keep names consistent and make their definitions easy to verify.

Common problems and how to fix them

Name not recognized: Check the spelling, whether the name exists in Name Manager, and whether it is scoped to the current sheet or workbook.
Wrong total after adding rows: Inspect the name’s Refers to field. A fixed range may not include the new cells; expand it or consider converting the data to a Table.
Unexpected result on one sheet: Look for a worksheet-scoped name that shares its label with a workbook-scoped name.
Name cannot be created: Remove spaces, avoid a label that resembles a cell address, and start with a valid character such as a letter or underscore.
Formula seems correct but omits records: Check the exact start and end cells, hidden gaps, headers, and whether the criteria and sum ranges cover corresponding rows.

When troubleshooting, open Formulas > Name Manager and select the name. Review both Scope and Refers to, then confirm that the referenced cells contain the data you expect. Editing the name definition can change every formula that uses it, so check the workbook’s dependent calculations after making a change.

Frequently Asked Questions

What is an example of a named range formula in Excel?
If Sales refers to B2:B5, =SUM(Sales) adds the values in those cells. The name must be defined before you use it.

Do named ranges update automatically when I add rows?
Not necessarily. A name that refers to a fixed address usually continues to refer to that address. For a list that grows, consider using an Excel Table or update the named range deliberately.

Can a named range refer to a formula instead of cells?
Yes. In Formulas > Name Manager, create or edit a name and enter a constant or formula in Refers to. Test the resulting name in a worksheet formula so you can confirm it behaves as intended.

Can two Excel names have the same label?
A workbook-scoped name and a worksheet-scoped name can share a label. The local name can take precedence on its own sheet, so check scope if the same formula behaves differently across sheets.

Are named ranges better than cell references?
They can make a formula’s purpose clearer, but they are not always better. Use them when the label adds useful meaning or when a reference is reused; use tables for growing records and ordinary references when names would add needless complexity.

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.