To refer to a named range in an Excel formula, use its name where you would otherwise use a cell reference—for example, =SUM(QuarterSales). In Excel for Microsoft 365 on Windows, the name must already be defined in the workbook, and its scope determines where Excel can use it.
What a named range means
A named range is a label attached to one cell, a group of cells, or a formula. Instead of writing an address such as B2:B13 in a formula, you can give that range a meaningful name such as MonthlySales. The label is a reference, not a copy of the data: if a value in the named cells changes, formulas using the name recalculate as usual.
Names can make formulas easier to read and maintain, especially in workbooks with many sheets or calculations. A formula that refers to =SUM(QuarterSales) communicates its purpose more clearly than one that refers to an unexplained block of cell addresses. A name can also refer to a single cell, a fixed value, or a formula, so it is not always a multi-cell range.
Excel names are not case-sensitive: MonthlySales and monthlysales refer to the same name. Names cannot contain spaces, and they cannot be written in a way Excel interprets as a cell reference. Use underscores or periods to separate words, such as Monthly_Sales or Monthly.Sales.
Create a name before using it
You can create a name from a selected cell or range, or define it through Excel’s ribbon. These steps apply to Excel for Microsoft 365 on Windows:
- Select the cells you want the name to represent.
- Click the Name Box, the small field to the left of the formula bar that normally displays the active cell address.
- Type a valid name, such as QuarterSales, and press Enter. Do not include spaces.
For more control, select the cells and choose Formulas > Define Name. In the New Name dialog, enter the name, choose its scope, and check the Refers to field before selecting OK. The reference normally uses absolute cell addresses, such as =$B$2:$B$13, so it continues to point to the same cells when copied or used in another formula.
To review or change existing names, choose Formulas > Name Manager. There you can inspect each name’s scope and Refers to entry, edit a reference, or delete a name. Deleting a name does not delete its cells, but formulas that depended on the name may return a name error. Excel for Mac also has name-definition and name-management tools under the Formulas tab, though ribbon layout and dialog appearance can differ. Excel for the web has a different interface and may not expose every desktop name-management option.
Use the name in a formula
After the name exists, type it directly into a formula. Excel often offers matching names in formula autocomplete as you type. Names work anywhere the relevant function accepts a cell or range reference, including SUM, AVERAGE, COUNT, and many lookup formulas.
For a concrete example, suppose cells B2:B4 contain 120, 180, and 200, and that range is named QuarterSales. With those values in B2:B4 and QuarterSales referring to B2:B4, enter =SUM(QuarterSales) in an empty cell; it returns 500. The arithmetic is 120 + 180 + 200. You can also use a name for a single input: if cell F2 contains 0.08 and is named TaxRate, a formula can use TaxRate instead of F2.
With a price of 80 in B2 and a workbook-level name TaxRate referring to a cell containing 0.08, enter =B2*TaxRate in another cell; the result is 6.4. This illustrates the reference syntax, not a special tax calculation rule. Names may also be used inside longer formulas; the important point is that Excel resolves the name to the cell, range, or value specified in its definition.
A name can also appear without a function. In Microsoft 365, entering a name that refers to several cells in a worksheet formula may return or spill the referenced values into adjacent cells when the destination area is clear. To calculate one result from a multi-cell name, wrap it in a function such as SUM rather than assuming the name itself will total the range.
Named ranges versus cell references
A regular reference such as B2:B13 identifies cells by their column and row positions. A defined name identifies them by a label. The two can be used together: names are often most useful for important inputs or ranges whose purpose may not be obvious from their location, while direct cell references are convenient for short, local calculations.
Unlike a cell address, a defined name has management settings, including scope and a Refers to definition. That can be helpful when a worksheet is rearranged, but it can also create a maintenance issue if the name points to the wrong cells. Check the definition in Name Manager if a formula appears to calculate from unexpected data.
Excel tables offer another option. A table column reference, such as a structured reference to a table’s Amount column, is often a good fit for data that grows as new rows are added. A named range can refer to a table column, but it is worth asking whether the table’s own structured references are clearer for the workbook. Names are not automatically superior to either cell references or table references.
Workbook scope and worksheet scope
When you define a name, its scope decides where Excel recognizes it. A workbook-scoped name is available throughout the workbook and is usually the best choice for a value or range shared across several sheets. A worksheet-scoped name is limited to one worksheet, which can be useful when different sheets need separate names for similar local calculations.
For example, two sheets could each have a worksheet-scoped name called LocalSales, with each name pointing to a different range on its own sheet. On its own sheet, a formula can generally use LocalSales without extra qualification. To refer to a worksheet-scoped name from elsewhere, qualify it with the sheet name and an exclamation mark, as in =SUM('West Region'!LocalSales). The single quotes are needed here because the sheet name contains a space.
If a workbook-scoped name and a worksheet-scoped name share the same text, the local name takes precedence on its worksheet. This can make a formula’s meaning less obvious to someone reviewing the file. To avoid confusion, use distinct names unless there is a deliberate reason for the overlap. Name Manager’s Scope column helps identify which definition Excel will use.
Names that expand as data grows
A name that refers to a fixed address, such as B2:B13, continues to refer to that address; it does not necessarily include new records added below the range. If your source data grows, consider converting it to an Excel table using Insert > Table. Table columns expand as table rows are added, and formulas can refer to the table or its columns by their structured references.
Another option is to define a name using a formula or a dynamic-array expression, but that requires careful setup and can make a workbook harder to understand. For most lists and datasets that regularly gain rows, a table is the simpler starting point. If you inherit a workbook with a name that should grow, inspect its Refers to entry rather than assuming it is dynamic.
Names can refer to formulas or constants as well as ranges. For instance, a name could represent a fixed rate or a calculated result. In the New Name dialog, the Refers to field shows what Excel evaluates for that name. A name referring to a constant is useful for readability, but changing the underlying business assumption still requires updating the named definition or its source cell.
Common reference problems
- Excel shows #NAME? Check spelling, confirm the name exists, and verify that its scope makes it visible to the formula’s sheet. A name created only for another worksheet may not resolve as an unqualified name.
- The formula returns an unexpected result. Open Formulas > Name Manager and inspect Refers to. The name may point to the wrong range, include a header, or use a fixed address that no longer matches the data.
- A name cannot be created. Remove spaces and avoid text that resembles a cell address. Names also cannot use certain characters, such as a hyphen, as ordinary word separators; use an underscore instead.
- A copied formula changes in an unexpected way. Check whether the name refers to absolute or relative cell references. Relative names can depend on the active cell and are harder to reason about, so absolute references are usually safer for general-purpose names.
- A name was deleted or renamed. Formulas that refer to the old name may stop working. Use Name Manager to verify the current spelling and update affected formulas where needed.
For a quick check that a name exists and points where expected, click the Name Box’s drop-down arrow and choose the name to select its reference. You can also choose Home > Find & Select > Go To, enter a defined name, and select OK. This navigates to the range; it does not insert the name into a formula.
Good practices for readable formulas
Choose short names that describe what the range contains or how it is used. Keep a consistent style, such as TaxRate or ProductList, and avoid names that require readers to guess their meaning. If a workbook has many names, review them periodically in Name Manager and remove obsolete definitions only after checking that formulas, charts, and other workbook features do not rely on them.
Use workbook scope for shared values and ranges, and worksheet scope only when local definitions are genuinely useful. For a large, changing dataset, consider a table rather than manually extending a fixed named range. Finally, make sure the name points to the intended cells before relying on it in a critical calculation: a readable name improves clarity, but it does not verify the underlying data.
Frequently Asked Questions
How do I use a named range in an Excel formula?
Type the name in place of a cell or range reference, such as =SUM(QuarterSales). The name must be defined, spelled correctly, and in scope for the worksheet containing the formula.
How do I find a named range in Excel?
Open the Name Box drop-down beside the formula bar and select the name, or use Home > Find & Select > Go To. Excel selects the cells the name refers to.
Can a named range be used on another worksheet?
Yes, if it has workbook scope. A worksheet-scoped name is local; from another sheet, qualify it with the sheet name and an exclamation mark, for example 'West Region'!LocalSales.
Why does Excel return #NAME? for a named range?
The name may be misspelled, undefined, or outside the formula sheet’s scope. Check its exact spelling and scope in Formulas > Name Manager.
Will a named range include new rows automatically?
Not if it refers only to a fixed address that excludes those rows. For a dataset that grows, an Excel table and its structured references are often easier to maintain.
Related
- Excel Power Query Value Transformation: Types and Steps
- Excel Power Query Training: Skills, Courses, and Practice
- Excel Power Query Value Replacement: How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Flash Fill vs AutoFill in Excel: What’s Different?Flash Fill recognizes a pattern in example data and fills in similar text, while AutoFill copies cells or…
- Open Excel Files in the Desktop App From Your BrowserTo open a spreadsheet that is already open in Excel for the web, choose Open in Desktop App…
- How to Insert a Line Under a Header in Microsoft WordTo add a line beneath a heading in Word, click in the heading paragraph, then choose Home >…
- Mail Merge in Word: How It Works and How to Use ItMail merge in Word combines a document template with a list of information—such as names and addresses—to create…
- Audit Checklist Excel: Build and Use a Reliable TemplateYes—an Excel audit checklist can organize audit questions, evidence, results, owners, due dates, and corrective actions in one…
- How to Clean Up an Excel Spreadsheet: Practical StepsTo clean up an Excel spreadsheet, first save a copy, then make the data consistent: use one header…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.