Formulas in Excel often use data and values from other cells using references to those cells. If you have many formulas, the cell references can be confusing, making it hard to understand your formulas.
the Names Defined The Excel feature makes your formulas and other data less confusing and easier to understand. Instead of referring to a cell that contains a value or formula or cell range by row and column headers (A1, B2, etc.), you can use a defined name assigned to that cell or cell range.
Today we'll look at working with named cell ranges, including creating names, naming rules and scopes, and how to edit, delete, view, and use names.
Do not confuse defined names with table names. An Excel table is a collection of data stored in records (rows) and fields (columns). Excel assigns a default name (Table1, Table2, etc.) to each table you create, but you can change the names.
Previously we introduced you to Excel Tables 8 Tips to Learn Excel Fast 8 Tips to Learn Excel Fast Not as comfortable with Excel as you'd like? Get started with simple tips for adding formulas and managing data. Follow this guide and you'll be up to speed in no time. Read More How to use an Excel pivot table for data analysis. How to use an Excel pivot table for data analysis. It is frequently used for big data analysis. Follow our step-by-step demo to learn all about it. Read more . For more information on tables in Excel, see Microsoft's support page on creating tables.
When creating defined names in Excel, you must follow certain rules. For example, defined names cannot contain spaces, and the first character must be a letter, an underscore (_), or a backslash (\).
For a complete list of naming rules, see the "Learn about naming syntax rules" section of this Microsoft support page.
The scope of a defined name in Excel refers to where the name is recognized without qualification, such as a sheet name or workbook file name. Each name can have a local worksheet level scope or a global workbook level scope.
For more information about the scope of a defined name, see the “The scope of a name” section of this Microsoft support page.
You have a few different options for creating named ranges in Excel. You can define names using the Name in the Formula bar, using the header cell text for a group of selected cells, or using the New name dialog box.
The names do not have to refer to the cells. You can use a name to label a value or formula that you use in many places in your workbook.
When defining a name for a cell or range of cells, absolute cell references are used by default.
Using the Name box is the easiest way to define a name for a group of cells. Defining a name using Name box creates names only scoped to the workbook level, which means the name can be referenced anywhere in the workbook it was created in without adding any sheet names or workbook file names.
We'll cover another method that allows you to create worksheet-level names later.
Use the Name box to define a name, do the following:
Be sure to follow the naming rules above.
If you have added row or column headers to your data, you can use these headers as names.
To define a name from a header, do the following:
If the tag contains spaces or other invalid characters, such as an ampersand (&), they are replaced with an underscore.
About Create names from selection dialog box, select the location of the header tag that you want to use as the name.
For example, the header tag in the image above is at the top of the selected column. Therefore, we check the Top Row box and click OK .
The name is a workbook level name and is applied to all selected cells except the header label cell. When you select the cells that the name refers to, without the header label cell, you will see the name in the Name box.
the New name the dialog box provides more options when creating names, such as specifying scope at the worksheet level or adding a comment to a name. Also the New name The dialog allows you to define a name for a value or a formula.
Use the New name dialog to name a cell or range of cells, start with the following:
About the New name dialog box, specify the following information:
The button on the right side of the Refers to box allows you to minimize the New Name in the dialog box, and select cells in the worksheet to enter a range of cells. We'll talk more about how to use that button in the next section, “Editing a named range”.
When you have finished entering the information for the name, click OK to return to the worksheet.
Once you have defined a name, you can change the name, its comment and its reference.
To edit a defined name or named range, click Name Manager in the Defined names Formulas section tab.
If you have a long list of names, you can filter them to find the one you want to edit.
Click Filter in the upper right corner of the Name Manager dialog box. Then select the types of names you want to display. You can select multiple filters by clicking Filter again and selecting another option.
To clear all filters and display all names again, click Filter and select Clear Filter .
To change the name or comment, select the name you want to change and click Edit .
Change the Name or Comment , or both, and click OK .
You cannot change the Scope of an existing name. Remove the name and redefine it with the correct scope.
You can change the cells name Refers to about the Edit name cash register. But you can also do this directly in the Name Manager Dialog box, which we will do next. The method to change the Refers to The cell reference is the same in both dialogs.
To change the cell or range of cells to which this name refers in the Name Manager dialog box, first select the name for which you want to change the cell reference. Then click the up arrow button on the right side of the Refers To box.
The Name Manager dialog is reduced to just the Refers to cash register. To change the cell reference, do the following:
You'll see the new cell reference in the Refers To box and the fully restored Name Manager dialog box.
To accept the new cell reference, click the green check mark button. Or to revert the change to the original cell reference, click the black X button.
Click Close to close the Name Manager dialog box.
The Name Manager the dialog lists all the worksheet level and workbook level names you have defined in your workbook, no matter which worksheet is currently active. But while the dialog is open, you can't work on your worksheet.
It can be helpful to have a list of names directly on the current worksheet. That way you can move around and decide which names need to be edited or which names you want to remove by referring to your list.
You can generate a list of directly in an empty area of a worksheet. This list includes workbook level names and worksheet level names that are scoped to the currently active worksheet.
To start generating a list of names, do the following:
About the Paste name dialog box, click Paste List .
The names and their respective cell references, values, and formulas are pasted into the worksheet cells.
Now you can review your list and decide what to do with the names. You can delete this list in the worksheet once you no longer need it.
If you have some names that you no longer use, it's a good idea to delete them. Otherwise, your list of names gets messy and unwieldy.
To open the Name Manager , click Name manager in the Defined names Formulas section tab.
About the Name Manager dialog box, select the name you want to delete and click Delete . Click OK in the confirmation dialog.
If a name refers to a range of cells, you can use that name to quickly navigate to and select that range of cells.
To jump to a named range, click the down arrow on the Name on the left side of the Formula bar and select the name you want.
Worksheet-level names are only displayed in the Name drop-down list box if the worksheet they were created in is the currently active worksheet.
Also, you can type a name for a range of cells you want to jump to in the Name press and press Enter . But make sure you have already defined the name. Otherwise, it will apply that name to the currently selected cell or range of cells.
Using defined names in formulas makes your formulas easier to understand if you use descriptive names.
There are several ways to enter a name into a formula. If you know exactly what name you want to use, you can type the name directly into the formula.
You can also use Formula AutoComplete. As you type your formula, Excel automatically lists the valid names, and you can select a name to enter into the formula. For example, the name Total It is suggested to us in the following worksheet because the selected cell range has that name.
You can also start typing your formula and then click Use in Formula in the Defined names Formula section tongue. Then select the name you want to use in your formula from the dropdown list.
One last method to use a name in a formula is to start typing your formula and press F3 . Then select the name you want to use in the Paste name dialog and click OK , or press Enter .
Defined names in Excel can help keep your workbooks organized and easy to understand. Use names not only for cell ranges, but also to refer to values and formulas that you use frequently.
For more information, see our beginner's guide to Microsoft Excel The Beginner's Guide to Microsoft Excel The Beginner's Guide to Microsoft Excel Use this beginner's guide to start your experience with Microsoft Excel. The basic spreadsheet tips here will help you start learning Excel on your own. Read more.
