Range Name Syntax
Name syntax rules:
The Name string must begin with a text character, not a number, and consists of adjacent characters.
Two words can be joined with an underscore (_). For example, to enter the Name "Excel Book", you should type Excel_Book.
You cannot use a Name that could otherwise be used as a cell reference. For example, A1 or IS2002.
Notes:
There is no limit on the number of Names you can define. Be sure to define unique Names for a specific workbook. Defining Names that resemble Names in other sheets will only complicate your work.
Defining a Range Name
To define a range Name, use one of the following two techniques:
Type the text directly into the Name box :
1. Select cell A1.
2. In the Name box, type the text, and then press <Enter>.
Define a Name using the Define Name dialog box :
1. Select cell B1.
2. Press <Ctrl+F3>.
OR
From the Insert menu, select Name and then Define.
3. Type the text in the Names in workbook box, and then click OK.
Deleting a Range Name
Why it is highly recommended to delete unnecessary range Names:
Large numbers of range Names makes it more difficult to locate a
specific Name.
Range Names create references and unwanted links.
To find unnecessary/unwanted range Names:
To delete a range Name:
Press <Ctrl+F3>, select the Name, and then click Delete.
Using a Range Name in a Formula
To use a range Name in a formula:
Name syntax rules:
The Name string must begin with a text character, not a number, and consists of adjacent characters.
Two words can be joined with an underscore (_). For example, to enter the Name "Excel Book", you should type Excel_Book.
You cannot use a Name that could otherwise be used as a cell reference. For example, A1 or IS2002.
Notes:
There is no limit on the number of Names you can define. Be sure to define unique Names for a specific workbook. Defining Names that resemble Names in other sheets will only complicate your work.
Defining a Range Name
To define a range Name, use one of the following two techniques:
Type the text directly into the Name box :
1. Select cell A1.
2. In the Name box, type the text, and then press <Enter>.
Define a Name using the Define Name dialog box :
1. Select cell B1.
2. Press <Ctrl+F3>.
OR
From the Insert menu, select Name and then Define.
3. Type the text in the Names in workbook box, and then click OK.
Deleting a Range Name
Why it is highly recommended to delete unnecessary range Names:
Large numbers of range Names makes it more difficult to locate a
specific Name.
Range Names create references and unwanted links.
To find unnecessary/unwanted range Names:
- Select a cell in a new sheet.
- Press <F3> and click Paste List. A full list of range names and their references is pasted into the new sheet; delete each unwanted Name.
To delete a range Name:
Press <Ctrl+F3>, select the Name, and then click Delete.
Using a Range Name in a Formula
To use a range Name in a formula:
- Define the following range Names for ranges B2:B11, C2:C11, and D2:D11 respectively: Jan_2004, Feb_2004, and Mar_2004 (see the screenshot below).
- Select a cell and type the formula =SUM.
- Press <Ctrl+A>.
- Select the first argument box and press <F3>.
- Select the Name Jan_2004, and then click OK.
- Paste the Names Febr_2004 and Mar_2004 in the next two argument boxes, and then click OK. The following formula has now been inserted into the cell: =SUM(Jan_2004, Feb_2004, Mar_2004)
EmoticonEmoticon