Creating and using named ranges

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...:
  
• 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:

Type a name in the Name field (Excel can display the name if you selected a data range with a heading line). 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 spreadsheet 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. (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:
  
• 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:
  

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