Excel Named Range in a Formula: How to Use It Correctly

Formulas & functions · Updated November 19, 2026

In Excel, use a named range in a formula by typing its name where you would normally put a cell reference—for example, =SUM(Q1Sales). First create a name that refers to the cells you want; Excel then calculates with those cells whenever the name appears in a formula.

What a Named Range Means

A named range is a label you assign to one cell, a group of cells, or—in some cases—a formula or constant. Instead of writing a reference such as B2:B20 in a formula, you can use a descriptive name such as MonthlySales. The name does not change the underlying values; it gives a reference a label that can make formulas easier to read and maintain.

Names are available throughout formulas, including functions such as SUM, AVERAGE, IF, and lookup functions. For example, the name can stand for a sales column, a tax rate, or a list of product codes. Excel recognizes a defined name when it appears in a formula and substitutes the cells or value that the name refers to.

A name is different from a worksheet tab name and from a table name. A named range is defined separately, while a table is an Excel feature with its own structured-reference syntax. You can use both in the same workbook, but they solve slightly different problems.

How to Use a Name in a Formula

Once a name exists, use it just as you would use the range reference it represents. Do not put quotation marks around the name, and do not add dollar signs to it. Excel handles the cell references stored in the name definition.

With values 120, 180, and 200 in B2:B4, and the name Q1Sales referring to B2:B4, enter =SUM(Q1Sales) in a result cell. The formula returns 500 because 120 + 180 + 200 = 500. This is equivalent to summing the cell range directly, but the name makes the purpose of the inputs clearer.

With the same Q1Sales range, =AVERAGE(Q1Sales) returns 166.6667, or about 166.67 when the result is displayed to two decimal places. A name can be passed to a function in the same way as an ordinary range reference.

You can combine a name with cell references, operators, and other names. With 80 in B2 and TaxRate referring to a cell containing 8%, =B2*TaxRate returns 6.4. The named rate makes the calculation easier to interpret than a formula containing an unexplained percentage.

Create a Named Range in Excel

In Excel for Microsoft 365 on Windows, select the cell or range to name, click the Name Box to the left of the formula bar, type a valid name, and press Enter. For example, select B2:B4, type Q1Sales in the Name Box, and press Enter. The selection is then associated with that name.

For more control, select the range and choose Formulas > Define Name. In the New Name dialog, enter the name, confirm the range in the Refers to field, and choose a scope. Click OK to save it. You can also open Formulas > Name Manager to inspect, edit, or delete names already in the workbook. On Excel for Mac, the corresponding commands are available from the Formulas tab, including Define Name and Name Manager; the precise dialog layout can differ slightly.

Before using a name in a formula, check that it refers to the intended cells. In Name Manager, the Refers to field shows the reference or expression behind a selected name. This is particularly useful when a workbook has many similarly named ranges or when a formula produces an unexpected result.

Rules for Naming Ranges

Excel applies rules to names so it can distinguish them from cell references and formula syntax. A good name is short, descriptive, and consistent with the workbook’s terminology. The main rules include:

  • A name must begin with a letter, an underscore, or a backslash. It can contain letters, numbers, periods, and underscores after that.
  • Names cannot contain spaces. Use an underscore or capitalize the start of each word, such as Annual_Total or AnnualTotal.
  • A name cannot look like a cell reference. For example, Q1 is not a valid name because it can be interpreted as a cell address.
  • Names are not case-sensitive, so Sales and SALES refer to the same name.
  • A name can be up to 255 characters, but a shorter descriptive label is usually easier to work with.

Names also cannot be used to create two distinct labels that differ only in capitalization. If a name is already used in the relevant scope, choose another one or review the existing definition in Name Manager.

Workbook Scope and Worksheet Scope

When you define a name, you can give it workbook scope or worksheet scope. A workbook-scoped name can generally be used from formulas on any worksheet in that workbook. A worksheet-scoped name is intended for use on one particular sheet. Scope matters when two different parts of a workbook need the same short label for different ranges.

If a worksheet has a local name that matches a workbook-level name, the local name takes precedence in formulas on that worksheet. This can cause confusion if a formula seems to use the wrong range. In Name Manager, check the Scope column as well as the name and its Refers to definition.

A local name can be referenced from another worksheet by qualifying it with the sheet name and an exclamation mark. For example, with a local name QuarterSales on the East sheet referring to B2:B4, a formula on another sheet can use =SUM('East'!QuarterSales). If the sheet name contains no spaces, quotes are not required, but using single quotes around sheet names is a common way to avoid problems with spaces or punctuation.

Practical Formula Examples

