Unhide Sheets In Excel

Publication Date :

22 Oct, 2019

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What Is Unhide Sheets In Excel?

Unhide Sheets in Excel is a method to reveal or display the hidden worksheets in an Excel workbook.
The Excel Unhide Sheets option can Unhide one sheet at a time. However, using different methods, we can unhide a single sheet, a particular sheet, or multiple sheets simultaneously.

For example, in the following image, we have an Excel workbook with five worksheets, namely, WS1, WS2, WS3, WS4, and WS5, where WS1 and WS4 are hidden. When we want to Unhide Sheets, we right-click on any of the “Sheet Names”, then the “Unhide” window opens, as shown below.

Now, we can select the desired sheet to Unhide.

Key Takeaways

In an Excel workbook, we may hide some of the worksheets. The Unhide Sheets in Excel helps users to Unhide these hidden worksheets so that we can work on them or display them.
We have various methods to Unhide the hidden sheets, namely, Unhide Single Sheet → Right-click, and the Home Tab options, Shortcut Key, etc. Unhide multiple sheets → VBA code.
The VBA code can be written to Unhide all the hidden worksheets at once, all except a specific worksheet, or just a specific worksheet.

Different Methods To Unhide Excel Sheets

In an Excel workbook, by default, we have three worksheets, Sheet1, Sheet2, and Sheet3. We can add or delete worksheets, rename them, hide and Unhide them.

The different methods to Unhide Excel Sheets are,

Using Right Click.
From the Home Tab.
Using Excel Shortcut Key.
Unhide Multiple Sheets.
Unhide All Worksheets Except Particular Worksheet.
Unhide Only Specific Excel Sheet.

Method #1 - Using Right Click

The steps to Unhide Sheets in Excel using the right-click option are as follows:

First, we need to right-click any worksheet tabs to Unhide the sheet.
Once you right-click, you can see the options below.
Select the “Unhide” option in these options, and you will see a list of all hidden worksheets.
Select the worksheet you want to Unhide, and click “OK”. It will Unhide the selected sheet.
Now, we can see the worksheet named “WS1” in the “My Sheet” tab.

Method #2 - From the Home Tab

The steps to Unhide Sheets in Excel using the “Home” tab are as follows:

Step 1: Select Home → Format → Hide & Unhide → Unhide Sheet.

Step 2: Upon clicking that option, as shown in the above image, the “Unhide” window opens.

As usual, select the worksheet to Unhide, and click “OK”. It will Unhide the selected sheet.

Method #3 - Using Excel Shortcut Key

The steps to use the Shortcut Key to Unhide Sheets in Excel are as follows:

Step 1: Press “ALT + H + O + U + H” to open the Unhide window.

Step 2: The Unhide window opens.

As usual, select the worksheet to Unhide, and click “OK”. It will Unhide the selected sheet.

Method #4 - Unhide Multiple Sheets

Unhide window can only Unhide single sheets at a time, but imagine you have to Unhide ten sheets, then repeating the same set of tasks ten times is time-consuming and overwhelming. Therefore, we can Unhide all the sheets by writing the VBA code in Excel.

Below is the code to Unhide all the hidden worksheets in the workbook.

VBA Code:

Sub Unhide_All_Worksheets()

    Dim WSht As Worksheet

    For Each WSht In ActiveWorkbook.Worksheets
        WSht.Visible = xlSheetVisible
    Next WSht

End Sub

The code that appears in the VBA editor is shown below.

We have used the For Each loop in VBA to Unhide the worksheet. Copy the above code, go to your worksheet, and press ALT + F11 to open Visual Basic Editor.

Now, insert a new module under the “INSERT” option.

In the new module, paste the copied code.

Now, run this code. It will Unhide all the hidden worksheets in the workbook.

Method #5 - Unhide All Worksheets Except Particular Worksheet

In some situations, we need to Unhide all the worksheets except the specific worksheet. In such cases too, we can use the VBA with a slightly modified code.

For example, assume you want to Unhide all the worksheets except the worksheet named “Workings”.

The below code will do the same.

VBA Code:

Sub Unhide_All_Except_One()

     Dim WSht As Worksheet

     For Each WSht In ActiveWorkbook.Worksheets
         If WSht.Name <> "Workings" Then
         WSht.Visible = xlSheetVisible
         End If
     Next WSht

End Sub

The code appears in the module window, as shown below.

Now, run this code, and it will Unhide all worksheets except the one named “Workings”.

You can change the worksheet name from “Workings” to your worksheet name.

Method #6 - Unhide Only Specific Excel Sheet

Similarly, if you want to Unhide only a specific Excel sheet, VBA can do this. For example, if you are going to Unhide only the worksheet named “Working”, then we can use the following code.

VBA Code:

Sub Unhide_One_Sheet()

     Dim WSht As Worksheet

     For Each WSht In ActiveWorkbook.Worksheets
         If WSht.Name = "Workings" Then
         WSht.Visible = xlSheetVisible
         End If
     Next WSht

End Sub

The code appears in the module window, as shown below.

Important Things To Note

We must remember that when the Unhide window appears with the list of hidden worksheet names, we can unhide only one sheet at a time.
The sheets that we unhide can be hidden back until further use.
All the Unhide Sheet methods open the “Unhide” window with the list of hidden sheets.