How to Create and Use Named Ranges in Excel
Which formula is easier to understand: =B2*$E$1 or =B2*VAT? Named ranges let you give a cell or group of cells a meaningful name, then use that name in formulas. They make spreadsheets easier to read, harder to break, and quicker to navigate.
Create a name with the Name Box (quickest)
1. Select the cell or range, for example the cell holding your VAT rate.
2. Click in the Name Box, the small box at the left of the formula bar that normally shows the cell address, like E1.
3. Type a name, such as VAT.
4. Press Enter.
2. Click in the Name Box, the small box at the left of the formula bar that normally shows the cell address, like E1.
3. Type a name, such as VAT.
4. Press Enter.
That's it. Don't forget the Enter, or the name won't be saved.
Naming rules
• Start with a letter or underscore.
• No spaces. Use an underscore or capitals instead: Sales_Total or SalesTotal.
• It can't look like a cell address. Q1, TAX2026 and AB12 are taken, so use Qtr1 instead.
• Names aren't case-sensitive. vat and VAT are the same name.
• No spaces. Use an underscore or capitals instead: Sales_Total or SalesTotal.
• It can't look like a cell address. Q1, TAX2026 and AB12 are taken, so use Qtr1 instead.
• Names aren't case-sensitive. vat and VAT are the same name.
Use a name in a formula
Type the name anywhere you'd normally type a cell reference:
=B2*VAT
=SUM(Sales)
=AVERAGE(Scores)
=VLOOKUP(E2, PriceList, 3, FALSE)
=SUM(Sales)
=AVERAGE(Scores)
=VLOOKUP(E2, PriceList, 3, FALSE)
As you type, Excel suggests matching names. Press Tab to accept one. You can also press F3 while writing a formula to pick from a list of every name.
Named ranges are always absolute, so there's no need for $ signs, and they won't move when you copy formulas.
Create names from your headings
If your data has headings, Excel can name every column in one go:
1. Select the data including the heading row.
2. Go to Formulas > Create from Selection (or press Ctrl + Shift + F3).
3. Tick Top row and untick anything else.
4. Click OK.
2. Go to Formulas > Create from Selection (or press Ctrl + Shift + F3).
3. Tick Top row and untick anything else.
4. Click OK.
Each column is now named after its heading. Spaces in headings become underscores, so Unit Price becomes Unit_Price.
Manage names
Press Ctrl + F3 (or go to Formulas > Name Manager) to see every name in the workbook, what it refers to, and its current value. From here you can:
• Edit a name, or change the cells it points to.
• Delete names you no longer need.
• Filter to find names with errors.
• Delete names you no longer need.
• Filter to find names with errors.
Jump to a named range
Click the arrow on the Name Box and pick a name. Excel jumps straight to it and selects it, even on another sheet. You can also press F5 (Go To) and choose a name from the list.
Names that grow with your data
A normal named range doesn't grow when you add rows. The simplest fix is to use a Table instead (press Ctrl + T). Tables name themselves, and their columns expand automatically. Formulas like =SUM(Sales[Amount]) always include every row.
Name a value, not a cell
You can name a fixed number without it being in a cell at all:
1. Go to Formulas > Define Name.
2. Type VAT as the name.
3. In Refers to, type =20%.
4. Click OK.
2. Type VAT as the name.
3. In Refers to, type =20%.
4. Click OK.
Now =B2*VAT works anywhere in the workbook.
Troubleshooting
#NAME? The name is misspelt or doesn't exist. Check it in the Name Manager.
The name isn't accepted. It probably contains a space, starts with a number or looks like a cell address.
The formula shows #REF! The cells the name referred to were deleted. Fix it in the Name Manager.
Wrapping up
Select a cell, type a name in the Name Box, press Enter. Then use it in formulas instead of cell addresses. Your future self, and anyone else who opens the file, will thank you.
