MS-Excel / Functions and Formula

---

Introducing Ranges Names

Excel users have long recognized the advantages of defining names for items like cells, constants, or formulas, particularly when authoring formulas. When you have entered data into your worksheet, you'll find it worthwhile to examine it to identify cells or ranges that may be used later in formulas and assign meaningful names to them. Having created names, you can go on to construct stable formulas that are more comprehensible by referring to these names rather than cell references. A defined name like Total_Amount is much more user-friendly, and much less likely to be mistyped, than a reference like J213. Furthermore, formulas and functions that incorporate names, for example =Total_Profit-Total_Expenses or =AVERAGE(MonthlySales), certainly convey a clearer impression of what the result will mean than formulas or functions that only use cell references.

Excel will sometimes create a name for you, for example when you set a Print Area. Also, when you create a table, Excel will automatically allocate a name in the format of Table1, Table 2, etc. however, you can change these names to make them more meaningful.

The easiest way to create a name for a cell or range is to select the cell or cells and type the name directly into the Name box to the left of the formula bar (see Figure below) and press Enter. You can also use the Name box to go directly to a named cell or range by selecting the name from the dropdown list that appears when you click the down arrow to the right of the Name box.

You can use the Name box to quickly create and navigate to named cells or ranges. If the selected cell has not been named, the Name box will display the reference of the cell.

Excel 2007 has acknowledged the importance of defining names, especially when inputting formulas, by including a Defined Names group in the center of the Formulas tab on the Ribbon. Commands for naming ranges and creating names from selected cells are prominently positioned there (instead of being hidden in the Insert menu as before) and the tasks of creating, finding, editing, and deleting names can now be facilitated in a much more convenient manner by using the Name Manager.