Creating and using named ranges

Excel 365 2016 2013 2010 2007
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, select Insert -> Name -> Define... (or press Ctrl+F3). Excel displays the Define Name dialog box:

Define Name in Excel 2003

Type a name in the box labelled Names in Workbook (or use the name that Excel proposes, if any). 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. Or you can click the Add button to continue adding names to your worksheet. If you do this, you must specify the Refers To range either by typing an address (make sure to begin with an equal sign) or by pointing to it in the worksheet. Each name appears in the list 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 2003

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.

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

  • Select Insert -> Name -> Paste: Excel displays its Paste Name dialog box with all the names listed. Select the name and click OK. Or you can double-click the name, which inserts the name and closes the dialog box.
  • Press F3: This also displays the Paste Name dialog box.
Paste Name in Excel 2003

The worksheet contains two defined names: Numbers and Saldo. The Paste Name dialog box is being used to insert a name (Numbers) into the formula being entered in cell.

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.