Use the Name Manager dialog box to work with all the defined names and table names in a 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.

Name Manager dialog box

The Name Manager dialog box displays the following information about each name in a list box:

Column Name

Description

Name

One of the following:

  • A defined name, which is indicated by a defined name icon. Defined name icon

  • A table name, which is indicated by a table name icon. Table name icon

    Note: A table name is the name for an Excel table, which is a collection of data about a particular subject stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, and so on, each time you insert an Excel table. You can change a table's name to make it more meaningful. For more information about Excel tables, see Using structured references with Excel tables.

Value

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"

  • 3.1459

  • {2003;12,2002;23,;2001,18}

  • #REF!

  • {...}

Refers To

The current reference for the name. The following are representative examples:

  • =Sheet1!$A$3

  • =8.3

  • =HR!$A$1:$Z$345

  • =SUM(Sheet1!A1,Sheet2!B2)

Scope

  • A worksheet name, if the scope is the local worksheet level.

  • "Workbook," if the scope is the global workbook level. This is the default option.

Comment

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.

Refers to:

The reference for the selected name.

You can quickly edit the range of a name by modifying the details in the Refers to box. After making the change you can click Commit Enter button to save changes, or click Cancel Cancel button to discard your changes.

Notes: 

  • You cannot use the Name Manager dialog box while you are changing the contents of a 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).

  1. On the Formulas tab, in the Defined Names group, click Define Name.

  2. In the New Name dialog box, in the Name box, type the name you want to use for your reference.

    Note: Names can be up to 255 characters in length.

  3. The scope automatically defaults to Workbook. To change the name’s scope, in the Scope drop-down list box, select the name of a worksheet.

  4. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

  5. In the Refers to box, do one of the following:

    • Click Collapse Dialog Button image(which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog Button image.

    • To enter a constant, type = (equal sign) and then type the constant value.

    • To enter a formula, type = and then type the formula.  

      Tips: 

      • Be careful about using absolute or relative references in your formula. If you create the reference by clicking on the cell you want to refer to, Excel will create an absolute reference, such as "Sheet1!$B$1". If you type a reference, such as "B1", it is a relative reference. If your active cell is A1 when you define the name, then the reference to "B1" really means "the cell in the next column". If you use the defined name in a formula in a cell, the reference will be to the cell in the next column relative to where you enter the formula. For example, if you enter the formula in C10, the reference would be D10, and not B1.

      • More information - Switch between relative, absolute, and mixed references

  6. To finish and return to the worksheet, click OK.

Note: To make the New Name dialog box wider or longer, click and drag the grip handle at the bottom.

If you modify a defined name or table name, all uses of that name in the workbook are also changed.

  1. On the Formulas tab, in the Defined Names group, click Name Manager.

  2. In the Name Manager dialog box, double-click the name you want to edit, or, click the name that you want to change, and then click Edit.

  3. In the Edit Name dialog box, in the Name box, type the new name for the reference.

  4. In the Refers to box, change the reference, and then click OK.

  5. In the Name Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name.

  1. On the Formulas tab, in the Defined Names group, click Name Manager.

  2. In the Name Manager dialog box, click the name that you want to change.

  3. Select one or more names by doing one of the following:

    • To select a name, click it.

    • To select more than one name in a contiguous group, click and drag the names, or press SHIFT and click the mouse button for each name in the group.

    • To select more than one name in a noncontiguous group, press CTRL and click the mouse button for each name in the group.

  4. Click Delete.

  5. Click OK to confirm the deletion.

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, making it easy to combine or remove different filter operations to get the results you want.

You can filter from the following options:

Select

To

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 containing errors (such as #REF, #VALUE, or #NAME).

Names Without Errors

Display only those names with values that do not contain errors.

Defined Names

Display only names defined by you or by Excel, such as a print area.

Table Names

Display only table names.

  • To sort the list of names in ascending or descending order, click the column header.

  • To automatically size the column to fit the longest value in that column, double-click the right side of the column header.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Why am I seeing the Name Conflict dialog box in Excel?

Create a named range in Excel

Insert a named range into a formula in Excel

Define and use names in formulas

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.