Excel’s robustness lies not just in its complex formulas and pivot tables but also in its ability to organize and present data effectively. An essential part of this organization is the ability to hide and unhide sheets as needed. Whether you're a novice user looking to streamline your workbook or an advanced user managing complex data sets, this guide will equip you with the knowledge to unhide sheets in Excel effortlessly.

• Unhide all sheets
• Unhide multiple sheets (specified worksheets)
• Unhide worksheets with specific text in the sheet name

Unhide sheets one by one manually

The simplest method to unhide sheets in Excel is to do it manually, which works best when dealing with a small number of sheets.

1. In your Excel workbook, right-click on any visible sheet tab at the bottom of your Excel workbook.

2. Select Unhide from the context menu.

3. The Unhide dialog box will appear, listing all hidden sheets. Select the sheet you wish to unhide and click OK.

4. Then the selected hidden sheet is displayed now. Repeat the above 3 steps to unhide more hidden worksheets one by one.

One click to unhide all sheets with a powerful feature – Kutools for Excel

Experience one-click convenience with Kutools for Excel’s powerful Unhide All Hidden Sheets feature. This remarkable tool empowers you to effortlessly reveal every hidden sheet with a single click, bypassing the tedious process required by Excel's default Unhide option, which limits you to unhiding one sheet at a time. Say goodbye to the tedious repetition of steps for each worksheet, and welcome a more efficient, time-saving approach to managing your Excel documents with Kutools for Excel.

After installing Kutools for Excel, click Kutools > View > Unhide All Hidden Sheets to make all hidden sheets visible.

Result

All hidden sheets are shown at once.

Unhide sheets with VBA

Amazing! Using Efficient Tabs in Excel Like Chrome, Edge, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Advanced Excel users or those comfortable with Excel's programming capabilities can use VBA to unhide sheets. This method provides flexibility and can be customized to suit various needs, such as unhiding all sheets, multiple selected worksheets, or sheets with specific text in the name.

In this section, we will explore three VBA scripts designed to meet three distinct requirements for unhiding sheets.

Unhide all sheets

This VBA script makes all sheets in the workbook visible.

Step 1: Open the VBA module editor and copy the code

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module and paste the following code in the Module Window.
3. VBA code: Unhide all sheets

   Sub UnhideAllSheets()
   'Updateby Extendoffice
       Dim ws As Worksheet
       For Each ws In ThisWorkbook.Worksheets
           ws.Visible = xlSheetVisible
       Next ws
   End Sub

Step 2: Execute the code to get the result

After pasting this code, please press F5 key to run this code. And all the hidden sheets will be displayed at once.

Unhide multiple sheets (specified worksheets)

This VBA script allows you to unhide a list of specified sheets.

Step 1: Open the VBA module editor and copy the code

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module and paste the following code in the Module Window.
3. VBA code: Unhide specified sheets

   Sub UnhideSelectedSheets()
   'Updateby Extendoffice
       Dim sheetNames As Variant
       sheetNames = Array("Sheet5", "Sheet6") ' Customize the list with your sheet names
       Dim name As Variant
       For Each name In sheetNames
           Sheets(name).Visible = xlSheetVisible
       Next name
   End Sub
   

Step 2: Execute the code to get the result

After pasting this code, please press F5 key to run this code. And the specified hidden sheets (Sheet5 and Sheet6 ) will be displayed at once.

Unhide worksheets with specific text in the sheet name

To unhide sheets based on specific text in their names, use the following VBA script.

Step 1: Open the VBA module editor and copy the code

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module and paste the following code in the Module Window.
3. VBA code: Unhide sheets with specific text in the sheet name

   Sub UnhideSheetsWithSpecificText()
   'Updateby Extendoffice
       Dim ws As Worksheet
       For Each ws In ThisWorkbook.Worksheets
           If InStr(ws.Name, "Excel") > 0 Then
               ws.Visible = xlSheetVisible
           End If
       Next ws
   End Sub

Step 2: Execute the code to get the result

After pasting this code, please press F5 key to run this code. And the hidden sheets with the text Excel in the name will be displayed at once.

Unhide all sheets with Custom View

Custom Views in Excel lets you bypass the hassle of unhiding sheets one by one by allowing you to save a view of your workbook when all sheets are visible. Essentially, you take a snapshot of your workbook when everything is visible. Then, if you hide any sheets, you can easily return to that initial state where all sheets are visible with just a click. So, for the best results, set up this custom view at the beginning of your work, before hiding any sheets.

