Using Range Names
The use of range names in a spreadsheet provides several important benefits; perhaps the most important of which is that you can use the range name to both move and simultaneously select the cell range in a flash no matter where this range is located in the entire workbook. After a range is named, all you have to do to select the range is to choose its range name from the Name box drop-down list on the Formula bar.
This method of selecting cell ranges can be a tremendous time-saver when you are dealing with a table or list of data that you find yourself referring to or editing on a regular basis. This is all the more true if the named range’s data tables or lists are located in columns and rows in some distant region of a worksheet.
Remember that you can use range names to select cell ranges with data tables or lists that you print on a regular basis. All you have to do is select the cell range with the Name box, and then open the Print dialog box (Ctrl+P) and select the Selection option button in the Print What section before you select the OK button.
The second important use for range names is to define and name a constant (such as a tax rate or interest percentage) whose value you need to refer to in the formulas you are about to build in a data table or list but which you don’t want to enter in the worksheet as a separate cell entry. To do this, open the New Name dialog box (Formulas => Define Name or Alt+MMD) and enter the name for the constant in the Name text box. Next, set the name equal to the value or the formula that calculates the value you want used in the Refers To text box before you select OK. Then, to refer to the constant in a formula you are building, click its name on the drop-down menu that appears when you click the Use in Formula button on the Formulas tab or press Alt+MS.
The final important use for range names is in documenting the functioning of the formulas in your data tables in a spreadsheet. You can do this by having Excel apply the table’s row and column headings to the cells of the table. Simply select the table including the cells with the row and column headings before you open the Create Names From Selection dialog box (Formulas => Create From Selection or Alt+MC). Next, select the appropriate check boxes (Top Row, Left Column, Bottom Row, or Right Column) that contain the row and column headings before you select OK. Then, to replace the cell addresses in the formulas in this table with the new range names, click the Apply Names option on the Define Name button’s drop-down menu to open the Apply Names dialog box (Alt+MMA). With the Use Row and Column Names check box selected, select the OK button.