What is Excel Name Manager?
The Name Manager in Excel creates, edits, and deletes defined names for ranges, formulas, tables, and constants. As a result, when we use names, they are easier to read, understand and use in calculations. You use the Name Manager dialog box to work with all the names in a workbook.
It is under the “Formulas” tab in the “Defined Names” group. For instance, in the worksheet below, we have three columns containing some students’ names, Math scores, and Science scores. Go to the Formulas tab and choose “Define Name” to define named ranges. We have named the range containing the Math scores from B2 to B7 as “MathScore” and the range C2 to C7 containing the Science scores as “Sciscore.” To find the highest score for each student, enter the following formula in E2:
=IF(MathScore>SciScore, MathScore, SciScore)
Here, we have specified the ranges’ names instead of their references. Thus, the Name Manager in Excel gives us a list of the named ranges or formulas which are simple to use.
Table of Contents
Key Takeaways
- The Name Manager in Excel allows you to create, edit, and delete names for ranges, formulas, and constants. It can be accessed from the Formulas tab or the shortcut for Name Manager in Excel, Ctrl + F3.
- Another short method to create a named range is by selecting the range, typing a name in the Name Box, and pressing Enter. The Name Box also helps in easy navigation to the named ranges.
- You can choose to name a range of cells, a single cell, a constant, or even a formula. The names can also have scopes like the Workbook and Worksheet levels.
Explanation – Name Manager
A named range is a group of cells given a name, thus making them easier to understand when used in formulas. We can use the Name Manager dialog box to define, edit and delete all the ranges and table names in our worksheet. The most significant benefits of Name Manager in Excel are that all the defined ranges, tables, etc., can be used in our formulas, thereby reducing time and effort.
- Opening the Name Manager dialog box – Go to Formulas > Defined Names > Name Manager.
- To create a named range – Go to the Formulas tab in the Defined Names group, and click Define Name.
- You can also create a named range by selecting the range and typing the name in the Name Box.
Named ranges are helpful as they appear when the first letter of the name is typed, thereby helping us avoid mistakes while typing formulas.
Navigation is made very easy, and it can be used in data validation in excel.
How to Use Name Manager in Excel?
The Name Manager in Excel is used to edit and delete existing names. However, you can also create a new name with it. Here’s how!
To find Name Manager in Excel, go to the Formulas tab. In the Defined Names group, click Name Manager. You can also press Ctrl + F3.
The Name Manager dialog window opens. Then, in its top left corner, click the New button:
It opens the New Name dialog box where you add a named range.
Enter the name needed, and the range of cells, and press OK.
Naming a formula
You can similarly name a formula by typing it in the “Refers to” box. For instance, here, we have named the formula to find the sum of a range of cells, as shown below.
Type the name in a cell as shown below. When you type part of the name after the symbol =, the name automatically appears, as displayed below. Choose the name and press OK to get the sum.
Thus, the formula’s name can be used anywhere in the workbook without typing it out each time.
Examples
Let us look at some examples to understand the Name Manager in Excel in detail and how powerful it is for organizing data.
Example #1 – Creating, Editing & Deleting Named Range in Excel
Below is an example containing the details of the birthday party celebrations of three children in a household. Now, to check which party was the most expensive, let us create a named range to add Name Manager in Excel.
Step 1: Let us name the three ranges after the children. Go to Formulas > Defined Names > Name Manager.
The Name Manager Dialog Box pops up. Click on New…
Step 2: You get the “New Name” dialog box. Enter the details for Danny’s expenses here.
Step 3: Press OK. Now the named range appears in the Name Manager.
Step 4: Repeat the steps for the other two as well.
Step 5: We have made a mistake in Elizabeth_exp and missed including cell D6. To include it, click on the same and press Edit.
Make changes by clicking the Refers to box and selecting the range from D3 to D6. Press OK.
Step 6: Now, you can see that the value has been edited.
Step 7: To delete any entry, click Delete after selecting the entry. Here, we will delete Danny_exp.
- You get a popup confirmation asking whether to delete the named range. Now, press “OK,” and the range name is deleted.
- The entry is removed in the Name Manager.
Example #2 – Create an Excel Name for a Constant
Here’s an example of how to create a named constant. Here, we will convert the weight of a few people given in kilograms into pounds. The value in pounds can be found by multiplying the weight in kilograms by 2.2. Let us assign the constant value 2.2 a name.
- Now, to assign it a name, go to the Formulas tab – Defined Names – Define Name.
Step 1: Enter the name you want to assign and type the value of the constant, 2.2, in this case. You can also type the value 2.2 into a cell and give its reference here. Press OK. Now, the name kg_pound is assigned to 2.2.
You have the details of the weights of six people in the worksheet. Go to cell C2 and type the following formula.
=kg_pound*B2
Here, as soon as you start typing, the name of the constant appears in the drop-down list and you can select it.
Step 2: After typing the formula, press Enter. You get the volume of the cone.
Step 3: Use the same formula with the named constant and find the weight in pounds of the others as well.
Thus, a named constant is simple to use in a formula.
You can use it anywhere in your worksheet and calculate the weights in an instant!
Example #3 – Defining Name for a Formula
In this example, let us deal with naming a formula and show how simple it is in reducing time and effort, especially when you have large volumes of data. We have some numbers in Columns A to D.
Step 1: Now, let us name the range before naming the formula. Go to Formulas – Defined Names – Define Name.
Enter the details in the popup window.
Step 2: We need a name for the formula to find the sum of this range. Go to Define Name again and in the popup window, type the name and the formula as shown below.
Step 3: Type the name and use it anywhere if you need the sum of these figures.
Example #4 – Filters in Excel Name Manager
If many names are used in a workbook, you can click on the Filter button at the top right corner of the Excel Name Manager window to filter and view only the required names based on your filter. The filters found in Name Manager include:
Step 1: Using the previous example, let us name a range scoped to a particular worksheet. We choose the range from D2 to D6 and call it Range_D.
Step 2: Apply the filter “Names Scoped to Worksheet” in the Name Manager.
Step 3: You can see that the name has been filtered according to the filter applied.
Important Things to Note
- If you type a non-existing name in a formula (deleted), you get the #NAME error.
- You get a #REF error in the Name Manager if you delete the cells in a named range.
- Use Ctrl + F3 to open the Name Manager.
- F3 lists the names in a workbook.
- By default, Excel names are treated like absolute references in excel.
- In case, you use the same name for different scopes while naming, the worksheet takes precedence over the workbook level.
Frequently Asked Questions (FAQs)
In the Name Manager, press the Shift key, which selects all the named ranges, or you can use the Ctrl key for specified names you want to delete. Next, click on the Delete button at the top, and a prompt asks if you are sure to delete the names. Then, click OK to clean the Name Manager.
You can create a VBA (Visual Basic for Applications) macro in Excel to copy all the range names from one workbook to another.
You cannot disable the entire Name Manager in Excel. However, you can delete all the named ranges using the Shift key and pressing Delete. The formula or range these names refer to must be in a protected worksheet to disable the Delete button.
You can give a name to a range of cells, formulas, or tables and refer to them by name rather than reference. It helps us easily use the formulas and ranges when large amounts of data are involved.
Download Template
This article must be helpful to understand the Name Manager in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Name Manager in Excel. Here we explain how to use it to create, edit, and delete named ranges, constants, and tables with step-by-step examples. You may learn more from the following articles –
Leave a Reply