A named range formula example in Excel is =SUM(Sales), where the name Sales refers to a cell range such as B2:B10. To create it, select B2:B10, type Sales in the Name Box to the left of the formula bar, and press Enter; the name can then be used in formulas instead of the cell addresses.
What a named range means
A named range is a label assigned to one cell or a group of cells. Instead of referring to a range by its coordinates, such as B2:B10, a formula can refer to it by a descriptive name. The name does not change the cell values or move the data: it provides another way to identify the cells.
In Excel for Microsoft 365 on Windows, names are managed through the Name Box or the Formulas tab. A name can refer to a fixed range, a single cell, or even a formula. For example, a workbook might contain a range called Sales for monthly figures and a single-cell name called TaxRate for a rate used in calculations.
Names are especially useful when formulas are read by other people or reused across a workbook. A reference like =SUM(Sales) communicates more than a reference like =SUM(B2:B10), provided the name clearly describes what those cells contain.
A basic named range formula example
Suppose monthly sales figures are in B2:B10 and you want their total. First create the name Sales for those cells. Then, with sales values in B2:B10 and the name Sales referring to that range, enter:
=SUM(Sales)
If the values are 120, 150, 90, 180, 110, 125, 140, 160, and 115, the formula returns 1,190. The calculation is the same as summing the original cell range; the name simply makes the reference easier to understand.
A name can also point to one input cell. For example, if the tax rate is in D1 and the name TaxRate refers to D1, with a pre-tax amount in C2, use:
=C2*TaxRate
If C2 contains 200 and D1 contains 8%, the result is 16. The name makes the purpose of the reference visible in the formula. The values here are illustrative, not a statement about any particular tax rate.
How to create and check a name
The quickest way to name a contiguous range in Excel for Microsoft 365 on Windows is to select the cells, click the Name Box to the left of the formula bar, type a valid name, and press Enter. For example, select B2:B10 and enter Sales. Do not type the name into a cell; it belongs in the Name Box.
For more control, use Formulas > Define Name. In the New Name dialog, set the name, choose whether its scope is the workbook or a particular worksheet, and check the Refers to field. To define Sales for B2:B10 on a sheet named Sheet1, the Refers to field should contain:
With the sales data on Sheet1 in cells B2:B10, enter this reference in the name definition:
=Sheet1!$B$2:$B$10
The dollar signs make the row and column references absolute. For a sheet name containing spaces, Excel uses single quotation marks around the sheet name; for instance, a range on a sheet named Sales Data is written as ='Sales Data'!$B$2:$B$10.
To inspect or edit names, choose Formulas > Name Manager. In the Name Manager, review the name, scope, and Refers to field. On Windows, Ctrl+F3 opens Name Manager. On a Mac, use the Formulas tab and its Name Manager or Define Name command; ribbon placement can vary slightly by Excel version. Excel for the web has fewer name-management options than the desktop app, so use the desktop version if a particular name-management command is unavailable.
Rules for choosing a useful name
Names must follow Excel’s naming rules. In practical terms, use a descriptive word or short phrase with no spaces, and avoid anything that Excel could mistake for a cell address. Names are not case-sensitive, so Sales and sales are treated as the same name. A clear convention is more important than capitalization.
- Use underscores or periods to separate words, such as
Monthly_SalesorSales.Total; spaces are not allowed. - Start with a letter or underscore, and do not use a name that looks like a cell reference, such as
A1. - Choose a label that describes the cells’ role, not just their current location. A name such as
Salesis more durable thanColumnB. - Keep names distinct and consistent. For example, use
Sales_2026for a year-specific range if the workbook contains several years.
Names can have workbook scope or worksheet 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 a local name can take precedence over a workbook name with the same spelling when formulas are entered on that sheet. If a formula unexpectedly uses the wrong cells, check the scope in Name Manager.
Using names in common formulas
A defined name can appear wherever a cell or range reference is accepted. In these examples, the named range Sales refers to B2:B10 on Sheet1. The function works on the named cells just as it would on their addresses.
- To add the values in Sales, use
=SUM(Sales). - To calculate their average, use
=AVERAGE(Sales). - To count numeric entries, use
=COUNT(Sales).
These formulas assume Sales refers to the intended cells; their results depend on the data in that range. A name can also be used as one part of a formula. For instance, if DiscountRate refers to D1 and an item’s original price is in C2, the layout is C2 for the price and D1 for the rate. With those references, the discounted price is:
=C2*(1-DiscountRate)
If C2 is 80 and DiscountRate is 10%, the result is 72. Parentheses make clear that the rate is subtracted from 1 before the price is multiplied.
Fixed names, changing data, and Excel Tables
A standard named range such as B2:B10 remains that address even when additional records are typed immediately below it. This is a common surprise: a name is not automatically a rule that expands to include new rows. If the list grows, you may need to edit the name in Name Manager or choose a structure that expands with the data.
For a regularly growing list, an Excel Table is often simpler and less error-prone. Select the data and choose Insert > Table, making sure the header setting matches the data. Tables expand when rows are added directly below them, and their structured references identify columns by header. For example, if a table is named Table1 and has a column called Sales, its total can be written as:
With Table1 containing a Sales column, use this structured-reference formula to total that column:
=SUM(Table1[Sales])
The formula includes the table’s Sales data and typically adjusts as table rows are added or removed. A table is not identical to a named range: it has headers and table-specific behavior, while a named range can refer to any selected cells or a formula. Use a table for a growing dataset; use a name when you want a convenient label for a fixed range or an individual input.
It is possible to create a name that refers to a calculated range, but such definitions require more care than a fixed address. One older pattern uses INDEX and COUNTA, and it assumes there is a header in A1 followed by a continuous, nonblank list in A2 downward. With that layout on Sheet1, a defined name’s Refers to field can use:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
For this example, COUNTA includes the header and all populated list cells, so the final INDEX row corresponds to the last item when there are no blanks in the list. Blank cells, unrelated content elsewhere in column A, or an empty list can make this pattern return an unintended range. For most ordinary lists, a Table is easier to maintain.
Common problems and how to fix them
If Excel displays #NAME?, it may not recognize the name used in the formula. Check spelling, confirm the name exists in Formulas > Name Manager, and verify that its scope makes it visible where the formula is entered. If the name was typed into the Name Box but Enter was not pressed, it may not have been saved.
If a formula returns a plausible but incorrect number, check the Refers to field. The name may point to the wrong rows, include a header, omit recent entries, or refer to a local name instead of the workbook-level one. Absolute references are useful for fixed ranges because they do not shift if a reference is copied or the definition is edited.
Another source of confusion is relative references. A name defined using relative cell references can behave differently depending on where the formula is entered. For a straightforward range name, use absolute references in the definition, such as $B$2:$B$10, and verify the complete sheet reference in Name Manager. If deleting a worksheet or changing its structure affects a reference, revisit the name definition afterward.
Finally, names cannot contain spaces. If a proposed name is rejected, replace spaces with underscores or another allowed separator, and make sure the name does not resemble a cell address. A named range also does not make a formula automatically dynamic: the name refers to the reference or calculation specified in its definition.
When named ranges are worth using
Named ranges are most helpful when they make formulas easier to interpret, when important input cells are reused, or when a workbook has several calculations referring to the same fixed area. They can also make it easier to navigate: choose a name from the Name Box to jump to its cells.
They are less helpful when a workbook has many vaguely named ranges, when the data grows frequently but the names are fixed, or when structured table references already make the formulas clear. A name should reduce the effort needed to understand and maintain a workbook. If readers need a separate legend to decipher dozens of names, the naming system may be creating more work than it saves.
Frequently Asked Questions
What is a simple named range formula in Excel?
If Sales refers to B2:B10, =SUM(Sales) adds the values in that range. Create the name first using the Name Box or Formulas > Define Name.
How do I name a range of cells?
Select the cells, click the Name Box to the left of the formula bar, type a name without spaces, and press Enter. You can also use Formulas > Define Name to choose the name’s scope and check its reference.
Can a named range expand automatically?
A basic fixed-range name does not automatically expand when you add rows. For a list that grows, an Excel Table is usually a simpler option; otherwise, update the name definition or use a carefully designed dynamic reference.
Why does Excel show #NAME? for my named range?
Excel may not recognize the spelling, the name may not exist, or its worksheet scope may make it unavailable in that formula. Check the name and its Refers to and Scope fields in Name Manager.
Can a named range be used in functions other than SUM?
Yes. A name that refers to a range can generally be used wherever that range is accepted, including functions such as AVERAGE and COUNT. The result depends on the cells and the function’s rules.
Related
- Excel Goal Seek for Multiple Cells: How Solver Works
- Conditional Formatting Between Two Values in Excel
- Protect Sheet and Workbook in Excel: What’s the Difference?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
- Align Left Shortcut Key in Excel for Windows and MacIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, H, A, L,…
- Round Excel Without Decimals: Formula or Formatting?To round a value in Excel to the nearest whole number, use =ROUND(A1,0) when the value is in…
- Excel IFERROR With IF: How to Handle Formula ErrorsIn Excel, use IFERROR to return a chosen result when a calculation produces an error, and use IF…
- Excel FV Function Examples: How to Calculate Future ValueExcel’s FV function calculates the future value of an investment or savings plan from a fixed interest rate…
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.