Unhide Hidden Sheets in Excel (Show Worksheet Tabs)

This tutorial teaches you how to unhide sheets or worksheets from an Excel workbook. The methods can be applied to any Excel version, such as 2007, 2010, 2013, 2016, 2019, 2021, or Excel 365.

Let me tell you a real-life scenario. Let’s say you’ve been working on a project for months and have hidden a few sheets in your Excel file to keep things organized. Now, it’s time to access one of those hidden sheets, but you’re unsure how to unhide it.

There are multiple ways to unhide a worksheet, and you can use any of these ways depending on your needs. So, let’s get started and learn all the ways in detail.

Key Points

• You can unhide worksheets manually by right-clicking on the sheet tab and opening the Unhide Sheets.
• You can also use a VBA code to unhide one or all the sheets.

Unhide Worksheets One by One (Manually)

In Excel, when you hide a worksheet, the worksheet tab won’t be there with all the unhidden worksheets, but when you open the option to unhide, you can see which worksheets are hidden and unhide them.

So, for this, you need to use the below steps:

1. First, right-click on any of the worksheet tabs.
2. Now, from the right-click menu, click “Unhide”.
3. Here, you can see a dialog box with all the hidden worksheets. You need to select the worksheet you want to unhide from this box.
4. In the end, click OK to unhide the worksheet.

In the above example, we have selected “Sheet3”. When you click OK, it unhides “Sheet3” in the workbook and gives you the tab and other sheet tabs.

If you want to unhide more sheets, follow the same steps to open the “Unhide” option and select the sheet you want to unhide.

Read Also: Group Worksheets / Show Worksheets Side by Side / Link a Sheet to Another Sheet

Unhide All the Worksheet (Manually)

Sometimes, you need to unhide all the hidden sheets. For this, you need to use the same steps, but you need to select all the sheets in the dialog box and then click OK.

Here are the steps to follow:

1. Right-click on the sheet tab and open the “Unhide” option.
2. CLICK on the FIRST sheet in the dialog box.
3. Press and hold the SHIFT key, then CLICK on the LAST sheet.
4. This will select the entire sheets list; in the end, click OK to unhide all the sheets in one go.

The moment you click OK, it unhides all the hidden sheets. This option might be unavailable if you use an old Excel version. In that case, you can use the VBA code I shared in this tutorial to unhide all the sheets in one go.

Unhide Multiple Sheets (Manually)

In the same way, you can unhide multiple sheets if you don’t want to unhide all or a single sheet. Once you open the “Unhide” dialog box, you will see a list of all the hidden sheets.

Now, click on the first sheet you want to unhide and press and hold the CONTROL key to select the other sheets you want to unhide.

Once you select all the sheets you want to unhide, click OK to unhide them in the workbook.

When you want to unhide sheets manually, you can also access the Unhide option from the Home tab using the ribbon. To do this, you need to go to Home > Cells > Format > Visibility > Hide & Unhide > Unhide Sheets. This will open the same dialog box where you have the hidden sheets list. As seen in the above methods, you can unhide a single, multiple, or even all the sheets from there.

Unhide Single, Multiple, or All Sheets with a VBA Code

If you love automating your stuff, you can use VBA codes to unhide all the hidden sheets. Here are the codes that can help you unhide sheets in different situations.

Unhide All the Sheets with a Code

Below is the code that can unhide all the sheets from a workbook in which you run the code. To do so, open Excel’s Visual Basic editor, insert a new module, and paste the code below.

Sub UnhideAllSheets()
    Dim ws As Worksheet
    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Set the sheet visibility to visible
        ws.Visible = xlSheetVisible
    Next ws
End Sub

Here is the code to unhide all the hidden sheets in all the open workbooks.

Sub UnhideAllSheetsInAllWorkbooks()
    Dim wb As Workbook
    Dim ws As Worksheet
    ' Loop through all open workbooks
    For Each wb In Application.Workbooks
        ' Loop through each sheet in the current workbook
        For Each ws In wb.Sheets
            ' Check if the sheet is hidden or very hidden
            If ws.Visible = xlSheetHidden Or ws.Visible = xlSheetVeryHidden Then
                ' Unhide the sheet
                ws.Visible = xlSheetVisible
            End If
        Next ws
    Next wb
    ' Confirmation message
    MsgBox "All hidden sheets in all open workbooks have been unhidden."
End Sub

Unhide Single Sheets with the Name You Enter

The code below helps you unhide a single sheet from the workbook. When you run this code, it shows you a message box where you must enter the sheet name, which you need to unhide. Then, click OK, and that sheet will be unhided.

