Jan 03, 2015 In name manager, in the list of already created names, IF the name is local it will have the worksheet name shown just to the right of the cell/range name. You can also always click on the cell/range name in name manager and at the bottom of the the name manager window it will display the location of the cell/range. A named range is just a human-readable name for a range of cells in Excel. Note: on a Mac, there is no Name Manager, so you'll see the Define Name dialog.
By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.
Name a cell
Select a cell.
In the Name Box, type a name.
Press Enter.
Define names from a selected range
Select the range you want to name, including the row or column labels.
Select Formulas > Create from Selection.
In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box.
Select OK.
Excel names the cells based on the labels in the range you designated.
Use names in formulas
Select a cell and enter a formula.
Place the cursor where you want to use the name in that formula.
Type the first letter of the name, and select the name from the list that appears.
Or, select Formulas > Use in Formula and select the name you want to use.
Press Enter.
Manage names in your workbook with Name Manager
On the Ribbon, go to Formulas > Defined Names > Name Manager. You can then create, edit, delete, and find all the names used in the workbook.
Name a cell
Select a cell.
In the Name Box, type a name.
Press Enter.
Define names from a selected range
Select the range you want to name, including the row or column labels.
Select Formulas > Create from Selection.
In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box.
Select OK.
Excel names the cells based on the labels in the range you designated.
Use names in formulas
Select a cell and enter a formula.
Place the cursor where you want to use the name in that formula.
Type the first letter of the name, and select the name from the list that appears.
Or, select Formulas > Use in Formula and select the name you want to use.
Press Enter.
Manage names in your workbook with Name Manager
On the Ribbon, go to Formulas > Defined Names > Name Manager. You can then create, edit, delete, and find all the names used in the workbook.
In Excel Online, you can use the named ranges you've defined in Excel for Windows or Mac. Select a name from the Name Box to go to the range's location, or use the Named Range in a formula.
For now, creating a new Named Range in Excel Online is not available.
Would you like that feature? Go to Excel Online User Voice and suggest it!
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
See Also
A named range, range name, or defined name all refer to the same object in Excel; it's a descriptive name — such as Jan_Sales or June_Precip — that is attached to a specific cell or range of cells in a worksheet or workbook. Named ranges make it easier to use and identify data when creating charts, and in formulas such as:
Additionally, since a named range does not change when a formula is copied to other cells, it provides an alternative to using absolute cell references in formulas. Three different methods for defining a name in Excel are to use the name box, the new name dialog box, or the name manager — two are covered in this tutorial.
Defining and Managing Names with the Name Box
One way, and possibly the easiest way, of defining names is using the Name Box, located above column A in the worksheet. You can use this method to create unique names that are recognized by every sheet in a workbook. To create a name using the Name Box as shown in the image above:
- Highlight the desired range of cells in the worksheet.
- Type thedesired name for that range in the Name Box, such as Jan_Sales.
- Press the Enter key on the keyboard.
- The name is displayed in the Name Box.
The name is also displayed in the Name box whenever the same range of cells is highlighted in the worksheet. It is also displayed in the Name Manager.
Naming Rules and Restrictions
Syntax rules to remember when creating or editing names for ranges are as follows:
- A name can't contain spaces.
- The first character of a name must be either a letter, underscore, or backslash.
- The remaining characters can only be letters, numbers, periods, or underscore characters.
- The maximum name length is 255 characters.
- Uppercase and lowercase letters are indistinguishable to Excel, so Jan_Sales and jan_sales are seen as the same name by Excel.
- Cell reference cannot be used as names such as A25 or R1C4.
Defining and Managing Names with the Name Manager
![Name Name](https://www.howtogeek.com/wp-content/uploads/2016/02/10_delete_confirmation.png.pagespeed.ce.7yOIj--_mO.png)
A second method for defining names is to use the New Namedialog box; this dialog box is opened using the Define Name option located in the middle of the Formulastab of the ribbon. The New Name dialog box makes it easy to define names with a worksheet level scope.
- Highlight the desired range of cells in the worksheet.
- Click on the Formulas tab of the ribbon.
- Click on the Define Name option to open the New Name dialog box.
- In the dialog box, you are required to define a Name, Scope, and Range.
- Once completed, click OK to return to the worksheet.
- The name will be displayed in the Name Box whenever the defined range is selected.
The Name Manager
The Name Manager can be used to both define and manage existing names; it is located next to the Define Name option on the Formulastab of the ribbon.
Defining a Name using the Name Manager
When defining a name in the Name Manager it opens the New Name dialog box outlined above. The complete list of steps are as follows:
- Click the Formulas tab of the ribbon.
- Click on the Name Manager icon in the middle of the ribbon to open the Name Manager.
- In the Name Manager, click on the New button to open the New Name dialog box.
- In this dialog box, you are required to define a Name, Scope, and Range.
- Click OK to return to the Name Manager where the new name will be listed in the window.
- Click Close to return to the worksheet.
Deleting or Editing Names
- In the window containing the list of names, click once on the name to be deleted or edited.
- To delete the name, click on the Delete button above the list window.
- To edit the name, click on the Edit button to open the Edit Name dialog box.
In the Edit Name dialog box, you can edit the chosen name, add comments about the name, or change the existing range reference.
The scope of an existing name cannot be changed using the edit options. To change the scope, delete the name and redefine it with the correct scope.
Filtering Names
The Filter button in the Name Manager makes it easy to:
- Find names with errors – such as an invalid range.
- Determine the scope of a name – whether worksheet level or workbook.
- Sort and filter listed names – defined (range) names or table names.
The filtered list is displayed in the list window in the Name Manager.
Defined Names and Scope in Excel
All names have a scope which refers to the locations where a specific name is recognized by Excel. A name's scope can be for either individual worksheets (local scope) or for an entire workbook (global scope). A name must be unique within its scope, but the same name can be used in different scopes.
The default scope for new names is the global workbook level. Once defined, the scope of a name cannot easily be changed. To change the scope of a name, delete the name in the Name Manager and redefine it with the correct scope.
Local Worksheet Level Scope
A name with a worksheet level scope is valid only for the worksheet for which it was defined. If the name Total_Sales has a scope of sheet 1 of a workbook, Excel will not recognize the name on sheet 2, sheet3, or any other sheet in the workbook. This makes it possible to define the same name for use on multiple worksheets – as long as the scope for each name is restricted to its particular worksheet.
Using the same name for different sheets might be done to ensure continuity between worksheets and ensure that formulas that use the name Total_Sales always refer to the same range of cells in multiple worksheets within a single workbook.
To distinguish between identical names with different scopes in formulas, precede the name with the worksheet name, such as:
Or
![Mac Mac](https://www.techonthenet.com/excel/ranges/images/add_named_range2011_002.png)
Names created using the Name Box will always have a global workbook level scope unless both sheet name and the range name are entered into the name box when the name is defined.
- Name: Jan_Sales, Scope — global workbook level
- Name: Sheet1!Jan_Sales, Scope — local worksheet level
Global Workbook Level Scope
A name defined with a workbook level scope is recognized for all worksheets in that workbook. A workbook level name can, therefore, only be used once within a workbook, unlike the sheet level names discussed above.
A workbook level scope name is not, however, recognized by any other workbook, so global level names can be repeated in different Excel files. For example, if the Jan_Sales name has a global scope, the same name could be used in different workbooks titled 2012_Revenue, 2013_Revenue, and 2014_Revenue.
Scope Conflicts and Scope Precedence
It is possible to use the same name at both the local sheet level and workbook level because the scope for the two would be different. Such a situation, however, would create a conflict whenever the name was used.
To resolve such conflicts, in Excel, names defined for the local worksheet level take precedence over the global workbook level. In such a situation, a sheet-level name of 2014_Revenue would be used instead of a workbook level name of 2014_Revenue.
To override the rule of precedence, use the workbook level name in conjunction with a specific sheet-level name such as:
The one exception to overriding precedence is a local worksheet level name that has a scope of sheet 1 of a workbook. Scopes linked to sheet 1 of any workbook cannot be overridden by global level names.