Naimul Hasan Arif
In terms of working with Excel, sometimes we can not find Excel Names in Name Manager. Let me go through some basic definitions before going through the solution to find Excel names not in Name Manager.
Define Name is an amazing feature where we can define a single cell, range of cells, constant value, or formula. We can mention the defined cell/cells/constant value/formula just by mentioning them whenever and wherever needed.
For further clarification, I am going to use a Dataset containing Product, Amount(lb), Price/lb, and Total columns to summarise a person’s shopping.
How to Name a Range
Using Define Name, we can mention all the values in a column rather than mentioning them separately. For this, we have to select the cells first, and then, we can give them a suitable name.
Steps:
- Here, I have selected cells C5:C9 and given this range a name “Amounts”.
- Similarly, I have selected cells D5:D9 and named them Prices.
- And lastly, I have selected cells E5:E9 and named them Totals.
We can use these defined names instead of selecting the cells separately. Here, I have calculated the Grand Total Amount which is the summation of cells C5:C9. At times using the SUM Function, I did not have to select all the cells. I just mentioned the defined name of those cells’ Amounts.
Here, I calculated the total value of the Amount (lb) column.
- Here. I selected D11 cell.
- Then, input the formula.
=SUM(Amounts)Where Amounts is a defined name that contains all the values of the Amount (lb) column.
- Press ENTER and we have the output with less effort.
There is another cool feature related to Define Name called Name Manager. We can create, edit and delete Defined Names through Name Manager.
For using Name Manager, we have to go first to the Formula ribbon and then select Name Manager.
A dialogue box will appear. Here, We can see the Defined Names. Using Name Manager, we can create, edit or delete the defined names.
Names Not in Name Manager in Excel: 2 Solutions to this Problem
1. Manual Solution to the Problem Names Not in Name Manager
If the Name Manager does not show defined names, that does not mean there are no defined names. We can check it manually which is not in the name manager.
Like, we can not see the defined names in Name Manager here.
But I showed a defined name named Amounts which does not appear in the Name Manager but still works perfectly.
So, We have to find the defined names as well as their ranges.
In order to find the defined names as well as their ranges, we need to follow some procedures.
Steps:
- Change the extension name into zip. Thus, it converts from an Excel file to a Zip file.
A warning box will appear.
- Hit YES.
The file will turn the file into a zip file.
- Then, Right-click on the mouse and select Open.
A new window will open.
- Open the xl folder.
- Next, select workbook.xml file and Copy files to clipboard .
- Paste the copied file to any other place or folder.
- Followingly, Select the workbook.xml file.
- Right-click on the mouse and choose Open with.
- Then, select WordPad.
From the file opened in Wordpad, we can have the defined names as well as their ranges.
Read More: [Fixed!] Name Manager Delete Option Greyed out in Excel
2. Applying VBA to Find Names Not Present in Name Manager
VBA (Visual Basic for Applications) is a very advanced way to find names in Name Manager.
Steps:
- Select Developer from the ribbon.
- Then, Choose Visual Basic.
A new window will open.
- Select Insert and hit Module.
- Now, write your VBA code in the Module.
Sub Find_Name_in_Name_manager()
For Each definename In ActiveWorkbook.Names
definename.Visible = True
Next
End Sub
Here, I used the Subroutine to declare Sub Find_Name_in_Name_Manager().
First I used a For loop to make the Names visible from ActiveWorkbook using ActiveWorkbook.Names Then, I also used Name.Visible property which returns a Boolean Value whether to show the value or not. I have chosen True to make the defined names visible.
- Go to the Name Manager
Finally, We can see the Excel Names in Name Manager.
Practice Workbook
You can practice here for more efficiency.
Download Practice Workbook
Conclusion
It is not an unusual thing to happen that Excel Names are not in Name Manager. We can easily solve this problem by applying the above-mentioned methods. For any further queries, you can comment below.
