Working with Range Names

In Excel, you can assign names to cells and ranges. For example, you can give a cell a name such as VAT, or you can name a range Q1Sales.

Advantages of Range Names

1. A meaningful range name (such as TotalPrice) is much easier to remember than a cell address (such as H2).
2. Entering a name is less error prone than entering a cell or range address, and if you type a name incorrectly in a formula, Excel will display a #NAME? error.
3. You can quickly select the range using the Name box at the left end of the Formula bar, or by choose Home > Editing > Find & Select > Go To (or pressing F5 or Ctrl+G) and select the range name.
4. Creating formulas is easier. You can paste a cell or range name into a formula by using Formula AutoComplete (Show a list of relevant functions and defined names when building cell formulas. Home > Excel Options > Working with formulas > Formula AutoComplete. ).
5. Names make your formulas more understandable and easier to use. Learn Using Names in Excel Formulas. A formula such as =Price*Quantity is certainly more intuitive than =F2*G2.

Range Names rules

1. Starts with a letter, underscore (_), or backslash (\).
2. You can use the period (.), question mark (?), underscore (_), and  backslash(\) symbols within the name, but not as the first character.
3. Names can be a single letter, but you cannot use the letters R, r, C and c.
4. Names can't be the same as a cell address, like B3, may12.
5. Names can't contain spaces.
6. Names can be up to 255 characters.

Creating Range Names

Using the Name box

The fastest way to create a name is to use the Name box.

1. Select the cell or range that you want to name.
   
2. Click the Name box.
3. Type the name.
4. Press Enter to create the name.

Using the New Name dialog box

The New Name dialog box enables you to assign a scope when naming a range.

1. Select the cell or range that you want to name. Again, it is advisable to not include any label in the range of values.
2. Choose Formulas > Defined Names > Define Name.
3. Excel opens the New Name dialog box.
   
4. Type a name in the Name text field.
5. Optional step: Open the Scope drop‐down list and click the name of a sheet to limit the scope of the new range name to that sheet.
6. Optional step: If you like, you can can add a comment to the name.
7. Click Ok.

Creating multiple Range Names at once

If your data has labels on the left or above. you can create multiple Range Names at once.

1. 1. Select the range that you want to name. Make sure to include data labels, if any.
   2. Choose Formulas > Defined Names > Create from Selection or press Ctrl+Shift+F3.
   3. Excel opens the Create Names from Selection dialog box.
   4. Check or clear boxes as needed to specify which cells hold the labels to use for the range names. For example, if you only want to create names for the ranges in the selected columns, check only the Top row.
      
   5. Click Ok.
   6. Excel creates the names. You can check the names: Choose Formulas > Defined Names > Name Manager or press Ctrl+F3.
      

Managing Names

You can manage names with Excel Name Manager. To opens the Name Manager: Choose Formulas > Defined Names > Name Manager (or press Ctrl+F3 or press Alt+MN).

The Name Manager enables you to do any of the following:

1. Display information about each name:
   • Resize the Name Manager dialog box
   • Widen the columns to show more information,
   • Rearrange the order of the columns
   • Click a column heading to sort the information by the column.
2. Create new range names: Click the New button to create a new name.
3. Edit range names: To edit a name, select it in the list, and then click the Edit button. You can change the name itself, modify the Refers To range, or edit the comment.
4. Delete unneeded range names: To delete a name, select it in the list and click Delete.
5. Filter the range names: Click the Filter button and then click any of the following filter option from its drop-down menu.
   • Names Scoped to Worksheet
   • Names Scoped to Workbook
   • Names with Errors
   • Names without Errors
   • Defined Names
   • Table Names

Using Names in Excel formulas

Learn Using Names in Excel Formulas.