A named range gives a cell or range a short name, such as TaxRate or SalesData. A formula like =B2*TaxRate is far easier to understand than =B2*$F$1, especially when someone else opens your file six months later.
Create a name in 10 seconds
Select the cell or range, click in the Name Box (left of the formula bar), type the name and press Enter.
Naming rules
- No spaces. Use TaxRate or Tax_Rate.
- Start with a letter or underscore.
- The name cannot look like a cell reference, so Q1 or A1 are not allowed.
Use the name in a formula
Type =SUM(SalesData) or =B2*TaxRate. While typing, Excel suggests your names in the list.
Manage names
Go to Formulas, then Name Manager. You can edit what a name refers to, rename it or delete it. If a name points to a deleted range, formulas using it show #REF!.
Create names from headers
Select a table including its headers, then Formulas, then Create from Selection. Excel makes one name per column using the header text.
Names in drop-down lists
For a drop-down, name your list range as Cities. In Data Validation, choose List and enter =Cities. If the list is a Table column, the drop-down grows with it.
When to use a Table instead
For data that grows, an Excel Table (Ctrl + T) is better than a fixed named range, because the structured references expand automatically.