Creating Named Ranges in Excel
Airfare Daily Deals eCigarettes Eyeglasses Hotels Jewelry Online Backup Online Dating Online Printing Online Tickets Skin Care Textbook Rentals Vitamins Web Hosting Weddings
Find thousands of shopping-related forums
SEARCH

Creating Named Ranges in Excel

Creating Named Ranges in Excel As you create and maintain large volumes of data in a worksheet, you will frequently need to refer to a range of cells. For example, a column in the worksheet contains the price of all products. You will need to refer to this range of cells while performing calculations and including it in formulas. In such a case, it is more convenient and efficient to name this range meaningfully and use the name of the range instead of the cell coordinates in formulas and functions. Naming a range of cells has the following advantages:

Creating Named Ranges in Excel

As you create and maintain large volumes of data in a worksheet, you will frequently need to refer to a range of cells. For example, a column in the worksheet contains the price of all products. You will need to refer to this range of cells while performing calculations and including it in formulas. In such a case, it is more convenient and efficient to name this range meaningfully and use the name of the range instead of the cell coordinates in formulas and functions. Naming a range of cells has the following advantages:

• Names are easier to remember than cell coordinates. For example, if you have the price of products stored in the range C3 to C10, you can name it as Prices. Subsequently, you can use the tag Prices instead of the cryptic reference C3:C10.

• Names reduce the chance of errors while typing.

• Naming a range makes navigating a worksheet easier. For example, you can refer to the name in the Go To dialog box instead of the cell coordinates.

• A named range can be used throughout a workbook. This is very useful when you link the in a workbook to generate a consolidated data set.

Creating Names

While naming ranges, you need to follow some naming rules defined by Excel. The rules for naming range in Excel are:

• Range names can be up to 255 characters long. However, you should assign short names since are easier to remember and type.

• Names must start with a letter or an underscore (__).

• You cannot use a space or a hyphen in a" name.

• Control characters are not allowed in a name.

The steps to create a named range are:

• Select the cell or range of cells that you need to name.

• Opt for the Name option from the Insert menu.

• Select the Define option from the Name sub-menu.

A default name of the selected range is displayed. This name is the column heading in case the range is within the same column or the value in the first cell if the range is a row.

• Edit the default name if required.

The selected range is displayed in the Refers to text box.

• Edit the range coordinates if required.

• Click on the Add button to create the name.

• Click OK button for closing the dialog box.

The steps to edit a named range are:

• Select the Name option from the Insert menu.

• Select the Define option from the Name sub-menu.

All the ranges that you have named in the current workbook are displayed in the Names in Workbook text box.

• Opt for the name that you want to edit.

• To change the name, edit it in the Names in Workbook text box.

• To change the reference range, edit the cell coordinates in the Refers to text box.

• Click on the OK button to complete editing process.

 

Need an answer?
Get insightful answers from community-recommended
experts
in Software on Knoji.
Would you recommend this author as an expert in Software?
You have 0 recommendations remaining to grant today.
Comments (0)
ARTICLE DETAILS
RELATED ARTICLES
ARTICLE KEYWORDS