How to unhide sheets in Excel: show multiple or all hidden sheets

This tutorial shows you how to unhide worksheets in Excel 2016, 2013, 2010, and earlier versions. Learn quick methods using right-clicks and VBA code to reveal hidden sheets.

Scenario: You open a spreadsheet, and formulas reference a missing sheet. The sheet tab isn't visible, and creating a new sheet with the same name fails. This means the worksheet is hidden. This tutorial covers manual and VBA-based solutions.

• Manual Sheet Unhiding
• VBA-Based Unhiding
  • Unhiding All Sheets
  • Unhiding Selected Sheets
  • Unhiding Sheets with Specific Text in Names
• Unhiding with Custom Views
• Checking for Hidden Sheets
• Troubleshooting Unhiding Issues

Unhiding Sheets Manually

To unhide one or two sheets:

1. Right-click any sheet tab and choose "Unhide...".
2. Select the hidden sheet(s) and click "OK" (or double-click the sheet name).

Alternatively, access the "Unhide" dialog via the ribbon:

• Excel 2003 and earlier: Format > Sheet > Unhide.
• Excel 2016, 2013, 2010, 2007: Home > Cells > Format > Visibility > Hide & Unhide > Unhide Sheet....

Note: Excel's "Unhide" only handles one sheet at a time. For multiple sheets, use VBA macros.

Unhiding Sheets with VBA

For numerous hidden worksheets, VBA automation is efficient.

Unhiding All Sheets

This macro makes all hidden sheets visible:

Sub Unhide_All_Sheets()
  Dim wks As Worksheet
  For Each wks In ActiveWorkbook.Worksheets
    wks.Visible = xlSheetVisible
  Next wks
End Sub

Showing All Hidden Sheets and Counting Them

This macro displays all hidden sheets and shows their count in a message box:

Sub Unhide_All_Sheets_Count()
  Dim wks As Worksheet
  Dim count As Integer
  count = 0
  For Each wks In ActiveWorkbook.Worksheets
    If wks.Visible  xlSheetVisible Then
      wks.Visible = xlSheetVisible
      count = count   1
    End If
  Next wks
  If count > 0 Then
    MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets"
  Else
    MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets"
  End If
End Sub

Unhiding Selected Sheets

This macro prompts the user to select which hidden sheets to unhide:

Sub Unhide_Selected_Sheets()
  Dim wks As Worksheet
  Dim MsgResult As VbMsgBoxResult
  For Each wks In ActiveWorkbook.Worksheets
    If wks.Visible = xlSheetHidden Then
      MsgResult = MsgBox("Unhide sheet " & wks.Name & "?", vbYesNo, "Unhiding worksheets")
      If MsgResult = vbYes Then
        wks.Visible = xlSheetVisible
      End If
    End If
  Next
End Sub

Unhiding Sheets Containing Specific Text

This macro unhides sheets with a specific word in their name (replace "report" with your text):

Sub Unhide_Sheets_Contain()
  Dim wks As Worksheet
  Dim count As Integer
  count = 0
  For Each wks In ActiveWorkbook.Worksheets
    If (wks.Visible  xlSheetVisible) And (InStr(1, wks.Name, "report", vbTextCompare) > 0) Then
      wks.Visible = xlSheetVisible
      count = count   1
    End If
  Next wks
  If count > 0 Then
    MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets"
  Else
    MsgBox "No hidden worksheets with the specified name have been found.", vbOKOnly, "Unhiding worksheets"
  End If
End Sub

Using the Macros

Insert the code into a module in the VBA editor (Alt F11), or download the provided workbook.

Inserting Macros

1. Open the workbook.
2. Press Alt F11.
3. Right-click ThisWorkbook, select Insert > Module.
4. Paste the code.
5. Press F5 to run.

Downloading the Workbook

Download the sample workbook containing all macros. Run macros via Alt F8.

Unhiding Sheets with Custom Views

Create a custom view to quickly show all sheets:

1. Ensure all sheets are visible.
2. View > Workbook Views > Custom Views.
3. Click "Add...".
4. Name the view (e.g., "ShowAllSheets") and click "OK".

Checking for Hidden Sheets

Quickly check for hidden sheets by right-clicking a sheet tab. If "Hide..." is enabled, hidden sheets exist.

Troubleshooting

Protected Workbook

If the workbook structure is protected, unhiding is impossible. Unprotect it via the Review tab.

Very Hidden Worksheets

Worksheets hidden via VBA's xlSheetVeryHidden require VBA to unhide.

No Hidden Sheets

A grayed-out "Unhide" means no sheets are hidden.

This concludes the guide on unhiding Excel worksheets. Downloadable macros are available.

The above is the detailed content of How to unhide sheets in Excel: show multiple or all hidden sheets. For more information, please follow other related articles on the PHP Chinese website!