Creating and using named ranges

Some important rules for the names of named ranges in Excel:

• Names can't contain any spaces – it is recommended to use an underscore character (_) instead of space (for example, Team_C_Total).
• Names can have any combination of letters and numbers, but the first character must be a letter symbol. The name can't start with a number (for example, 3rdQuarter) or look like a cell reference (for example, Q3).
• No characters other than underscores and periods are allowed in names.
• Name length is limited to 255 characters, but keeping names as short as possible is recommended. However, try to make them meaningful and understandable - first of all, this will help you deal with possible problems or errors.

Excel also has reserved names. Although Excel allows you to override internal Excel names, try to avoid this. Just in case, avoid using the following names: Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title.

Create a named range in Excel

To create a named range, do the following:

   1.   Select the cell or range you want to assign a name.

   2.   Do one of the following:

• On the Formulas tab, in the Defined Names group, click Define Name...:
  
• Right-click on the selection and choose Define Name... in the popup menu:
  
• On the Formulas tab, in the Defined Names group, click Name Manager (or press Ctrl+F3), then in the Name Manager dialog box, click the New... button:
  

Excel displays the New Name dialog box:

In the New Name dialog box:

• Type a name in the Name field.

  Excel will display the predefined name if you select a data range with a heading line.

• Verify the active or selected cell or range address that appears in the box labeled Refers to.
• Optionally, choose the appropriate item from the Scope dropdown list. This list contains all sheets of the active workbook, including the entire workbook:
  

  Selecting a specific spreadsheet means that the name can be used only on that sheet:

  • You cannot select that name to use on other sheets,
  • Excel will generate an error when using the name in formulas on other sheets.

  By default, all names are used throughout the Workbook.

  Note: Be careful! You can't change that customization in the Edit mode (see how to modify the name).

• Click OK to add the name and close the dialog box.

Note: A faster way to create a name is to use the Name box (to the left of the formula bar):

Select the cell or range to name and then click the Name box and type the name. Press Enter to create the name.

Press Enter to assign the name to the selected range. If you type a name and click anywhere in the worksheet, Excel won't create the name.

If the name already exists, you can't use the Name box to change the name's range. Attempting to do so, select the defined range instead.

For example:

Insert a named range reference into the formula

Use any of the proposed ways to insert a name into a formula:

• On the Formula tab, in the Defined Names group, click Use in Formula:
  

  Then choose the name:

  
• On the Formula bar, type the first letters of the range name, Excel opens the list of matching named ranges:
    or  
• Press F3 to display the Paste Name dialog box, choose the range name, and click OK:
  

Modify a named range

To change a name or references, do the following:

   1.   On the Formula tab, in the Defined Names group, click Name Manager:

   2.   In the Name Manager dialog box, choose the named range you need to modify:

   3.   Do one of the following:

• Make changes in the Refers to field:
  

  To apply changes, click the OK button .

  To remove all changes you made, click the Cancel button .

• Click the Edit... button to open the Edit Name dialog box:
  

Delete unneeded names

To remove names you no longer need or don't need to use, do the following:

   1.   On the Formula tab, in the Defined Names group, click the Name Manager button.

   2.   In the Name Manager dialog box:

• Choose the name you want to remove,
• Click the Delete button.

Note: To prevent errors, we recommend analyzing the precedents and dependents of the used cells of the named range you want to delete. See Tracing cell relationships for more details.

See also this tip in French: Comment créer et utiliser des plages nommées.