Names in formulas - Microsoft Support (2024)

Skip to main content

Microsoft

Support

Support

Sign in

Sign in with Microsoft

Sign in or create an account.

Hello,

Select a different account.

You have multiple accounts

Choose the account you want to sign in with.

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 More...Less

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 understand at first glance. The following information shows common examples of names and how they can improve clarity.

Example Type

Example with no name

Example with a name

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

Learn more about using names

There are several types of names you can create and use.

Defined nameA name representing a cell, range of cells, formula, or constant value. You can create your own defined name, or Excel can create a defined name for you, such as when you set a print area.

Table nameA 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.

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.

    To use a local worksheet name in another worksheet, you can qualify it by preceding it with the worksheet name. For example:

    Sheet1!Budget_FY08

  • 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 other workbooks.

A name must always be unique within its scope. Excel prevents you from defining a name that already existswithin 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 defined for the worksheet because the local worksheet level takes precedence over the global workbook level. If you want to override the precedence and use the workbook name, you can disambiguate the name by prefixing the workbook name. For example:

WorkbookFile!GrossProfit

You can override the local worksheet level for all worksheets in the workbook. One exception is for the first worksheet, which always uses the local name if there is a name conflict that cannot be overridden.

You define a name by using the:

  • Defined Names box on the formula bar This is best used for creating a workbook level name for a selected range.

  • Define namefrom selection You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.

  • New Name dialog boxThis 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 AutoCompleteUse the Formula AutoComplete drop-down list, where valid names are automatically listed for you.

  • Selecting from the Use in Formula commandSelect 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.

The following is a list of syntax rules for creating and editing names.

  • Valid charactersThe 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.

    Tip: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are used as 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 disallowedNames 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, such as, Sales_Tax or First.Quarter.

  • Name lengthA name can contain up to 255 characters.

  • Case sensitivityNames can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you create the name Sales and then another name called SALES in the same workbook, Excel prompts you to choose a unique name.

Define 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.

    Names in formulas - Microsoft Support (2)

    Name box

  3. Type the name 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.

