A named range gives a cell or a group of cells a short, readable name, such as TaxRate or Sales. Instead of writing =B2*Sheet2!$C$5 you write =B2*TaxRate, which is easier to read, easier to check and harder to break. Named ranges also make formulas easier to copy, since the name always points to the same cells, and they make drop-down lists and charts simpler to manage. This guide shows three ways to create a name, how to use it in formulas, how to manage names in the Name Manager, the rules for valid names, and the mistakes to avoid. A small price example with numbers you can check by hand is used throughout.
Updated November 2026
In this guide
- Why use named ranges
- Example data
- Method 1: the Name Box
- Method 2: Define Name
- Method 3: Create from Selection
- Use a name in a formula
- Manage names in Name Manager
- Rules for valid names
- Dynamic named ranges with tables
- Names in drop-down lists
- Names across sheets
- Names with lookup formulas
- Common mistakes
- Practice problems
- Quick recap
Why use named ranges
Names make a formula describe itself. A reader sees =Price*Quantity and understands it at once, while =C2*D2 needs a trip to the headers. Names are absolute by default, so they do not shift when you copy a formula. You can use one name in many formulas and change the cells it refers to in one place. They also work across sheets, so you do not need to type the sheet name. For a model with assumptions such as a tax rate or a discount, a named cell is much safer than a reference that someone might overwrite by accident.
Example data
Put a tax rate of 8 percent in cell F1 and a discount of 10 percent in F2. In column B list prices of 50, 80 and 120. We will name F1 as TaxRate and F2 as Discount, and name the prices B2:B4 as Prices. The sum of the prices is 50 plus 80 plus 120, which is 250. A tax of 8 percent on 250 is 20, so the total with tax is 270. A discount of 10 percent on 250 is 25, so the discounted total is 225. Use these numbers to check each formula that follows.
Method 1: the Name Box
The Name Box is the small field at the left of the formula bar. Select the cell F1, click in the Name Box, type TaxRate and press Enter. The name is created at once. Do the same for F2 with the name Discount. For a range, select B2:B4, type Prices in the Name Box and press Enter. To jump to a named range later, open the drop-down arrow of the Name Box and choose the name, and Excel selects those cells. This is the fastest method and it is the one most people use for one-off names.
Method 2: Define Name
Select the cells, go to the Formulas tab and choose Define Name. A dialog opens with the name, the scope and the cells it refers to. Type the name, choose the scope, which is the whole Workbook or one sheet, and check the Refers to box. Click OK. This method is useful when you need to set the scope to a single sheet, add a comment, or type a formula that the name refers to. Use workbook scope for most names, so they work on every sheet.
Method 3: Create from Selection
If your data has headers, Excel can create names from them. Select the headers and the data, for example A1:C4, go to Formulas, then Create from Selection, tick Top row and click OK. Excel creates one name for each column, using the header text. A header such as Unit Price becomes Unit_Price, because names cannot contain spaces. This is the fastest way to name many columns at once, and it keeps names in line with your headers.
Use a name in a formula
Type an equals sign and then the name, or choose it from the list that appears while you type. To get the total with tax, use =SUM(Prices)*(1+TaxRate). That gives 250 times 1.08, which is 270, so the result matches our check. For the discounted total use =SUM(Prices)*(1-Discount), which gives 250 times 0.9, or 225. You can also press F3 to paste a list of names into a formula. Our SUMIFS guide shows how names make criteria ranges easier to read.
Manage names in Name Manager
Press Ctrl+F3 or go to Formulas, Name Manager. The list shows each name, its current value, the cells it refers to, the scope and any comment. Select a name and click Edit to change the name or the cells. Click Delete to remove it. If you delete a name that a formula uses, the formula shows a #NAME? error, so check where a name is used before you delete it. Use the Filter button to show only names with errors, which helps when you inherit a workbook with broken names.
Rules for valid names
A name must start with a letter, an underscore or a backslash. It can contain letters, numbers, periods and underscores, but no spaces. It cannot look like a cell reference, so Q1 and AB12 are not allowed, and the single letters R and C are not allowed either. Names are not case sensitive, so TaxRate and taxrate are the same. A name can be up to 255 characters, but short names are easier to use. Use a clear pattern, such as tax_rate or TaxRate, and keep it the same across the workbook.
Dynamic named ranges with tables
A fixed name such as Prices stays at B2:B4 even if you add a fourth price. To make a range that grows, convert the data to a table with Ctrl+T and use structured references, such as SalesData[Price], which expand automatically. A table column acts like a dynamic named range. You can also create a name that refers to a table column, so Prices refers to =SalesData[Price]. Our slicer guide shows how tables help with filtering as well.
Names in drop-down lists
A named range is a clean source for a drop-down list. Name the list of items, for example Regions, then in Data Validation choose List and type =Regions in the Source box. If you move the list to another sheet, the drop-down still works, because the name keeps pointing to it. Update the name in the Name Manager if the list grows. Our drop-down list guide covers the full steps.
Names across sheets
A workbook-scoped name works from any sheet without a sheet prefix. If TaxRate sits on a sheet called Settings, a formula on the Orders sheet can use =B2*TaxRate and does not need Settings!$F$1. This keeps assumptions in one place. If you copy a sheet that holds names, Excel may create sheet-scoped copies of them, which can cause confusion, so open the Name Manager afterwards and check the scope column. Keep one Settings sheet for rates, limits and lists, and name each cell there.
Names with lookup formulas
Names make lookup formulas shorter. If you name a product table column Products and a price column Prices, you can write =XLOOKUP(E2,Products,Prices) instead of listing the cell ranges. The formula reads like a sentence, and if the table moves you update the name once. For example, if Products holds Pen, Book and Bag with prices 5, 12 and 20, then looking up Book returns 12. Names also help when the same lookup range is used by dozens of formulas.
Common mistakes
The most common mistake is to use a name that looks like a cell, such as Q1, which Excel refuses. Another is to leave spaces in the name. A third is to create the same name with sheet scope on several sheets and then get a different result on each sheet. A fourth is to delete a name that formulas still use, which causes #NAME? errors. A fifth is to forget that a name refers to fixed cells, so inserting rows outside the range will not extend it. Check the Name Manager from time to time and remove names you no longer use.
Practice problems
Try these. Problem 1: name F1 as TaxRate and check that =100*TaxRate gives 8. Problem 2: name B2:B4 as Prices and check that =SUM(Prices) gives 250. Problem 3: write a formula for the total with tax and confirm that it gives 270. Problem 4: write a formula for the discounted total and confirm that it gives 225. Problem 5: create names from a table with headers using Create from Selection, then open the Name Manager and check the list. Problem 6: change the tax rate to 10 percent and check that the total becomes 275.
Quick recap
Select the cells, type a name in the Name Box and press Enter. Use Define Name when you need to set the scope, and Create from Selection to name many columns from their headers. Use names in formulas such as =SUM(Prices)*(1+TaxRate), and manage them in the Name Manager with Ctrl+F3. Follow the naming rules, avoid spaces and cell-like names, and use tables when the data will grow. With clear names, your formulas become easier to read and to maintain, and your workbook is less likely to break when somebody edits it.
Frequently asked questions
How do I create a named range in Excel?
Select the cells, type a name in the Name Box and press Enter.
How do I see all named ranges?
Press Ctrl+F3 to open the Name Manager.
Why can a name not contain spaces?
Spaces separate parts of a formula, so use an underscore instead.
How do I delete a named range?
Open the Name Manager, select the name and click Delete.
Can a named range grow automatically?
Yes, if you base it on an Excel table column.


