Unhide Sheets In Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Unhide-Sheets-in-Excel
Table of contents
  • 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,

  1. Using Right Click.
  2. From the Home Tab.
  3. Using Excel Shortcut Key.
  4. Unhide Multiple Sheets.
  5. Unhide All Worksheets Except Particular Worksheet.
  6. 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:

  1. First, we need to right-click any worksheet tabs to Unhide the sheet.


    Unhide Single Sheet Example 1.1

  2. Once you right-click, you can see the options below.


    Unhide Single Sheet Example 1.2

  3. Select the “Unhide” option in these options, and you will see a list of all hidden worksheets.


    Unhide Single Sheet Example 3.2

  4. Select the worksheet you want to Unhide, and click “OK”. It will Unhide the selected sheet.


    Unhide Single Excel Sheet

  5. Now, we can see the worksheet named “WS1” in the “My Sheet” tab.


    Unhide Single Sheet Example 1.4

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.
Unhide Single Sheet Example 2.1
  • Step 2: Upon clicking that option, as shown in the above image, the “Unhide” window opens.
Unhide Single Sheet Example 2.2

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.
Unhide Single Sheet Example 3.1
  • Step 2: The Unhide window opens.
Unhide Single Sheet Example 3.2

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.

Unhide All Worksheet 1.1
  • 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.
Unhide All Worksheet 1.2
  • Now, insert a new module under the “INSERT” option.
Unhide All Worksheet 1.3
  • In the new module, paste the copied code.
Unhide All Worksheet 1.4
  • Now, run this code. It will Unhide all the hidden worksheets in the workbook.
Unhide All Sheets

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.

Unhide All Worksheet Except One

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.

Unhide Single Worksheet using VBA

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.

Frequently Asked Questions (FAQs)

1. Can we Unhide all sheets in Excel same time?

We cannot Unhide all sheets in Excel at the same time using the Unhide window with the right-click option, or the shortcut key option. However, we can use the VBA methods to Unhide multiple sheets simultaneously.

2. How to unhide all sheets in Excel in one go?

We use the following VBA methods to unhide all sheets in one go, namely,
1) Unhide Multiple Sheets.
2) Unhide All Worksheets Except Particular Worksheet.
3) Unhide Only Specific Excel Sheet.

3. What is the shortcut key to unhide sheets?

We use the shortcut key, shown below, to open the “Unhide” window, to unhide the required sheets.

Unhide Sheets in Excel - FAQ 3

4. Name the various ways to Unhide Sheets in Excel.

The various ways to Unhide Excel Sheets are,
1) Using Right Click.
2) From the Home Tab.
3) Using Excel Shortcut Key.
4) Unhide Multiple Sheets.
5) Unhide All Worksheets Except Particular Worksheet.
6) Unhide Only Specific Excel Sheet.

Download Template

This article must help understand Unhide Sheets in Excel with its formulas and examples. You can download the template here to use it instantly.

This article has been a guide to Unhide Sheets in Excel. Here we see 6 ways to unhide single/multiple sheets, VBA, Home tab, examples & downloadable excel template. You can learn more from the following articles: -