This effortless Microsoft Excel tutorial will help you learn how to unhide sheets in Excel.

You might hide certain worksheets not immediately needed to declutter a large workbook. Or, someone else might have hidden a few sheets you need now to work on your data analytics and visualization project in Excel.

Here comes the Excel skill to unhide worksheets at your rescue.

Read on to learn the tried and tested methods to uncover hidden sheets in various ways. I’ve included a blend of techniques suitable for you as a beginner, intermediate, and expert-level Excel user.

📒 Read More: 11 Ways to Hide a Sheet in Microsoft Excel

Using the Right-click Menu

This is the easiest method you can try. It’s suitable for workbooks that have few worksheets among which only a few have been hidden.

Right-click on any of the visible worksheets in the workbook.

Click on the Unhide option in the context menu.

You’ll see the Unhide dialog box.

To reveal one hidden sheet, select that and click OK.

To unhide more than one or all, select sheets using the Ctrl-click action.

Now, hit OK to unhide multiple sheets at once.

Using the Excel Ribbon

Navigate to the workbook that contains some hidden worksheets.

Press Alt + H +ZC + O to bring up the Format context menu.

Now, hover the cursor over the Hide & Unhide option to open a new context menu on the right.

There, click on the Unhide Sheet option.

You should now see the Unhide dialog box.

The rest of the steps are the same as explained in the previous method.

Using the Excel Options Dialog

Often, a workbook owner might disable the sheet tabs option for the file. So, you can’t see the sheet tabs from which you can unhide hidden worksheets.

To enable the Sheet tab again, press Alt + F + T to bring up the Excel Options dialog box.

Click on the Advanced menu on the left-side navigation panel.

Scroll down until you find the display options for this workbook section.

Checkmark the checkbox for the Show sheet tabs option.

Using the Custom Views Tool

The Custom Views tool allows you to create several views for a workbook. For example, if there are 10 worksheets and all are visible, create a view named All Worksheets Visible.

Now, hide five worksheets and create another view named Five Worksheets Visible.

So, you can switch to the second workbook view to declutter your workbook. When you need to see all the worksheets, revert to the first custom view.

Let’s practically look at the steps below.

In the above workbook, there are four worksheets.

Bring up the Custom Views dialog box from the View tab > Workbook Views commands block.

Click Add and enter a new custom view name in the Name field, like Original View. Click OK to apply.

Now, let’s hide a sheet from this workbook, like Human Resource.

Create another view for this workbook by following the above steps.

Now that the Human Resource tab is hidden, you can easily unhide it by switching to the Original View from the Custom Views dialog box.

You must click the Show button after selecting the alternative workbook view.

Using Excel VBA Immediate Window

If you’re unable to follow any of the above methods to unhide sheets and quickly need to reveal all the hidden worksheets, you can use the VBA Immediate Window method.

Go to the target workbook and press Alt + F11 to bring up the Excel VBA Editor.

Select the correct project on the left-side menu, below the Project heading.

Press Ctrl + G to bring up the Immediate Window.

Copy and paste the following command inside the Immediate Window:

For Each ws In Sheets:ws.Visible=True:Next

Press Enter.

As soon as you do the above, Excel will unhide all the worksheets.

You can now close the Excel VBA Editor. Also, there’s no need to save the workbook as an XLSM file.

Using XML Editing

In this technique, you’ll extract the XML file of the target workbook, access the worksheet names in a code editor, and delete the state="hidden" tag.

First, create a copy of the target workbook. Now, rename the target workbook’s file extension to ZIP from XLSX.

Now, use WinRAR or any other ZIP file decompressing tool to extract the contents of the newly created ZIP file in a separate folder. For example, create a folder same as the name of the workbook.

Go to the xl directory of the extracted content. You’ll find the workbook.xml file in this folder.

Open it in any XML editor, like Office XML Handler, WordPad, Notepad, etc.