You can convert existing row and column labels to names.

  1. Select the range you want to name, including the row or column labels.

  2. On the Formulas tab, in the Defined Names group, click Create from Selection.

    Names in formulas - Microsoft Support (3)

  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. A name created by using this procedure refers only to the cells that contain values and excludes the existing row and column labels.

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

    Names in formulas - Microsoft Support (4)

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

  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:

    • 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 Collapse Dialog Names in formulas - Microsoft Support (5)(which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog Names in formulas - Microsoft Support (6).

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

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

  6. 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 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.

Names in formulas - Microsoft Support (7)

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. Names in formulas - Microsoft Support (8)

  • A table name, which is indicated by a table name icon. Names in formulas - Microsoft Support (9)

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 worksheet level.

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.

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

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

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

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.

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

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.

    Names in formulas - Microsoft Support (10)

  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.

  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.

    • To cancel unwanted or accidental changes, click Cancel Names in formulas - Microsoft Support (11), or press ESC.

    • To save changes, click Commit Names in formulas - Microsoft Support (12), or press ENTER.

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

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

    Names in formulas - Microsoft Support (13)

  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 DELETE.

  5. Click OK to confirm the deletion.

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

Need more help?

You can always ask an expert in the Excel Tech Communityor get support inCommunities.

See Also

Define and use names in formulas

Need more help?

Want more options?

Discover Community

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

Names in formulas - Microsoft Support (14)

Microsoft 365 subscription benefits

Names in formulas - Microsoft Support (15)

Microsoft 365 training

Names in formulas - Microsoft Support (16)

Microsoft security

Names in formulas - Microsoft Support (17)

Accessibility center

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

Names in formulas - Microsoft Support (18)

Ask the Microsoft Community

Names in formulas - Microsoft Support (19)

Microsoft Tech Community

Thank you for your feedback!

×

Names in formulas - Microsoft Support (2024)

FAQs

Names in formulas - Microsoft Support? ›

You can create formulas that are easy to understand by using descriptive names to represent cells, ranges of cells, formulas, or constant values.

Can names be used in formulas? ›

You can create formulas that are easy to understand by using descriptive names to represent cells, ranges of cells, formulas, or constant values.

How do you add defined names to existing formulas? ›

Here's how:
  1. Select one or more formula cells that you want to update.
  2. Go to the Formulas tab > Define Names group, and click Define Name > Apply Names…
  3. In the Apply Names dialog box, click on the names you want applied, and then click OK.
Mar 20, 2023

What characters are not allowed in Excel names? ›

Important: Worksheet names cannot:
  • Be blank .
  • Contain more than 31 characters.
  • Contain any of the following characters: / \ ? * : [ ] ...
  • Begin or end with an apostrophe ('), but they can be used in between text or numbers in a name.
  • Be named "History". This is a reserved word Excel uses internally.

Can a text be used in a formula? ›

We often hear that you want to make data easier to understand by including text in your formulas, such as "2,347 units sold." To include text in your functions and formulas, surround the text with double quotes ("").

How do I enable defined names in Excel? ›

On the Formulas tab, in the Defined Names group, click Define Name. 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.

How do you combine names in Excel formulas? ›

To combine first and last names, use the CONCATENATE function or the ampersand (&) operator. Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function.

Where is name manager in formula tab? ›

On the Formulas tab, in the Defined Names group, click Name Manager. 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. In the Edit Name dialog box, in the Name box, type the new name for the reference.

Can an Excel formula reference a tab name? ›

Here's how: Click on the cell where you want to reference the current sheet name. Type =CELL("filename") in the formula bar and press Enter. The result will be a string that contains the full path and name of the workbook, followed by a square bracket, followed by the sheet name in quotes.

How do you replace the existing cell addresses with the defined names? ›

To replace the references with defined names in all formulas on the active sheet, select any single blank cell. Go to the Formulas tab > Defined Names group, click the arrow next to Define Name, and then click Apply Names… In the Apply Names dialog box, select one or more names, and click OK.

What special characters are not allowed in Excel sheet name? ›

Make sure the name you entered does not exceed 31 characters. Make sure the name does not contain any of the following characters: : \ / ? * [ or ]

What is not allowed in a defined name in Excel? ›

The following is a list of syntax rules for creating and editing 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.

What are the illegal characters in Excel file name? ›

Illegal Filename Characters
  • # pound.
  • % percent.
  • & ampersand.
  • { left curly bracket.
  • } right curly bracket.
  • \ back slash.
  • < left angle bracket.
  • > right angle bracket.

Why does my formula say name? ›

The formula has a typo in the defined name

If the syntax incorrectly refers to a defined name, you see the #NAME? error.

Can you reference a sheet name in a formula? ›

Here's how: Click on the cell where you want to reference the current sheet name. Type =CELL("filename") in the formula bar and press Enter. The result will be a string that contains the full path and name of the workbook, followed by a square bracket, followed by the sheet name in quotes.

What Excel formula counts names? ›

=COUNTA(A2:A100)

All we had to do was type a different range (A2:A100), and it counts all of the first names, giving us an answer of 85. If you type more names at the bottom of the spreadsheet, Excel will automatically recalculate this value.

Top Articles
Latest Posts
Article information

Author: Nicola Considine CPA

Last Updated:

Views: 5243

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Nicola Considine CPA

Birthday: 1993-02-26

Address: 3809 Clinton Inlet, East Aleisha, UT 46318-2392

Phone: +2681424145499

Job: Government Technician

Hobby: Calligraphy, Lego building, Worldbuilding, Shooting, Bird watching, Shopping, Cooking

Introduction: My name is Nicola Considine CPA, I am a determined, witty, powerful, brainy, open, smiling, proud person who loves writing and wants to share my knowledge and understanding with you.