How to hide / unhide all named ranges in Excel?
Named ranges are a practical tool in Excel, allowing you to assign memorable labels to important cell ranges, formulas, or data sets. By using named ranges, you can simplify complex calculations and make your spreadsheets more readable. However, in certain situations, these named ranges might contain sensitive formulas or critical references that you do not want to be accidentally altered or deleted by others using your workbook. In such cases, hiding your named ranges can help protect them from accidental edits, thereby maintaining the integrity of your data and formulas.
Below are two primary methods to hide or unhide all named ranges in Excel, each suitable for different scenarios and user preferences:
Hide or display all named ranges with VBA codes
Easily hide or unhide named range with Kutools for Excel
Hide or display all named ranges with VBA code
Excel does not provide a direct, built-in function to show or hide named ranges. If you would like to make all named ranges invisible—or restore their visibility—the most flexible and powerful option is to use VBA code. This method is especially useful for users managing a large number of named ranges or wishing to automate this process. However, it’s important to note that running VBA scripts should be done with care: always save a backup of your workbook before making changes using VBA, as actions cannot be easily undone.
1. Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window. If you are new to VBA, you will find this under the Developer tab in the Ribbon. If the Developer tab is not visible, you can enable it in Excel Options.
2. In the VBA editor, click Insert > Module to add a new module. Next, copy and paste the following code into the Module Window.
VBA: Hide all named ranges in Excel
Sub HideNames()
'Update 20140318
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.Visible = False
Next
End Sub
3. With the module active, press F5 to run this code. All of the named ranges in your workbook will be hidden and will no longer be displayed in the Name Box or the Name Manager. Please refer to the screenshots below for a visual demonstration:
 |  |  |
If you decide to unhide the named ranges and make them visible again, you can use another VBA script. This makes it easy to toggle the visibility of named ranges whenever needed. Be sure to double-check which workbook you are editing before running the script, especially if you have multiple workbooks open.
VBA: Display all named ranges in Excel
Sub ShowNames()
'Update 20140318
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.Visible = True
Next
End Sub
After inserting and running this code (following the same steps as above), all hidden named ranges in your workbook will be visible again.
Applicable scenario: VBA is recommended when you need to hide or unhide all named ranges at once—especially in large workbooks—or if you prefer automation.
Advantages: Quickly processes all ranges; works for large batches.
Limitations: Requires basic familiarity with VBA; changes are workbook specific; not suitable for Excel Online.
Tips and reminders: If you receive a macro security warning upon running code, ensure macros are enabled. If hiding named ranges that are referenced by formulas, be aware that hiding does not affect formula calculations—formulas will still work as expected, only the range names will be hidden from the user interface.
Easily hide or unhide named range with Kutools for Excel
In addition to VBA, Excel users can take advantage of the Hide/Unhide Range Name utility available in Kutools for Excel. This feature is especially user-friendly for those who prefer not to use VBA or who are looking for a more straightforward, graphical approach. With this utility, you can easily manage individual named ranges—hiding or unhiding specific ones with just a few clicks—rather than applying changes to every range at once. This flexibility is ideal when you want to protect only certain sensitive ranges, or temporarily hide names for cleaner worksheet navigation.
1. On the Ribbon, go to Kutools > Name Tools > Hide/Unhide Range Name. Consult the screenshot below for help with locating this feature:
2. The Hide/Unhide Range Name dialog box will appear. Here you will see a complete list of all named ranges in your workbook, displayed in the Name list box.
To hide a particular named range, simply uncheck its corresponding box in the list. To make a hidden range visible again, check the box. Multiple ranges can be toggled at once, offering precise control over which names are visible or hidden. See the dialog box example below:
As shown, the named ranges highlighted in yellow ( Add_range and Sheet1_range) will remain visible in your workbook, while the unchecked names (New_Range1 and Test_range) will be hidden from the Name Box and Name Manager. Changes take effect as soon as you apply them.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Applicable scenario: Kutools is well-suited if you want to selectively manage individual named ranges, or prefer a visual, mouse-driven solution.
Advantages: No need for VBA experience; offers fine-grained control; straightforward interface.
Practical tips: Use the Hide/Unhide Range Name utility when sharing your workbook with colleagues to reduce the risk of accidental editing of key ranges. If you're working with sensitive or proprietary formulas, hiding the corresponding named ranges can prevent unwanted disclosure or modification.
Troubleshooting: If changes to the named ranges are not taking effect, ensure you are working in the correct workbook window. Also, if you find the Hide/Unhide Range Name dialog missing names, try refreshing the workbook or restarting Excel.
Alternative Solutions: If neither VBA nor Kutools is available, you can manually manage named ranges through Excel’s Name Manager (Formulas > Name Manager). Although this does not provide a direct show/hide capability, you can temporarily delete and later recreate named ranges as needed. For step-by-step guidance on managing names, see the How to list named ranges in Excel? article.
Related articles:
- How to quickly delete all named ranges in Excel?
- How to list named ranges in Excel?
- How to create dynamic named range in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in