Find the state="hidden" tags in the code base. Then, delete all the instances of this tag to unhide all the hidden sheets.

Alternatively, you can just delete the tag for a specific worksheet.

Click the Save button on the XML editor to save the script.

Now, compress the main folder back to a ZIP file.

Rename the ZIP file to XLSX.

Open the file in Excel and you should see that the hidden sheets have surfaced.

Using Excel VBA

You can use Visual Basic for Applications to automate various tasks, calculations, visualizations, etc., in Excel including unhiding worksheets.

This method doesn’t require mandatory knowledge of scripting in VBA. You can simply copy and paste the scripts I have shared below to create VBA macros.

Before continuing with the scripts, find below the Excel tutorial that helps you learn to set up macros:

📒 Read More: How To Use The VBA Code You Find Online

Now use the scripts listed below along with special scenarios:

Unhide All Worksheets

This VBA code will show all the sheets in the active workbook:

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

After creating a macro using the above script, press Alt + F8 to bring up the Macro dialog.

There, select the UnhideAllSheets macro and hit the Run button.

The VBA macro will force the workbook to show all the worksheets in the sheets tab.

Unhide Worksheets Based on User Inputs

Suppose, you want to unhide worksheets by their complete or partial names using VBA. Then, you can use the following script:

Sub UnhideWorksheets()
    Dim ws As Worksheet
    Dim userInput As String
    Dim exactMatch As Boolean
    
    ' Ask the user whether to unhide by exact or partial worksheet name
    userInput = InputBox("Do you want to unhide by exact worksheet name? (Yes/No)")
    exactMatch = UCase(userInput) = "YES"
    
    ' If exact match, ask for worksheet name
    If exactMatch Then
        userInput = InputBox("Enter the exact worksheet name:")
        On Error Resume Next
        Set ws = Worksheets(userInput)
        On Error GoTo 0
        If ws Is Nothing Then
            MsgBox "Worksheet not found!", vbExclamation
            Exit Sub
        End If
    Else
        ' If partial match, ask for keyword
        userInput = InputBox("Enter a keyword available in the worksheet name:")
        For Each ws In Worksheets
            If InStr(1, ws.Name, userInput, vbTextCompare) > 0 Then
                ws.Visible = xlSheetVisible
            End If
        Next ws
    End If
    
    ' Unhide the worksheet
    ws.Visible = xlSheetVisible
End Sub

This script, once executed, will show visual cues and input boxes. Therefore, you can follow those instructions and unhide sheets from a workbook containing hundreds of hidden worksheets.

• Input box to choose from complete or partial worksheet name as a condition.

• Visual prompt to enter a keyword or complete the name of the worksheet.

• An example of revealing the hidden Sales sheet using this script.

Unhide Sheets Based on a List

For example, you start your day on a business dashboard workbook by unhiding a few worksheets. You work on the sheets throughout the day and hide those at the end of the day.

In this scenario, you can use the following VBA code to unhide sheets by choosing a list of sheet names from the active worksheet of the same workbook.

 Sub UnhideSheetsFromRange()
    Dim ws As Worksheet
    Dim myRange As Range
    Dim myCell As Range
    Dim sheetName As String
    Dim hiddenCount As Integer
    
    ' Prompt the user to select a cell range containing sheet names
    On Error Resume Next
    Set myRange = Application.InputBox("Select a cell range with sheet names:", Type:=8)
    On Error GoTo 0
    
    If myRange Is Nothing Then
        MsgBox "No range selected. Please try again.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each cell in the selected range
    For Each myCell In myRange
        sheetName = Trim(myCell.Value)
        If sheetName <> "" Then
            On Error Resume Next
            Set ws = ThisWorkbook.Sheets(sheetName)
            On Error GoTo 0
            
            If Not ws Is Nothing Then
                If ws.Visible = xlSheetHidden Then
                    ws.Visible = xlSheetVisible
                    hiddenCount = hiddenCount + 1
                End If
            End If
        End If
    Next myCell
    
    ' Show completion message
    If hiddenCount > 0 Then
        MsgBox hiddenCount & " sheet(s) unhidden successfully!", vbInformation
    Else
        MsgBox "No sheets were unhidden. Please check the sheet names in the selected range.", vbExclamation
    End If
