Excel Named Ranges in Formulas: How to Use Them

Formulas & functions · Updated November 19, 2026

Yes. Excel lets you use a named range in a formula just like a cell reference: if the name Sales refers to cells B2:B13, enter =SUM(Sales) to total those cells. In this example, if the twelve sales values add up to 1,410, the formula returns 1,410; the name makes the formula easier to read without changing its calculation.

What a Named Range Means

A named range is a label assigned to a cell, a group of cells, or—in some cases—a formula. Instead of remembering that a particular set of sales figures is in B2:B13, you can call that range Sales. The name is a reference to the cells, not a duplicate of their contents: changing a value in the range changes the result of formulas that use the name.

Names can represent a single cell, a rectangular block, or multiple areas. For example, a name might identify one tax-rate cell or a whole column of monthly figures. You can also define a name for a constant or calculation, which is useful for values that recur throughout a workbook. Named ranges are part of the workbook’s defined names, which you can inspect and manage in Excel.

Names are not case-sensitive, so Excel treats Sales and sales as the same name. A name cannot contain spaces, and it must not look like a cell reference such as B2. Use letters, numbers, underscores, or periods in a clear label such as Monthly_Sales or TaxRate.

Create a Named Range

In Excel for Microsoft 365 on Windows, select the cells you want to name. Click the Name Box—the small box to the left of the formula bar—type a name, and press Enter. For example, selecting B2:B13 and entering Sales creates a name for those cells. The Name Box can also be used to jump to a name later: open its drop-down list and choose the name.

You can create a name through the ribbon as well:

  1. Select the cell or range to name.
  2. Choose Formulas > Defined Names > Define Name.
  3. Enter the name, check the workbook or worksheet scope, and confirm the Refers to range.
  4. Select OK. The name is now available in formulas within its scope.

When defining a name for B2:B13 on a worksheet called Sheet1, Excel’s Refers to box can contain =Sheet1!$B$2:$B$13. The dollar signs make the cell addresses absolute, so they do not shift when a formula is copied. When the sheet name contains spaces, Excel encloses it in apostrophes, as in ='Monthly Data'!$B$2:$B$13.

Another option is to create names from labels already in a table of data. Select the labels and the corresponding values, then choose Formulas > Defined Names > Create from Selection. In the dialog, indicate whether the labels are in the top row, left column, bottom row, or right column. Excel uses the selected labels as names for the associated cells. Review the results afterward, especially if a label contains spaces or punctuation that Excel must adapt.

Use Names in Common Formulas

Once a name exists, use it wherever a range reference is accepted. For a range named Sales that refers to B2:B13, enter =AVERAGE(Sales) to calculate the arithmetic mean of the twelve values. If their total is 1,410, the average is 117.5. The name can also be combined with other references and operators.

Suppose a worksheet has sales amounts in B2:B13 and a commission rate in D1. If the name CommissionRate refers to D1, use =B2*CommissionRate in E2 to calculate commission on the first sale. With B2 equal to 200 and D1 equal to 5%, E2 returns 10. Copying the formula down adjusts the relative reference B2 to B3, while the named rate continues to refer to the cell assigned to CommissionRate.

Names can be combined with ordinary ranges, other names, and functions. For example, if WestSales refers to a set of sales values and EastSales refers to another set, you can add their totals. The name itself does not have to be the entire argument: Excel accepts it anywhere a compatible reference or value can appear.

  • Use a named range in a calculation, such as a sum or average.
  • Use a single-cell name as an input, such as a rate, threshold, or reporting date.
  • Use a name in a function argument where you would otherwise enter a cell range.
  • Combine a name with a normal cell reference when only part of a formula needs a descriptive label.

Understand Workbook and Worksheet Scope

Every defined name has a scope. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available only on the sheet where it was created, unless another sheet refers to it with the sheet name and an exclamation mark.

For example, if a worksheet named Inputs contains a worksheet-scoped name TaxRate, a formula on that same sheet can use the name by itself. On a different sheet, qualify the name as Inputs!TaxRate. If a workbook-scoped name and a local name have the same spelling, the local name takes precedence in a formula entered on that worksheet. This can make a formula behave differently from the same-looking formula on another sheet.

Choose workbook scope for a value or range intended to be shared across sheets, such as a company-wide rate or a master list. Worksheet scope can be helpful when separate sheets use the same label for different local data, such as Target on each department sheet. If formulas are difficult to interpret, check the scope in Name Manager before changing the formula.

Names, Tables, and Changing Data

