[Solved!] Names Not in Name Manager in Excel (2 Solutions)

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 the group of cells a suitable name.

Steps:

Here, I have selected cells C5:C9 and named this range “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 defined names, and delete the 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.

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

Solutions of Names Not in Name Manager.xlsm

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.

<< Go Back to Name Manager | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!