Creating and using named ranges
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...:
- On the shortcut menu choose the Define Name...:
- 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:
Excel displays the New Name dialog box:
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):
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:
- On the formula bar insert first letters of a range name, Excel proposed list:
- Press F3 to display the Paste Name dialog box.