Use names to clarify formulas

What's in a name ? That which we call a cell range, function, constant, or table would tally and toil just as sweetly, methinks. But nay, don't take me at my word. Read what follows that is writ, to master all manner of names that befit.Pray tell, what dost thou desire to do?

Learn more about using names

Syntax rules for names

Create a name for a cell or cell range on a worksheet

Create a name by using a selection of cells in the worksheet

Create a name by using the New Name dialog box

Manage names by using the Name Manager dialog box

Change a name

Delete one or more 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.

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 the name to make it more meaningful. For more information on Excel tables, see Using structured references with Excel tables.

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, then that name, if not qualified, is only recognized in Sheet1, but not in Sheet2 or Sheet3 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:

  Sheet1!Budget_FY08

• If you have defined a name, such as Sales_Dept_Goals, and its scope is the workbook, then 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, 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:

WorkbookFile!GrossProfit

Creating and entering names

You create 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 Formula tab.

Auditing names

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.

Syntax rules for names

The following is a list of syntax rules that you need to be aware of when you create and edit names.

• Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

  Note You cannot use the letters "C", "c", "R", or "r" as a defined name, because both of these letters are used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

• Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not valid Spaces are not allowed. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
• Name length A name can contain up to 255 characters.
• Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

Create a name for a cell or cell range on a worksheet

1. Select the cell, range of cells, or nonadjacent selections that you want to name.
2. Click the Name box at the left end of the formula bar .

   Name box

3. Type the name that you want to use to refer to your selection. Names can be up to 255 characters in length.
4. Press ENTER.

Note You cannot name a cell while you are changing the contents of the cell.

Create a name by using a selection of cells in the worksheet

You can convert existing row and column labels to names.

1. Select the range that you want to name, including the row or column labels.
2. On the Formulas tab, in the Defined Names group, click Create from Selection.
3. 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.

Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels.

Create a name by using the New Name dialog box

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 that you want to use for your reference. Names can be up to 255 characters in length.
3. In the name box, enter the defined name that you want to create.
4. To specify the scope of the name, in the Scope drop-down list box, select Workbook, or the name of a worksheet in the workbook.
5. Optionally, enter a descriptive comment up to 255 characters.

   Note If you save the workbook to Microsoft Office SharePoint Server Excel Services, and you specify one or more parameters, the comment is used as a tooltip in the Parameters toolpane.

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

   Cell reference The current selection is entered by default. To enter other cell references as an argument, click Collapse Dialog (which temporarily hides the dialog box), select the cells on the worksheet, and then press Expand Dialog .Constant Type an = (equal sign), followed by the constant value.

   Formula Type an = (equal sign) followed by the formula.

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

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

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 Formula tab, in the Defined Names group, click Name Manager.

View names

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

Notes:

• 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").

Resize columns

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

Sort names

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

Filter names

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:

Change a name

If you change 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, click the name that you want to change, and then click Edit. You can also double-click the name.

   The Edit Name dialog box is displayed.

3. Type the new name for the reference in the Name box.
4. Change the reference in the Refers to box, and click OK.
5. In the Name Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name.
   • To cancel unwanted or accidental changes, click Cancel , or press ESC.
   • To save changes, click Commit , or press ENTER.

Note The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

Delete one or more names

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+Click for each name in the group.
   • To select more than one name in a noncontiguous group, press CTRL+Click for each name in the group.
   • Click Delete. You can also press DELETE.
   • Click OK to confirm the deletion.

Note The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

See also:

• Create a 3-D reference to the same cell range on multiple worksheets
• Create an external reference (link) to a cell range in another workbook
• Create or change a cell reference