8 Ways to Unhide Sheets in Microsoft Excel

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 visible sheet
Right-click on any visible sheet

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.

select one sheet to unhide
Select one sheet to unhide

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

Select multiple sheets to unhide
Select multiple sheets to unhide

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

Bring up Format context menu
Bring up the Format context menu

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.

Unhide Sheet on the overflow menu
Unhide Sheet on the overflow menu

There, click on the Unhide Sheet option.

Unhide dialog box
Unhide dialog box

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

Sheet tabs missing
Sheet tabs missing

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.

Show sheet tabs
Show sheet tabs

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.

Custom Views dialog box
Custom Views dialog box

In the above workbook, there are four worksheets.

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

Add a custom view
Add a custom view

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.

Alternative custom view
Alternative custom view

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

Switch back to the first custom view
Switch back to the first custom view

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.

Unhide sheet in Excel using Custom Views
Unhide sheet in Excel using Custom Views

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.

Bring up VBA Editor
Bring up VBA Editor

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.

Bring up Immediate Window
Bring up Immediate Window

Press Ctrl + G to bring up the Immediate Window.

Unhidden sheets with Immediate Window command
Unhidden sheets with Immediate Window command

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.

Renaming worksheet
Renaming worksheet

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

Extracted the contents of the XLSX file in a folder
Extracted the contents of the XLSX file into a folder

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.

Open workbook.xml in an XML editor
Open workbook.xml in an XML editor

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 state hidden tags
Find state hidden tags

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.

package the extracted content in a ZIP archive
package the extracted content in a ZIP archive

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

Rename ZIP to XLSX
Rename ZIP to XLSX

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:

VBA script 1
Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub
The Macro dialog box
The Macro dialog box

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:

VBA script 2
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.

Choose from complete or partial sheet name
Choose from complete or partial sheet name
  • Input box to choose from complete or partial worksheet name as a condition.
Enter keywrod
Enter keyword
  • Visual prompt to enter a keyword or complete the name of the worksheet.
Unhide sales sheet using VBA script
Unhide sales sheet using VBA script
  • 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.

VBA script 3
 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
Input box to select cell range with sheet name
Input box to select cell range with sheet name

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.

Unhide worksheet in Excel using a list in VBA
Unhide worksheet in Excel using a list in VBA

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.

VBA script 4
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.

Enter sheet names to unhide
Enter sheet names to unhide

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

Unhide sheets using VBA on a specific date
Unhide sheets using VBA on a specific date

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

Unhide request denied
Unhide request denied

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.

Use Office Scripts script
Use Office Scripts script

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.

Unhidden sheet using Office Scripts
Unhidden sheet using Office Scripts

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