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. Show
Contents
The problem of hidden defined namesThe built-in Name Manager in Excel doesn’t show all defined namesPlease 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 problemThe 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.
Solution 1: Access named ranges manuallyThe 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.
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 rangesOur 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 visibleThis 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 namesThe following VBA macros deletes all names in your workbook.
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 namesThis 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 ToolsBecause 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) SummaryDefined 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 do you fix Excel name already exists?This error generally arises when there are some erroneous names in the worksheet. Press Ctrl+F3 (the Excel name manager box will show up). On the right hand side there will be a filter button - select "Names with error" and once all of them show up, delete the erroneous names.
How do I get Excel to recognize a name?Use names in formulas. Select a cell and enter a formula.. Place the cursor where you want to use the name in that formula.. Type the first letter of the name, and select the name from the list that appears. Or, select Formulas > Use in Formula and select the name you want to use.. Press Enter.. How do I fix name conflict in Excel?Resolution. Click Yes to continue using the conflicting range name in the destination workbook. What happens when you click Yes? ... . Click Yes to All to continue using all possible conflicting range names in the destination workbook. ... . Click No to rename the conflicting name range.. What is the error if Excel doesn't recognize a name?The #NAME error occurs in Excel when the program doesn't recognize something in your formula. The most common cause is a simple misspelling of the function being used. For example, in the image below, the formula has VLOOKUP spelled incorrectly in the first instance (F5), so it produces the #NAME? error.
|