Named ranges are most useful when they convey what a group of values represents. The following examples show several common patterns, with the assumed data and the result for each formula made explicit.

  • With 120, 180, and 200 in B2:B4 and Q1Sales referring to B2:B4, =MAX(Q1Sales) returns 200. This can make a summary formula self-explanatory when read alongside other calculations.
  • With product codes P-101, P-204, and P-305 in A2:A4, corresponding names Cable, Adapter, and Case in B2:B4, and workbook names ProductCodes and ProductNames referring to those respective ranges, =XLOOKUP("P-204",ProductCodes,ProductNames,"Not found") returns Cable. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
  • With 50 in B2 and a workbook-level name DiscountRate referring to a cell containing 10%, =B2*(1-DiscountRate) returns 45. The calculation applies the 10% reduction to 50.

In each case, the named range is a reference to data, not a separate copy of it. If the values in the referenced cells change, a formula using the name normally recalculates based on the updated values.

Fixed Ranges, Expanding Data, and Tables

A named range that refers to a fixed address, such as B2:B20, will continue to refer to that address even as you add records below row 20. This is a common reason a total misses newly added data. You can update the range definition in Name Manager, create a name that uses a suitable dynamic reference, or convert the data to an Excel table.

Tables are often a convenient choice for lists that grow over time. When you add rows to a table, its structured references generally expand with the table. For example, with a table named SalesTable containing an Amount column with values 120, 180, and 200, =SUM(SalesTable[Amount]) returns 500. The table name and column name describe the data, and the reference can include newly added table rows.

A named range can still be useful for a fixed input, a selected set of cells, or a value reused across multiple worksheets. A table may be more practical for a growing record list. These approaches are not mutually exclusive: a workbook can use tables for transaction data and named ranges for inputs or summary calculations.

Common Problems and How to Fix Them

If Excel displays a name-related error or a formula gives an unexpected answer, first check spelling. Names are not case-sensitive, but a missing letter or extra character can make Excel treat the text as an undefined name. Autocomplete may offer existing names while you type a formula, which can help you select the intended one.

  • The name is not recognized: Open Formulas > Name Manager and confirm that the name exists in the current workbook and has the scope you need.
  • The formula uses the wrong cells: Inspect the name’s Refers to field. A name may still point to an old range after rows or columns have been reorganized.
  • The result excludes new records: Check whether the name refers to a fixed-size range. Expand it or consider a table for data that grows.
  • A name cannot be created: Check for spaces, a leading number, or a label that resembles a cell address. Use a valid alternative such as Product_Total.
  • Two definitions seem to conflict: Review each name’s scope. A worksheet-scoped name can take precedence over a workbook-scoped name on that sheet.

Names that refer to formulas rather than ordinary cell ranges can also be useful, but they need careful setup. Name Manager displays the expression in Refers to. If a workbook was copied from another file or sheets were renamed, review those definitions for references that no longer point where intended.

Named Ranges Compared with Cell References

Ordinary references such as B2:B20 are compact and familiar, and they are often the clearest choice for a short, one-off calculation. Named ranges are helpful when a reference is reused, when its purpose would otherwise be unclear, or when a workbook contains formulas that other people need to maintain. For example, Q1Sales communicates more than a bare address, provided the name accurately describes the range.

Names do not automatically make a workbook better. Too many vague names can make it harder to trace calculations, and names with unexpected scope can hide which cells a formula uses. Use them for meaningful inputs and reused ranges, and keep the definitions easy to review. For large datasets that expand, structured table references may be clearer and more resilient than manually maintained fixed ranges.

Frequently Asked Questions

Do I need to type an equal sign before a named range? Yes, when entering a formula in a worksheet cell, start the formula with an equal sign. For example, with Q1Sales referring to B2:B4, =SUM(Q1Sales) is a complete formula. The name itself does not replace the equal sign.

Can a named range refer to one cell? Yes. A name can refer to a single cell as well as multiple cells. This is useful for a shared input, such as a tax rate or target value, that appears in several formulas.

Can I use a named range on another worksheet? A workbook-scoped name can generally be used across sheets. A worksheet-scoped name is associated with its own sheet; from another sheet, qualify it with the sheet name, such as 'East'!QuarterSales.

How do I change or delete a named range? Choose Formulas > Name Manager, select the name, and use the available edit or delete controls. Review formulas that use the name before deleting it, since those formulas may no longer work as intended.

Will a named range automatically grow when I add rows? Not if it refers to a fixed range such as B2:B20. Update the definition, use a suitable expanding reference, or use an Excel table when the data is expected to grow.

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.