Yes. In Excel for Microsoft 365, type a defined name into a formula anywhere you would otherwise use its cell reference or range address. For example, a name such as Sales can make a formula easier to read, check, and update than a reference such as B2:B20.
What a named range means in Excel
A named range is a label attached to a cell, a range of cells, a formula, or a constant. Instead of remembering that B2:B20 contains sales figures, you can give that range the name Sales and use the name in formulas. Names are workbook features, not just labels displayed beside cells, so Excel can resolve them when it calculates a formula.
Names can also describe one cell, such as TaxRate, or a value or calculation that is not stored in a worksheet cell. This makes them useful for inputs that appear repeatedly, such as a tax rate, a discount threshold, or the start and end dates of a reporting period. A name does not change the underlying data: it provides a reference to it.
Excel names are not case-sensitive, so Sales and SALES refer to the same name. A name can make a formula more understandable, but it does not automatically explain the business meaning of the data. Choose specific names and, where needed, document assumptions separately.
How to create a name
For a simple range, select the cells, click the Name Box to the left of the formula bar, type a name, and press Enter. For example, select B2:B20 and enter Sales. The Name Box normally displays the active cell address; typing a name there assigns it to the selected range. Avoid spaces in names: use an underscore, as in North_Region, or capitalize word starts, as in NorthRegion.
For more control in Excel for Microsoft 365 on Windows, 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. To inspect or change existing names, use Formulas > Name Manager. Excel for Mac has corresponding commands on the Formulas tab; ribbon placement can differ slightly by window size and Excel build.
Names must follow Excel’s naming rules. They cannot contain spaces, cannot be written like a cell reference such as A1, and must begin with a letter, underscore, or backslash. Later characters can include letters, numbers, periods, and underscores. In practice, use a descriptive name that starts with a letter and avoids punctuation to make it easy to read and maintain.
Using a name in a formula
Once a range has a name, use it as the range argument to a function just as you would use the address. With the sales values in B2:B6 and that range named Sales, enter =SUM(Sales) to add the values. If B2:B6 contains 120, 85, 140, 95, and 60, the result is 500. The formula stays readable even if a colleague does not know where the sales figures sit on the sheet.
Names also work as references in calculations. With a unit price of 50 in B2 and a named cell TaxRate containing 0.08, enter =B2*(1+TaxRate) to calculate a price with an 8% tax rate; the result is 54. This assumes the rate is stored as 8%, or 0.08, rather than the whole number 8. Naming a key input can make the intent clearer, while the formula still depends on the value and format stored in that cell.
Most formulas that accept a range can use a named range. With department scores in C2:C6 and those cells named Scores, enter =AVERAGE(Scores) to calculate their mean. The name is a substitute for the range argument, not a special function, so normal function rules still apply. A name for a whole range can also be used in formulas that compare, count, or summarize its contents.
Names in conditional and multi-range calculations
Named ranges are particularly helpful when a formula uses several inputs. With region labels in C2:C6, that range named Regions, and the text West appearing three times, enter =COUNTIF(Regions,"West"). The result is 3. The name identifies which cells Excel should check, while the quoted text is the criterion. The criterion still needs quotation marks because it is text.
For a calculation using more than one range, names can make each argument easier to identify. With sales amounts in B2:B6 named Sales, and matching region labels in C2:C6 named Regions, enter =SUMIFS(Sales,Regions,"West"). If the West rows contain 120, 140, and 60, the result is 320. Each named range must cover the same corresponding rows for this example to return the intended total.
Names can also be used in logical tests. With a threshold stored in D1 and named Target, and the value to test in B2, enter =IF(B2>=Target,"Met","Not met"). If B2 is 75 and Target is 70, the result is Met. Here the name stands for a single input cell; the words returned by the formula are text and therefore appear in straight double quotes.
Workbook scope and worksheet scope
When you create a name, its scope determines where Excel recognizes it. A workbook-scoped name can be used from any worksheet in that workbook. A worksheet-scoped name belongs to one sheet, which is useful when separate sheets use the same label for different ranges. The scope appears in Name Manager and is selected in the Define Name dialog.
If a worksheet-scoped name and a workbook-scoped name have the same spelling, a formula on that worksheet resolves the local name in preference to the workbook name. That can produce a surprising result when someone copies a formula or creates a similarly named local definition. If a formula needs a particular sheet-level name, qualify it with the sheet name and an exclamation mark, as in Sheet2!LocalSales. When a sheet name contains spaces, Excel uses apostrophes around it, as in 'Quarter 1'!LocalSales.
For most names intended for general use, workbook scope is the simplest choice. Use worksheet scope deliberately, such as for a repeated template where each sheet should calculate from its own local inputs. Before adding a name to an established workbook, check Name Manager for existing names and scopes so you do not create a conflict or change the meaning of a formula.
Fixed ranges, expanding data, and Excel tables
A named range refers to the cells specified in its definition; it does not necessarily expand just because you type new data beneath those cells. If Sales refers to B2:B20 and you add a value in B21, a formula using Sales may still exclude B21. Check the name’s Refers to definition in Name Manager when new rows are missing from a result.
For regularly growing lists or transaction data, an Excel table is often more convenient than a manually maintained range name. Select the data and choose Insert > Table, then use the table’s structured references in formulas. Tables expand as rows are added and their column references describe the data by heading. A named range remains useful for a fixed block or a single input; a table is often a better fit for records that grow over time. The two approaches can coexist in one workbook.
Another option is a name that refers to a formula rather than a fixed block, but these definitions require careful design and testing. For ordinary lists, avoid making a dynamic definition more complicated than necessary: a table is easier for many users to understand and maintain. Whichever method you choose, verify that added rows are included in downstream totals and reports.
Common problems and how to fix them
- The name is not recognized: Check its spelling and scope in Formulas > Name Manager. A worksheet-scoped name may not be available from another worksheet.
- The total omits new rows: Inspect the name’s Refers to range. A fixed name will not always include data entered below its last cell; extend it or use a table where appropriate.
- A formula returns an unexpected value: Look for both a local and a workbook-scoped name with the same spelling. The local definition can take precedence on its worksheet.
- Excel rejects the name: Remove spaces, avoid a cell-reference-like name such as A1, and begin with a permitted character. Use an underscore instead of a space.
- A copied formula behaves unexpectedly: Check whether the name refers to absolute or relative cell references. Relative references in a defined name can depend on the formula’s position, so absolute references are generally safer for reusable named ranges.
Names can also become difficult to maintain if they are vague, duplicated across scopes, or left behind after their source cells are deleted. Use Formulas > Name Manager to review definitions and their Refers to fields. Before deleting a name, search formulas or inspect its use: formulas that depend on a deleted name can show a #NAME? error.
When named ranges are worth using
Use a name when it makes a formula clearer or gives a frequently used input a meaningful identity. Typical examples include a fixed list used in multiple calculations, an assumptions cell such as an annual rate, or a range used by several summary formulas. Names can also make a model easier to review because a reader can infer the role of an argument from the formula.
They are less helpful when every small range receives a name without a consistent convention. A cell reference may be more direct for a one-off calculation, while a table reference may communicate a changing dataset better. Consider how often a range changes, how many formulas use it, and whether another person will understand the name. Clear naming and a current definition matter more than maximizing the number of names.
Frequently Asked Questions
Can I use a named range inside any Excel formula?
Usually, yes: use it wherever the function or operator accepts the kind of reference the name represents. A range name can supply a range argument, while a name for one cell or a constant can be used as a single value. The formula’s normal rules still apply.
Do named ranges update when I add rows?
Not automatically in every setup. A fixed named range continues to refer to its defined cells unless its definition is changed. For data that grows, consider an Excel table or revise the name so it covers the added rows.
Can two sheets have a named range with the same name?
Yes, if the names have worksheet scope and belong to different sheets. A workbook-scoped name can also share that spelling, but the local name takes precedence in a formula on its own worksheet. Check scope carefully to avoid confusion.
How do I see what cells a name refers to?
In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager and inspect the Refers to column. Selecting a name lets you review or edit its definition. The Name Box can also be used to jump to a named range.
Are named ranges the same as table references?
No. A named range is a label for a defined reference, formula, or value. A table is a structured data feature with named columns and rows that can expand as records are added. Use the one that suits the shape and maintenance needs of your data.
Related
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEN Formula Examples: Count Text in CellsIn Excel for Microsoft 365, use =LEN(A2) to count the characters in cell A2, including spaces and punctuation.…
- Excel Trim a Table: Clean Text or Remove Rows?To trim text inside an Excel table, add a column with =TRIM([@[Raw Name]]); to trim the table’s physical…
- Rank Names in Excel: Formulas and Tie RulesIf names are in A2:A20 and their scores are in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0) in C2 and fill it…
- SUMIF in Excel: Count Values or Sum Them CorrectlySUMIF adds numbers that meet a condition; it does not count matching cells. To count matches, use COUNTIF…
- Excel Table Filter Multiple Columns: How It WorksYes. In an Excel table, you can filter multiple columns at the same time by choosing a filter…
- IFERROR to Return Zero in Excel: Formula and UsesTo make an Excel formula return zero when it produces an error, wrap it in IFERROR. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.