MS-Excel / Functions and Formula

---

Defining Range Name

The Name Manager in Excel 2007 provides you with an alternative interface for viewing and managing the names in a workbook. You can use the Name Manager to simplify naming chores like editing or renaming names or deleting multiple names. You can even add comments to clarify the purpose of a name.

1. Choose the Formulas tab on the Ribbon. In the Defined Names group, click Name Manager.
   Click Name Manager to create, view, edit, and delete defined names.
   
   
   
   
2. The Name Manager dialog box will appear, listing any names that have already been defined in the current workbook.
   Use the Name Manager to create a new name or select an existing one to edit or delete.
3. Click New to open the New Name dialog box see below appears. You can also open the New Name dialog box directly from the worksheet by clicking Define Name in the Defined Names group.
   
   
   
   Enter the appropriate details for the new name and click OK.
4. Enter a name in the Name box (or use the name that Excel suggests if appropriate). Excel will automatically insert any text that appears in a cell to the left or above the selected cell or range in the Name box.
5. Select the Scope setting for the name (either to a specific worksheet or to the entire workbook). The scope of a name defines where the name can be used without being qualified and is either local (a worksheet) or global (a workbook).
   If the scope of a name is the local worksheet, the name will only be recognized within that worksheet. To use a defined name whose scope is the local worksheet in another worksheet, you need to qualify it by preceding it with the worksheet name. For example, if the name Start_Date is restricted to Sheet1, to use it in another sheet you will need to enter Sheet1!Start_Date. If the scope for the name Start_Date is the workbook, it can be used in any worksheet in the same workbook.
6. Add a description or explanation for the name in the Comment box if appropriate. Text entered in the Comment box will appear as the ScreenTip for the name in the Formula AutoComplete box.
7. By default, the selected cell or range will be the reference in the Refers to box. If you want to enter a different reference, click the Collapse Dialog icon on the right. The "New Name-Refers to" dialog box will appear, allowing you to select the cell or range on the worksheet. Click the Expand Dialog icon on the right to return to the New Name dialog box. If you want the name to refer to a constant or a formula, type = followed by the constant or formula in the Refers to box.
8. Click OK to return to the Name Manager; your new name will be listed in the Name Manager. (If you opened the New Name box by clicking Define Name in the Defined Names group, you will be returned to the worksheet when you click OK in the New Name box.)
9. Continue to define any other names you want to create or click Close to close the Name Manager dialog box.

Each new name you create must be unique within its scope, although you can use the same name several times within one workbook as long as each instance has a different scope. For example, you can create a name Total_Cost scoped to Sheet1 and another name Total_Cost scoped to Sheet2 within the same workbook. You can even have a third Total_Cost scoped to the global workbook as this is a separate scope-although this could cause a name conflict, in which case the local worksheet level will take precedence.