Excel has a useful feature: Named Ranges. You can name single cells or ranges of cells in Excel. Instead of just using the cell link, e.g. =A1, you can refer to the cell (or range of cell) by using the name (e.g. =TaxRate). Excel also provides the “Name Manager” which gives you a list of defined names in your current workbook. The problem: It doesn’t show all names. Why that is a problem and how you can solve it is summarized in this article.
The built-in Name Manager in Excel doesn’t show all defined names
Please take a look at the screenshots below. On the left-hand side you can see the built-in Excel Name Manager (you can access it though Formulas–>Name Manager). The right-hand side is a screenshot of the Excel add-in “Professor Excel Tools” (more to that later). They were both taken with the same Excel workbook.
As you can see, the built-in Name Manager only shows none-hidden names. There are 4 names names in this workbook which are not hidden. But there are thousands more defined names in this particular workbook. Excel just doesn’t show them to you.
Have you ever built an app based on Excel?
Why not showing all names is a problem
The problem with not showing all defined names is that you can’t delete them. Because they are hidden. Let’s talk a little bit about defined names in Excel.
- Defined names are copied with each worksheet. So if your workbook has with thousands of names, they will be copied to a new workbook if you copy one worksheet.
- Even if you delete the worksheet you copied, the defined names stay. That means, the name just keep accumulating.
- Besides that the names enlarge the workbook, you might run into trouble when duplicating worksheets. In worst case, you have to confirm the following dialogue box for each name separately.
Solution 1: Access named ranges manually
The first method is to access the source file of your Excel workbook. Please refer to this article for information about the source contents of an Excel file.
- Create a copy of your Excel workbook and rename it. Delete .xlsx in the end and replace it with .zip.
- Open the file and navigate to the folder “xl”. Copy the file “workbook.xml” and paste it into an empty folder outside the .zip-file.
- Open the “workbook.xml” file with the text editor (right-click on the file and then on “Open with” –> “Notepad”). If you just want to see the named ranges without editing or removing them, you could also just double-click on the file “workbook.xml”. It usually opens in your web browser. The advantage: The layout is much better to read.
- Now you can see all named ranges. They are listed between <definedNames> and </definedNames>.
Say, you want to remove all named ranges. Then you could delete all the content between <definedNames> and </definedNames>.
After editing the file you should save it and copy it back into the .zip-file. Replace the existing “workbook.xml” file there. Rename the complete .zip-file back to .xlsx. Now you can open it. Excel might notice that you changed the file and ask you if you’d like to recover as much as possible.
Please note: Tempering with the source code of your Excel file might damage the file. So please only work with copies of your file.
Solution 2: Use a VBA macro to see all named ranges
Our next method to edit hidden names in Excel is via VBA macros. We have prepared two VBA macros. Please insert a new VBA module and paste the following codes. If you need assistance concerning macros, please refer to this article.
VBA macros to make all names visible
This first VBA macros makes all defined names visible. You can then edit them within the built-in Name Manager (go to Formulas–>Name Manager). After pasting this code snipped into the new module, place the cursor within the code and click on the play button on the top of the VBA editor (or press F5 on the keyboard).
Sub unhideAllNames() 'Unhide all names in the currently open Excel file For Each tempName In ActiveWorkbook.Names tempName.Visible = True Next End Sub
If you want to hide all names in your current workbook, replace tempName.Visible = True by tempName.Visible = False.
VBA macro to remove all names
The following VBA macros deletes all names in your workbook.
Sub removeAllNames() 'Remove all names in current workbook, no matter if hidden or not For Each tempName In ActiveWorkbook.Names tempName.Delete Next End Sub
One word of caution: Print ranges and database ranges are also stored as defined names. Before you delete all names, make sure that you really don’t need them any longer.
Have you ever built an app based on Excel?
This last VBA macro only deletes all hidden names in your workbook.
Sub removeAllHiddenNames() 'Remove all hidden names in current workbook, no matter if hidden or not For Each tempName In ActiveWorkbook.Names If tempName.Visible = False Then tempName.Delete End If Next End Sub
Solution 3: Use Professor Excel Tools
Because this problem is – if it occurs – very troublesome, we’ve included our own version of a “Name Manager” into the Excel add-in “Professor Excel Tools“. As you can see on the screenshot on the right-hand side, it shows all names including hidden names.
You can then hide or unhide defined names. Or directly delete them.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Defined names are usually useful in Excel. Unfortunately, with built-in methods, hidden names can’t be edited. That could be a problem when you copy a worksheets and have to confirm for each name separately if you want to keep it or rename it. With thousands of names that could take a while.
Unfortunately, Excel doesn’t provide functions to edit such hidden names. You could work on them manually, with VBA macros or third party add-ins such as “Professor Excel Tools“.
How to use vba to find name with error to delete it?
Deleting the names from the embedded file worked like a charm! Many thanks.
Is there a way (VBA or manually) add ranges in bulk from the XML file? For example, I would like to add references to over 30 individual cells (imported data source with monthly data) without having to go through the cumbersome process of finding and highlighting each one every time. My UI automatically opens the worksheet and does not let me simply type in the data. If I could just cut and paste text into an XML file it would be easier to bulk update/name ranges.
Thank U. really helpful
Is there some way to identify UNUSED names? With the intention of selectively deleting them We may identify some unused names we want to keep..
thank you for this post as it has helped with removing thousands of Named Ranges that existed in a report from my client.
The problem is they keep sending us these reports that have all these hidden named ranges and links but they also have thousands and thousands of styles that the reports drag along with them.
I have a macro for removing the styles but I have to run it on every file after opening one of their reports. Is there a similar way (to the above for named ranges) to get rid of all the hidden styles permanently?
It is causing havoc with my Excel.
thanks for the tips.. it worked for me!
This helped me so much. Thank you