Creating and using named ranges

Range names is a powerful Excel feature which allows you to give a symbolic name for the cell or range of cells to be later used as convenient replacement for cell/range address in formulas.

Before you begin, however, you should be aware of some important rules about what is acceptable:

  • Names can't contain any spaces. You might want to use an underscore character to simulate a space (such as Annual_Total).
  • You can use any combination of letters and numbers, but the name must begin with a letter. A name can't begin with a number (such as 3rdQuarter) or look like a cell reference (such as Q3).
  • 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 uses a few names internally for its own use. 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 for names: Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title.

To create a range name, start by selecting the cell or range that you want to name. Then, do one of the following:

  • On the Formulas tab, in the Defined Names group, click Define Name...:
    Defined Names in Excel 2016
  • On the shortcut menu choose the Define Name...:
    Name a Range in 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 Excel 2016

Excel displays the New Name dialog box:

New Name in Excel 2016

Type a name in the box labelled Name in Workbook. The active or selected cell or range address appears in the box labelled 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 actually record the name; if you type a name and then click in the worksheet, Excel won't create the name.) If a name already exists, you can't use the Name box to change the range to which that name refers. Attempting to do so simply selects the range.

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.

There are ways to insert a name into a formula are available:

  • On the Formula tab, in the Defined Names group, click Use in Formula and choose a range name:
    Defined Names - Use in Formula Excel 2016
  • On the formula bar insert first letters of a range name, Excel proposed list:
    Name a Range1 or Name a Range2
  • Press F3 to display the Paste Name dialog box.
    Paste Name in Excel 2016