Sub UnhideSingleSheet()
    Dim ws As Worksheet
    Dim sheetName As String
    Dim sheetFound As Boolean
    sheetFound = False
    ' Prompt the user to enter the name of the sheet they want to unhide
    sheetName = InputBox("Enter the name of the sheet you want to unhide:")
    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Check if the current sheet name matches the input, ignoring case
        If LCase(ws.Name) = LCase(sheetName) Then
            ' Unhide the sheet if it is hidden
            ws.Visible = xlSheetVisible
            sheetFound = True
            MsgBox "The sheet '" & ws.Name & "' has been unhidden."
            Exit For
        End If
    Next ws
    ' If the sheet name is not found, display a message
    If sheetFound = False Then
        MsgBox "Sheet '" & sheetName & "' was not found in the workbook."
    End If
End Sub

This code takes care of the name you enter by ignoring the letter care, but you need to enter the correct name in the input box so that it can find the correct sheet to unhide.

Get the List of Hidden Sheets and the Unhide one of them…

Now, this is a smart VBA code that helps you get the sheets’ names first. It allows you to enter the sheet’s name in the input bar, and then, when you click OK, it searches for that sheet and unhides it instantly.

Sub UnhideFromHiddenSheets()
    Dim ws As Worksheet
    Dim hiddenSheets As String
    Dim sheetName As String
    Dim sheetFound As Boolean
    sheetFound = False
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' If the sheet is hidden, add it to the hiddenSheets string
        If ws.Visible = xlSheetHidden Or ws.Visible = xlSheetVeryHidden Then
            hiddenSheets = hiddenSheets & ws.Name & vbNewLine
        End If
    Next ws
    ' If there are no hidden sheets, show a message and exit the sub
    If hiddenSheets = "" Then
        MsgBox "There are no hidden sheets in this workbook."
        Exit Sub
    End If
    ' Show InputBox with the list of hidden sheets and prompt for user input
    sheetName = InputBox("The following sheets are hidden:" & vbNewLine & hiddenSheets & vbNewLine & "Enter the name of the sheet you want to unhide:")
    ' Loop through each sheet again to find and unhide the selected one
    For Each ws In ThisWorkbook.Sheets
        ' Check if the sheet name matches the input (case-insensitive)
        If LCase(ws.Name) = LCase(sheetName) Then
            ' Unhide the sheet
            ws.Visible = xlSheetVisible
            sheetFound = True
            MsgBox "The sheet '" & ws.Name & "' has been unhidden."
            Exit For
        End If
    Next ws
    ' If the sheet name was not found in the list of hidden sheets
    If sheetFound = False Then
        MsgBox "Sheet '" & sheetName & "' was not found in the list of hidden sheets."
    End If
End Sub

Unhide “Very Hidden” Sheet in Excel

In Excel, a very hidden sheet is a special type of hidden worksheet that cannot be unhidden using the options we had earlier in this tutorial (i.e., using the “Unhide” by right-clicking on the Tab or the Format option from the Home tab).

In the below example, we have the “Sheet2” very hidden, but when you right-click on it, we cannot unhide any of the sheets in the workbook.

To check whether a sheet is hidden, right-click on any sheet tab and then click on the “View Code” option.

This will open the Visual Basic Editor with the code window for the active worksheet. Now, you can see in the project explorer that you have “Sheet2″.

That means “Sheet2” is very hidden in the workbook. We can unhide it back. To do this, you need to click on “Sheet”2 in the project explorer.

Then, go to the “Properties” window below it. From there, you need to click on the “Visibility” drop-down, select “-1 – xlSheetVisible”, and close the “Visual Basic Editor”.

When you return to the workbook, you can see you have the “Sheet 2”.

And here’s a VBA code that you can use to unhide all the very hidden sheets in your workbook. This code loops through each sheet in your workbook and checks if it is very hidden.

If it finds one, it changes the setting, making the sheet visible again.

Sub UnhideAllVeryHiddenSheets()
    Dim ws As Worksheet
    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Check if the sheet is very hidden
        If ws.Visible = xlSheetVeryHidden Then
            ' Unhide the sheet
            ws.Visible = xlSheetVisible
        End If
    Next ws
    ' Confirmation message
    MsgBox "All very hidden sheets in the workbook have been unhidden."
End Sub

And here’s the code that can help you unhide all the very hidden sheets from all the open workbooks.

Sub UnhideAllVeryHiddenSheetsInAllWorkbooks()
    Dim wb As Workbook
    Dim ws As Worksheet
    ' Loop through all open workbooks
    For Each wb In Application.Workbooks
        ' Loop through each sheet in the current workbook
        For Each ws In wb.Sheets
            ' Check if the sheet is very hidden
            If ws.Visible = xlSheetVeryHidden Then
                ' Unhide the sheet
                ws.Visible = xlSheetVisible
            End If
        Next ws
    Next wb
    ' Confirmation message
    MsgBox "All very hidden sheets in all open workbooks have been unhidden."

End Sub