End Sub

You’ll see an input box once you execute the macro made from the above script. Use your mouse cursor to highlight a cell range on the active worksheet to select sheet names.

Excel will unhide the selected sheets and show a confirmation dialog box with the number of worksheets revealed.

Unhide Sheets at a Specific Time

For example, there are a few critical worksheets in your business dashboard workbook. You only want those to be revealed on a specific date or period, like during fiscal year-end, month-end, etc.

To do this, you can use the following VBA script and create a macro. This script will unhide the select worksheets if executed on a specific date or date range. Otherwise, the script will show an error message.

Sub UnhideSheetsInRange()
    Dim inputSheets As String
    Dim sheetName As Variant
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    
    ' Set the date range
    startDate = DateSerial(Year(Date), 6, 1) ' June 1
    endDate = DateSerial(Year(Date), 6, 30) ' June 30
    
    ' Get the sheet names from the user
    inputSheets = InputBox("Enter the sheet names (separated by semicolons):")
    
    ' Split the input into an array
    Dim sheetArray() As String
    sheetArray = Split(inputSheets, ";")
    
    ' Check if the current date is within the range
    currentDate = Date
    If currentDate >= startDate And currentDate <= endDate Then
        ' Unhide the specified sheets
        For Each sheetName In sheetArray
            On Error Resume Next
            Sheets(sheetName).Visible = xlSheetVisible
            On Error GoTo 0
        Next sheetName
    Else
        ' Show a message if outside the date range
        MsgBox "The requested worksheets can only be unhidden between June 1 and June 30."
    End If
End Sub

I’ve set up the above script to unhide the requested worksheets between June 1 and June 30.

Upon running, the macro shows an input box where you need to enter the names of the sheets separated by semicolons.

If the request is within the valid period, Excel will unhide the sheets.

When you run the macro outside the allowed period, you get an error message as shown above.

To change the allowed date range, simply edit the following code lines:

startDate = DateSerial(Year(Date), 6, 1) ' June 1
    endDate = DateSerial(Year(Date), 6, 30) ' June 30

To change the error message, edit this code line as well:

MsgBox "The requested worksheets can only be unhidden between June 1 and June 30."

Using Office Scripts

Are you using Excel for Microsoft 365 desktop or web app with a Business Standard or better subscription package? You can utilize Office Scripts to unhide worksheets automatically.

You can use this method if you see the Automate tab in your Excel ribbon menu.

Click on the Automate tab and select New Script from the Scripting Tools commands block.

You should see the Office Scripts Code Editor on the right side.

Clear any existing script from the Code Editor.

Copy and paste the following script into the Code Editor.

function main(workbook: ExcelScript.Workbook) {
    // Iterate over each worksheet.
    workbook.getWorksheets().forEach((worksheet) => {
        // Set the worksheet visibility to visible.
        worksheet.setVisibility(ExcelScript.SheetVisibility.visible);
    });
}

Click on the Save button to save the script for the future.

Now, hit the Run button to execute the script.

Excel will unhide all the hidden worksheets of the active workbook.

Conclusions

Now you know how to unhide sheets in Excel if you’ve followed along with the Excel tutorial so far.

Here, you’ve learned simple methods based on Microsoft Excel user interface buttons and tools.

You’ve also seen multiple automation opportunities using Excel VBA macros and Office Scripts.

Finally, you’ve learned a highly advanced technique to unhide sheets by editing the XML file of the Excel workbook.

Which method to unhide sheets in Excel do you prefer? Do you know a better and more effortless technique that I might have missed? Do comment in the box below.