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.

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

1 2 3 4 5 6

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.

1 2 3 4 5 6

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.

(Source: https://professor-excel.com/named-ranges-excel-hidden-names/)