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.
Download Practice Workbook
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.
- Here, I have selected cellsC5:C9 and given them a Define 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.
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.
For using Name Manager, we have to go first to the Formula ribbon and then select Name Manager.
2 Solutions to the Problem Names Not in Name Manager in Excel
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.
- 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.
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.
- 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.
Read More: How to Use Named Range in Excel VBA (2 Ways)
You can practice here for more efficiency.
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.
- Excel VBA to Create Named Range from Selection (5 Examples)
- Dynamic Named in Range Excel (Both One and Two Dimensional)
- How to Use Dynamic Named Range in an Excel Chart (A Complete Guide)
- Set Value to a Named Range with Excel VBA (3 Methods)
- How to Change Excel Column Name from Number to Alphabet (2 Ways)