How to Hide / Unhide Worksheets – Excel & Google Sheets

This tutorial will demonstrate how to hide and unhide worksheets in Excel and Google Sheets.

 

 

Hiding a Worksheet

You may wish to hide worksheets that contain sensitive information or calculations you don’t want other users to see or amend.

1. Select the tab of sheet that needs to be hidden.

 

 

2. In the Ribbon, select Home > Cells > Format > Hide & Unhide > Hide Sheet.

 

 

Alternatively, right-click on the sheet tab to obtain a shortcut menu and click Hide.

 

 

The selected sheet disappears from the sheet tabs.

 

 

3. Repeat the process to hide any more sheets that need to be hidden.

 

 

Note that one sheet always has to remain visible. If you try to hide the remaining sheet, the following message appears.

 

 

Unhiding a Worksheet

1. To show the worksheets once again on the screen, in the Ribbon, select Home > Cells > Format > Hide & Unhide > Unhide Sheet.

 

 

Alternatively, right-click on the sheet tab to obtain a shortcut menu and click Unhide.

 

 

2. Repeat the process to hide any more sheets that need to be unhidden.

Hiding Worksheets Using VBA Code

If you want to hide multiple sheets at a time, use a loop in VBA code to loop through the sheets and hide each sheet during the loop. You need to put in an error trap, however, as the macro would try to hide all the sheets, and as shown above, you need at least one sheet visible.

Sub HideSheets()
  Dim wks As Worksheet 
  On Error Resume Next
  For Each wks In ActiveWorkbook.Worksheets
  wks.Visible = False
  Next wks
End Sub

Alternatively, if you want to keep a specific sheet visible but hide the rest, test for the name property of the sheet by adding an IF statement to the macro and hide all the sheets except the one you wish to remain visible.

Sub HideSheets()
  Dim wks As Worksheet
  For Each wks In ActiveWorkbook.Worksheets
  If wks.name <> "MainSheet" Then
  wks.Visible = False
  End If
  Next wks
End Sub

Showing a Worksheet Using VBA Code

To unhide more than one worksheet at a time, use a loop in VBA code to loop through the sheets and unhide each sheet during the loop. You don’t need an error trap since the code loops through all sheets and unhides only the ones that are hidden.

Sub ShowSheets
  Dim wks as worksheet
  For each wks in ActiveWorkbook.Worksheets
  wks.Visible = True
  Next wks
End Sub

Hiding a Worksheet in Google Sheets

1. To hide a worksheet in a Google Sheets file, right-click on the sheet tab to obtain a shortcut menu.

 

 

2. Select Hide sheet.
3. Repeat this process for each sheet you want to hide.

Unhiding a Worksheet in Google Sheets

1. To unhide a worksheet in Google Sheets, in the Menu, select View > Hidden sheets. The number in parentheses indicates how many sheets are hidden.

 

 

2. Select the sheet that needs to be unhidden.
3. Repeat the process for each sheet that needs to be made visible.