Named Ranges in Excel: See All Defined Names (Incl. Hidden Names)

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 problem of hidden defined names

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.

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.

1. Create a copy of your Excel workbook and rename it. Delete .xlsx in the end and replace it with .zip.
2. 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.
3. 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.
4. 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.

VBA macro to remove all hidden names

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.

Summary

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“.