Step 1: Ensure All Sheets Are Visible

Before you can create a custom view to unhide all sheets, you must first ensure that all sheets in your workbook are visible.

Step 2: Navigate to Custom Views

Go to the View tab on the ribbon. In the Workbook Views group, click on Custom Views.

Step 3: Create a New Custom View

1. In the Custom Views dialog box, Click the Add button.



2. In the Add View dialog box, enter the name for your custom view, such as All Sheets Visible. Click OK.



Step 4: Using Your Custom View

Whenever you need to unhide all sheets in your workbook, you can simply go back to the View tab, click on Custom Views, select the view you created (e.g., All Sheets Visible), and then click on Show. This will instantly revert your workbook to the state where all sheets are visible.



How to check if a workbook contains any hidden sheets?

To swiftly identify any hidden sheets within an Excel workbook, follow these streamlined steps:

1. Right-click on any of the sheet tabs visible at the bottom of the Excel window. This action will prompt a context menu to appear.
2. In this menu, direct your attention to the Unhide option.
   • If the Unhide option is active (meaning it's not grayed out), this signals the presence of hidden sheets in your workbook. You can click on it to view and select any hidden sheets you wish to make visible.
   • Conversely, if the Unhide option is inactive (grayed out), it confirms that the workbook is free of any hidden sheets.



This method stands as the most direct approach to quickly ascertain whether your workbook conceals any sheets. However, this method does not show very hidden sheets. To view and unhide sheets that are very hidden, use the following method.

Easily unhide all sheets that are very hidden using Kutools for Excel

To view and unhide sheets set as very hidden in Excel—a status that makes them inaccessible through the usual Excel interface—Kutools for Excel offers an efficient solution. The Hide/Unhide Workbooks and Sheets feature enables you to manage sheets that are more deeply hidden than the standard hidden setting allows, without the need for complex VBA scripts. It enables you to effortlessly reveal all hidden sheets, including those marked as very hidden, and offers options to unhide either only the hidden sheets or exclusively the very hidden ones. Here’s how to unhide all sheets that are very hidden with Kutools.

After installing Kutools for Excel, select Kutools > View > Hide/Unhide Workbooks and Sheets . In the Hide/Unhide Workbooks and Sheets dialog box, check each checkbox of the Veryhidden sheets in the Worksheet list to display them.

You can see the very hidden sheets are unhidden instantly. Close the dialog as you need.

Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Why you cannot unhide sheets? - Problems and solutions

Amazing! Using Efficient Tabs in Excel Like Chrome, Edge, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Encountering issues when attempting to unhide sheets in Excel can be frustrating. These issues typically arise from a few common scenarios. Understanding these problems and their solutions can help you efficiently manage your Excel workbooks. Here’s a breakdown of common problems and their solutions:

1. Sheets Set as Very Hidden

Problem:

Excel allows sheets to be set as Very Hidden, which means they cannot be unhidden through the usual Excel interface options.

Solution:

You can use VBA to change the visibility status of these sheets. Access the VBA editor by pressing ALT + F11 keys, find the workbook and sheet in question, and set the sheet's Visible property to xlSheetVisible. Alternatively, tools like Kutools for Excel offer a more user-friendly way to unhide very hidden sheets without needing to write code.

2. Workbook Protection

Problem:

If the workbook is protected, you might be unable to make changes, including unhiding sheets.

Solution:

You need to remove the workbook protection. This usually requires the password used to protect the workbook. Once unprotected, you should be able to unhide sheets normally. If you don't know the password, please read: How to unprotect all protected sheets without password in a workbook?

3. Sheets are Not Actually Hidden

Problem:

Sometimes, it might appear that sheets are hidden when they are, in fact, not present in the workbook. /

Solution:

To know whether there are any hidden sheets in your workbook, refer to this guide in this article: How to check if a workbook contains any hidden sheets?

4. Excel Version and Compatibility Issues

Problem:

Older versions of Excel or compatibility issues between different releases can affect your ability to unhide sheets. /

Solution:

Ensure that you’re using a version of Excel that supports all features used in the workbook. If you’re working in compatibility mode for an older version of Excel, consider saving the file in a newer format if all users have access to updated versions of Excel.

In wrapping up, we've explored numerous ways to unhide sheets in Excel, from manual steps to using Kutools for Excel and VBA scripts.... With these tools at your disposal, may your spreadsheets remain organized and your data always within reach. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Related articles