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.
Learn more about using names
A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how they can improve clarity and understanding.
Example with no name
Example with a name
Types of names
There are several types of names that you can create and use.
Defined name A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name, and Microsoft Office Excel sometimes creates a defined name for you, such as when you set a print area.
Table name A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, and so on, each time that you insert an Excel table, but you can change a table's name to make it more meaningful.
The scope of a name
All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognized without qualification. For example:
If you have defined a name, such as Budget_FY08, and its scope is Sheet1, that name, if not qualified, is recognized only in Sheet1, but not in other sheets without qualification.
To use a local worksheet name in another worksheet, you can qualify it by preceding it with the worksheet name, as the following example shows:
If you have defined a name, such as Sales_Dept_Goals, and its scope is the workbook, that name is recognized for all worksheets in that workbook, but not for any other workbook.
A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. For example, you can define a name, such as GrossProfit that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name, GrossProfit, is always referencing the same cells at the local worksheet level.
You can even define the same name, GrossProfit, for the global workbook level, but again the scope is unique. In this case, however, there can be a name conflict. To resolve this conflict, by default Excel uses the name that is defined for the worksheet because the local worksheet level takes precedence over the global workbook level. If you want to override the precedence and you want to use the workbook name, you can disambiguate the name by prefixing the workbook name as the following example shows:
You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.
Defining and entering names
You define a name by using the:
Name box on the formula bar This is best used for creating a workbook level name for a selected range.
Create a name from selection You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
New Name dialog box This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.
Note: By default, names use absolute cell references.
You can enter a name by:
Typing Typing the name, for example, as an argument to a formula.
Using Formula AutoComplete Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
Selecting from the Use in Formula command Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formulas tab.
You can also create a list of defined names in a workbook. Locate an area with two empty columns on the worksheet (the list will contain two columns, one for the name and one for a description of the name). Select a cell that will be the upper-left corner of the list. On the Formulas tab, in the Defined Names group, click Use in Formula, click Paste and then, in the Paste Names dialog box, click Paste List.
Learn about syntax rules for names
Manage names by using the Name Manager dialog box
Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.
To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.
The Name Manager dialog box displays the following information about each name in a list box:
Icon and Name
One of the following:
A defined name, which is indicated by a defined name icon.
A table name, which is indicated by a table name icon.
The current value of the name, such as the results of a formula, a string constant, a cell range, an error, an array of values, or a placeholder if the formula cannot be evaluated. The following are representative examples:
"this is my string constant"
The current reference for the name. The following are representative examples:
A worksheet name, if the scope is the local worksheet level.
"Workbook", if the scope is the global worksheet level.
Additional information about the name up to 255 characters. The following are representative examples:
This value will expire on May 2, 2007.
Don't delete! Critical name!
Based on the ISO certification exam numbers.
Note: If you save the workbook to Microsoft Office SharePoint Server 2007 Excel Services, and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.
You cannot use the Name Manager dialog box while you are changing the contents of the cell.
The Name Manager dialog box does not display names defined in Visual Basic for Applications (VBA), or hidden names (the Visible property of the name is set to "False").
Use the commands in the Filter drop-down list to quickly display a subset of names. Selecting each command toggles the filter operation on or off, which makes it easy to combine or remove different filter operations to get the results that you want.
To filter the list of names, do one or more of the following:
Names Scoped To Worksheet
Display only those names that are local to a worksheet.
Names Scoped To Workbook
Display only those names that are global to a workbook.
Names With Errors
Display only those names with values that contain errors (such as #REF, #VALUE, or #NAME).
Names Without Errors
Display only those names with values that do not contain errors.
Display only names defined by you or by Excel, such as a print area.
Display only table names.