Creating and using named ranges

Excel 2016 2013 2010 2007 2003
Named ranges (also known as defined ranges) are a powerful feature in Excel that allows you to assign a symbolic name to a cell, range of cells, or formula. These defined names are a convenient replacement for the address of a cell, data range, or formula in other formulas, charts, diagrams, shapes, etc.

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...:
    Define Name in Excel 365
  • Right-click on the selection and choose Define Name... in the popup menu:
    Define Name in popup menu Excel 365
  • 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:
    Name Manager dialog box in Excel 365

Excel displays the New Name dialog box:

New Name in Excel 365

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:
    Scope list in New Name Excel 365

    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):

Name box in Excel 365

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:

Named range in Excel 365

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:
    Defined Names - Use in Formula Excel 365

    Then choose the name:

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

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:

Name Manager in Excel 365

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

Name Manager dialog box in Excel 365

   3.   Do one of the following:

  • Make changes in the Refers to field:
    Refers to field in Name Manager dialog box Excel 365

    To apply changes, click the OK button OK button in Name Manager dialog box Excel 365.

    To remove all changes you made, click the Cancel button Cancel button in Name Manager dialog box Excel 365.

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

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.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.