Skip to main content

Mastering Excel: The Ultimate Guide to Unhide All Sheets or Multiple Sheets with Ease

Author: Zhoumandy Last Modified: 2024-04-22

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

Note:

  • Besides the right-click contextual menu, the Unhide dialog can be accessed from the ribbon or from the short key:
  • Access from the ribbon:
    Go to the Home tab, and in the Cells group, click Format > Hide & Unhide > Unhide Sheet to open the Unhide dialog.
  • Access from the Excel shortcut key:
    Press ALT + H + O + U + H keys to open the Unhide dialog.
Tips:
  • For non-Microsoft 365 users, Excel's Unhide option only allows you to select one sheet at a time. To unhide multiple or all sheets, you will have to repeat the above steps for each worksheet individually.
  • However, if you are a Microsoft 365 user, you can unhide multiple Excel sheets in the Unhide dialog in one go.
    • To select multiple sheets, do either of these:
    • Press and hold Ctrl key, then click the items to select them.
    • Press and hold Shift key, then use the up and down arrow keys to adjust your selection.
  • If you are not a Microsoft 365 user, but want to hide multiple or all sheets quickly, please use the following methods ( Kutools for Excel method , VBA method, and Custom View method ) we will introduce next.

🌟 Instantly toggle all hidden sheets to be visible or invisible! 🌟

Save time and effort with Kutools for Excel's Toggle Hidden Worksheet Visibility feature! 🚀

With the handy Toggle Hidden Worksheet Visibility button provided by Kutools for Excel, you can use one click to show all hidden worksheets in the active workbook and click it again to toggle all hidden worksheets to be invisible. It's as quick as lightning! ⚡

📊 Kutools for Excel: Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! 🚀

Download Now

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.

Note: The text (11 sheet(s)) shown next to the function represents the total count of hidden worksheets in your workbook. If there is no hidden worksheet, the function will become greyed out.

Result

All hidden sheets are shown at once.

Tips:
  • To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.
  • For a tailored approach to managing your workbooks and worksheets, such as selectively showing specific worksheets instead of all at once, Kutools for Excel offers the advanced Hide / Unhide Workbooks and Sheets utility. This feature provides the flexibility to easily display or conceal groups of workbooks and worksheets according to your specific needs.

Unhide sheets with VBA


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

Note: In this case, Sheet5 and Sheet6 will be unhidden. You can customize the list with your sheet names in code: sheetNames = Array("Sheet5", "Sheet6") by replacing "Sheet5", "Sheet6" with other sheet names.

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

Note: In this case, sheets containing Excel in the name will be displayed. To unhide worksheets that have another particular text in their names, you can modify the provided code If InStr(ws.Name, "Excel") > 0 Then by replacing "Excel" with the desired text.

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.

Note: If you don’t know whether there are any hidden sheets in your workbook, refer to this guide at the end of this section: How to check if a workbook contains any hidden sheets?

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

Note: To unhide all sheets including hidden sheets and very hidden sheets, you only need to click the Unhide all button in the Hide/Unhide Workbooks and Sheets dialog box. Then all the sheets are displayed in one go!

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 can't you unhide sheets? - Problems and solutions


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 you're using a version of Excel that supports the features in your workbook. If you're working in compatibility mode for an older version, consider upgrading and saving the file in a newer format. Make sure that all users who need the workbook have access to the updated version of Excel to maintain functionality and compatibility.

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.

Comments (41)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
سهلت علي الكثبر
اقدر لك ذلك
شكرا لك
Rated 5 out of 5
This comment was minimized by the moderator on the site
it is showing "he Worksbook structure is password protected.!! How to proceed further?
This comment was minimized by the moderator on the site
Nice but can't scroll using mouse wheels. Very bad UX.
This comment was minimized by the moderator on the site
Thanks a Lot for Sharing VB COde
This comment was minimized by the moderator on the site
This is very helpful. Thanks! Is there VBA code I can use to unhide all hidden rows/columns across all tabs at once?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations