Named Ranges in Excel: Clearer Formulas
By ExcelReady TeamUpdated 5 min read
The short answer
A named range is a name, like TaxRate or Sales, that stands for a cell or range. To create one, select the cells, click the Name Box, type the name and press Enter. Then use it in formulas, such as =SUM(Sales) or =B2*TaxRate. Names are absolute by default, so they don't move when you copy a formula.
Key takeaways
- Select the cells, type a name in the Name Box and press Enter: that's the fastest way to create a named range.
- Names can't contain spaces, must start with a letter or underscore, and can't look like a cell address.
- A named range behaves like an absolute reference: it doesn't shift when you copy the formula.
- Use Name Manager (Formulas tab, or Ctrl+F3 on Windows) to edit, resize or delete names.
On this page+
A named range in Excel is a name, such as TaxRate or Sales, that stands for a cell or a block of cells. Instead of =B2*$F$1, you write =B2*TaxRate, which anyone can read. This guide shows three ways to create names, the rules a name must follow, and how to manage names once you have them.
What is a named range?#
A named range is a label you give to a cell or range so formulas can refer to it by name. =SUM(Sales) is easier to read than =SUM(B2:B13), and it tells the reader what is being added.
Names help most in three situations:
- Inputs used in many formulas, like a tax rate, exchange rate or target. Name the cell once and every formula can use it.
- Long or far-away ranges, especially on other sheets.
=SUM(Q1_Sales)beats=SUM('Q1 Data'!$C$2:$C$500). - Files other people review, such as models and reports. Names make the logic easier to check.
Named ranges work in every version of Excel, on Windows, Mac and the web.
How to create a named range (3 ways)#
1. Use the Name Box (fastest)#
- Select the cell or range, for example F1, which holds the tax rate.
- Click the Name Box, the small box at the left of the formula bar that normally shows the cell address.
- Type
TaxRateand press Enter.
If you press Enter without typing, or click away, the name isn't saved. The Move around the sheet lesson shows how the Name Box also jumps to any cell, and our article on Name Box tricks covers more uses.
2. Use Define Name#
Go to Formulas > Define Name. In the dialog, type the Name, choose the Scope (the whole workbook or one sheet), add an optional comment, and check the Refers to box. Click OK.
3. Create names from headers#
If your data has headers, Excel can name every column at once:
- Select the table, including the header row, for example A1:C13.
- Go to Formulas > Create from Selection (Windows shortcut: Ctrl+Shift+F3).
- Tick Top row and click OK.
Excel names each column after its header: the cells under Sales become the range Sales. Headers with spaces get underscores, so Unit Price becomes Unit_Price.
Naming rules: what Excel allows#
Excel rejects names that break these rules:
| Rule | Allowed | Not allowed |
|---|---|---|
| Start with a letter, underscore or backslash | TaxRate, _rate | 2025Sales |
| No spaces | Tax_Rate, TaxRate | Tax Rate |
| Can't look like a cell address | Rate_Q1 | TAX1, Q1, R1C1 |
| Letters, numbers, periods and underscores only | Sales.2025 | Sales-2025, Sales&Costs |
| Up to 255 characters |
Names are not case-sensitive: taxrate and TaxRate are the same name. Watch out for short names that match a column letter and row number. Excel has columns up to XFD, so TAX1 is a real cell address.
How to use a named range in a formula#
Type the name wherever you would type a reference:
=SUM(Sales)
=B2*TaxRate
=AVERAGE(Sales)As you type, names appear in the formula autocomplete list with a label icon, so you can press Tab to insert one. On Windows you can also press F3 to open the Paste Name list.
Here is a commission sheet with the rate in F1, named Rate:
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Priya | 12,500 | =B2*Rate → 625 |
| 3 | Marcus | 9,800 | =B3*Rate → 490 |
| 4 | Lena | 14,300 | =B4*Rate → 715 |
Named ranges vs absolute references#
A name made from a selection refers to fixed cells. Excel stores Rate as =Sheet1!$F$1, with dollar signs. That means a named range does the same job as an absolute reference with the $ sign: copy =B2*Rate down and it still points at F1.
=B2*$F$1 | =B2*Rate | |
|---|---|---|
| Stays put when copied | Yes | Yes |
| Easy to read | Not really | Yes |
| Works anywhere in the workbook | Needs a sheet name from other sheets | Yes (workbook scope) |
| Quick to set up | Press F4 | Takes a few seconds per name |
The absolute references lesson has you lock a tax rate with $F$1; a named range is the readable version of that idea. Interviewers often ask about both, so they appear in our list of Excel interview questions.
How to edit, resize and delete names#
Open Formulas > Name Manager (Windows shortcut: Ctrl+F3). It lists every name with its value, the cells it refers to, and its scope.
- Resize a range: select the name and change the Refers to box at the bottom, for example from
=Sheet1!$B$2:$B$13to=Sheet1!$B$2:$B$25. - Rename: select the name and click Edit. Formulas that use it update automatically.
- Delete: select the name and click Delete. Any formula that used it then shows #NAME?.
- Clean up: names that show
#REF!in the Refers to column point at deleted cells. Use the Filter button and choose Names with Errors to find them.
To see where each name points, select the Name Box drop-down arrow and pick a name; Excel selects its cells.
When to use named ranges#
Use names for inputs and key ranges that many formulas share: rates, targets, lookup tables and totals. They're common in finance models, which is why they come up in Excel skills for finance jobs. For quick one-off formulas, plain references are fine. If your data grows often, an Excel table (Ctrl+T) gives you names for columns that resize automatically. Once your names are set, the next steps for a tidy report are custom number formats and print setup.
For more on adding up a named range, see the SUM function guide.
Practise it#
Try the exercise on this page, then practise locking a rate cell with absolute references in a live spreadsheet. The Move around the sheet lesson shows you how to use the Name Box, the same box where you create names.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The commission rate is in F1. In Excel you could name it Rate and write =B2*Rate. Here, write the same formula with an absolute reference: in C2, type =B2*$F$1 and press Enter.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Commission | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: The $ signs lock F1, so the formula still points at the rate when you fill it down. That is what a named range does for you.
Practise this in a lesson
Frequently asked questions
How do I create a named range in Excel?
Select the cells, click the Name Box at the left of the formula bar, type a name such as Sales and press Enter. You can also use Formulas > Define Name, or Formulas > Create from Selection to name several ranges from their headers.
Why does Excel say the name is not valid?
The name probably contains a space, starts with a number, or looks like a cell address such as TAX1 or Q1. Use underscores or capitals instead of spaces, like Tax_Rate or TaxRate.
How do I edit or delete a named range?
Open Name Manager from the Formulas tab (Ctrl+F3 on Windows). Select the name, then change its Refers to box, click Edit to rename it, or click Delete.
Are named ranges absolute references?
Yes, by default. A name created from a selection refers to fixed cells, like $F$1, so copying a formula that uses it keeps pointing at the same cells.
Do named ranges slow Excel down?
Not in normal use. The usual problem is clutter: old names that point at deleted cells show #REF! in Name Manager and are worth deleting.