A standard named range usually refers to the specific cells selected when the name was created. If you add rows below that range, Excel may not include those new rows in the name. This is a common reason a total stops reflecting new entries: the formula is still working, but the referenced range has not expanded.

For data that grows over time, an Excel Table is often a more convenient alternative. Select the data and choose Insert > Table, or press Ctrl+T on Windows; on Mac, the common shortcut is Command+T. Tables expand as data is added, and formulas can refer to a table column by its header. For a table named SalesTable with a column titled Amount, the formula =SUM(SalesTable[Amount]) totals that column. This is a structured reference, not a defined name, although it serves a similar readability purpose.

Named ranges can also refer to formulas rather than just selected cells. This can centralize a repeated calculation or constant, but a name defined by a formula is not automatically a growing list of data. If you need a range to resize dynamically, consider an Excel Table or, in a Microsoft 365 workbook, a formula that returns a changing array. Choose the method that fits how the data is maintained, rather than assuming every named range expands by itself.

Review and Edit Names

To inspect names in Excel for Microsoft 365 on Windows, choose Formulas > Defined Names > Name Manager. The manager lists each name, its value or reference, its scope, and any comment. Select a name to edit its definition or delete it. Use care when deleting a name: formulas that rely on it can return a #NAME? error.

For an existing name, editing the Refers to field can point it to a different range without rewriting every formula that uses the name. This is useful when a data block moves or changes size. Before making a change, check the scope and confirm that the new reference includes the intended cells. A name that refers to the wrong range can produce a plausible but incorrect result, which may be harder to notice than an obvious error.

Excel for Mac also provides name management through the Formulas tab and Name Manager, though ribbon placement and dialog appearance can differ slightly by release. Excel for the web can work with named ranges, but the available name-management controls may differ from the desktop application. If you cannot find a particular command in the web interface, open the workbook in the desktop version to manage its defined names.

Common Problems and How to Fix Them

  • #NAME? appears: Check the spelling, confirm that the name exists, and verify that it is in scope for the formula’s worksheet. A deleted name or a mistyped label can cause this error.
  • The result excludes new rows: Inspect the name’s Refers to range in Name Manager. Expand the range or use a Table if entries are added regularly.
  • The same formula gives different results on two sheets: Look for worksheet-scoped names that share a label with a workbook-scoped name. Qualifying the intended local name can remove ambiguity.
  • The name will not save: Remove spaces, avoid a label that resembles a cell address, and check for an existing name with the same scope. Names are case-insensitive.
  • A formula is hard to copy: Remember that a name’s reference is defined separately from the formula. Check whether the name points to the intended fixed cells before relying on it in copied formulas.

One misconception is that replacing a cell reference with a name changes how Excel calculates the formula. It does not: =SUM(B2:B13) and =SUM(Sales) return the same result when Sales refers to B2:B13. The difference is readability and maintainability. Names can make formulas easier to understand, but they also add labels that a reader must locate and interpret.

When Named Ranges Are Most Useful

Use names when they clarify a formula or make a repeated input easier to manage. A name such as AnnualTarget is more informative than an unexplained reference such as F4, particularly in a formula reviewed months later. Names can also simplify models in which the same assumption appears in several formulas: change the cell assigned to the name, and formulas using that name continue to point to the defined input.

Keep names concise, specific, and consistent. Prefer ShippingRate over a vague label such as Rate when a workbook contains several rates. Avoid creating names for every individual cell; excessive labels can clutter Name Manager and make maintenance harder. A good test is whether the name helps a reader understand the formula or identify an important input.

Frequently Asked Questions

Can I use a named range inside a formula?
Yes. Use the name in the position where the corresponding cell or range reference would go. For example, if Sales refers to B2:B13, =SUM(Sales) totals that range.

Do named ranges update automatically when I add rows?
Not necessarily. A name that refers to a fixed range may continue to cover only the original cells. Check its Refers to setting, or use an Excel Table when new records are routinely appended.

Can I use a named range on another worksheet?
A workbook-scoped name can be used across worksheets. A worksheet-scoped name is local; to refer to it from another sheet, qualify it with its sheet name, such as Inputs!TaxRate.

How do I see all named ranges in Excel?
In Excel for Microsoft 365 on Windows, choose Formulas > Defined Names > Name Manager. It shows the names, their references, and their scopes, and lets you edit or delete them.

Are named ranges the same as Excel Table references?
No. A named range is a defined label for cells or a formula. A Table reference, such as SalesTable[Amount], identifies data through an Excel Table’s name and column header. Both can make formulas easier to read, but Tables are designed to grow with added records.

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.