Define and use names in formulas

By using names (name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value.), 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.

What do you want to do?


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

  1. Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
  2. Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
    Name box on the formula bar
  3. Type the name that you want to use to refer to your selection. Names can be up to 255 characters in length. To learn about additional rules for creating names, see the preceding section, Learn about syntax rules for names.
  4. Press Enter.

 Note   You can't name a cell while you are in Edit mode (changing the contents of the cell).

Top of Page Top of Page

Define 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.
    Create from Selection command
  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.

Top of Page Top of Page

Define a name by using the New Name dialog box

  1. On the Formulas tab, in the Defined Names group, click Define Name.
    Define Name command
  2. In the New Name dialog box, in the Name box, type the name that you want to use for your reference.

 Note    Names can be up to 255 characters in length. To learn about additional rules for creating names, see the section, Learn about syntax rules for names.

  1. 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.
  2. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

 Note    If you publish the workbook to Excel Services and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.

  1. In the Refers to box, do one of the following:
    • To enter a cell reference, type the cell reference.

 Tip    The current selection is entered by default. To enter other cell references as an argument, click Button image (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Button image to restore the dialog box to its normal size.

  • To enter a constant, type = (equal sign) and then type the constant value.
  • To enter a formula, type = and then type the formula.
  1. 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.

Top of Page Top of Page

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 that with errors (such as #DIV/0! or #NAME?), confirm the value and reference of a name, view or edit descriptive comments, or determine a name's scope. In this dialog box, you can also sort and filter the list of names, and easily add, change, or delete names.

To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

Name Manager command

View defined names and table names

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

This Column: Displays:
Icon and 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
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

One of the following:

  • A worksheet name, if the scope is the local worksheet level.
  • "Workbook", if the scope is the global worksheet level.
Comment

Additional information about the name up to 255 characters. The following are representative examples:

  • This value will expire on May 2, 2010.
  • Don't delete! Critical name!
  • Based on the ISO certification exam numbers.

Note    If you publish the workbook to Excel Services and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.

 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 Microsoft 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:

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 that contain 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.

Top of Page Top of Page

Change a name

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

  1. On the Formulas tab, in the Defined Names group, click Name Manager.
    Name Manager command
  2. In the Name Manager dialog box, click the name that you want to change, and then click Edit.

Tip    You can also double-click the name.

  1. In the Edit Name dialog box, in the Name box, type the new name for the reference.
  2. In the Refers to box, change the reference, and then click OK.
  3. 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 Cancel button, or press Esc.
    • To save changes, click Commit Enter button, or press Enter.

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

Top of Page Top of Page

Delete one or more names

  1. On the Formulas tab, in the Defined Names group, click Name Manager.
    Name Manager command
  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. You can also press the Delete key.
  5. Click OK to confirm the deletion.

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

Top of Page Top of Page

Learn more about using names

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).), 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 Type Example with no names Example that uses names
Reference =SUM(C20:C30) =SUM(FirstQuarterSales)
Constant =PRODUCT(A5,8.3) =PRODUCT(Price,WASalesTax)
Formula =SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) =SUM(Inventory_Level,-Order_Amt)
Table C4:G36 =TopSales06

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 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. For example:

  • If you have defined a name, such as Budget_FY08, and its scope is Sheet1, that name is recognized only in Sheet1, but cannot be recognized in other sheets without some additional information, such as the name of its worksheet.

To use a local worksheet name in another worksheet, you can increase its scope 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 limited to the workbook (in other words, the Excel file), 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:

WorkbookFile!GrossProfit

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 using names

You can 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. Type the name you want to use directly in the box (by default, the contents of the Name box is the address of the cell; for example, A1). The image shown here also shows the defined name, FirstName, in the Name Manager box after it's been defined.

    Name box
  • Existing row and column labels of a selection of cells    You can use the Create from Selection command to 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 (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

You can use a defined name in a formula by entering it as follows:

  • Typing    Entering the name; for example, as an argument in a formula. In the image shown here, ID is the defined name that refers to the range B2:B5.
    Defined name used in a formula
  • Using Formula AutoComplete    Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you. In the image shown here, the defined name ID is available in the drop-down list for use in the formula.
    A defined name shown in the AutoComplete list
  • 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.

Keeping track of names

To keep track of all defined names in a workbook so that you can see them at a glance, you can create a list that displays all defined names and their locations.

  1. 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).
  2. Select a cell that will be the upper-left corner of the list.
  3. On the Formulas tab, in the Defined Names group, click Use in Formula, and then click Paste Names.
  4. In the Paste Names dialog box, click Paste List.
    Pasting all defined names into the worksheet

    By clicking Paste List, the names and ranges of the two defined names (FirstName and ID) are pasted into the worksheet.

Top of Page Top of Page

Learn about 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 uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all 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 as part of a name. Use the underscore character (_) and period (.) as word separators; for example, 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.

Top of Page Top of Page

 
 
Се однесува на:
Excel 2013