Creating and using named ranges

Excel 2013 2010 2007 2003
Named ranges are a powerful Excel feature that allows you to assign a symbolic name for the cell or the range of cells for later use as a convenient replacement for cell/range address in formulas.

Before you begin, however, you should be aware of some important rules for the names of named ranges in Excel:

  • Names can't contain any spaces. You might want to use an underscore character instead of space (such as Annual_Total).
  • You can use any combination of letters and numbers, but the name of the range must begin with a letter symbol. A name can't begin with a number (such as 3rdQuarter) or look like a cell reference (such as Q3).
  • Other symbols, except for underscores and periods, aren't allowed.
  • Names are limited to 255 characters, but it's a good practice to keep names as short as possible yet still be meaningful and understandable.

Excel also reserves a few names internally for its own purposes. Although you can create names that override Excel's internal names, you should avoid doing so. To be on the safe side, 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, start by selecting the cell or range that you want to assign a name. Then, do one of the following:

  • On the Formulas tab, in the Defined Names group, click Define Name...:
    Defined Names group in Excel 2016
  • On the shortcut menu choose the Define Name...:
    Define Name in popup menu Excel 2016
  • On the Formulas tab, in the Defined Names group, click Name Manager (or press Ctrl+F3) and in the Name Manager dialog box click New... button:
    Name Manager dialog box in Excel 2016

Excel displays the New Name dialog box:

New Name in Excel 2016

Type a name in the box labeled Name in Workbook. The active or selected cell or range address appears in the box labeled Refers To. Verify that the address listed is correct and then click OK to add the name to your worksheet 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 2016

Select the cell or range to name and then click the Name box and type the name. Press Enter to create the name. (You must press Enter to assign the name to the selected range; if you type a name and then click in the worksheet, Excel won't create the name.) If the name already exists, you can't use the Name box to change the range to which that name refers. Attempting to do so selects the range instead.

If your formula uses named cells or ranges, you can either type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically. The worksheet contains two defined names: Names and Values.

Insert a named range reference into the formula

There are ways to insert a named range reference into a formula are available:

  • On the Formula tab, in the Defined Names group, click Use in Formula and choose the range name:
    Defined Names - Use in Formula Excel 2016
  • 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 2016 or Name a Range2 in Excel 2016
  • Press F3 to display the Paste Name dialog box:
    Paste Name in Excel 2016

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

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.