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.
- Select the tab of sheet that needs to be hidden.
- 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.
- 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
- 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.
- 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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 |
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
- To hide a worksheet in a Google Sheets file, right-click on the sheet tab to obtain a shortcut menu.
- Select Hide sheet.
- Repeat this process for each sheet you want to hide.
Unhiding a Worksheet in Google Sheets
- To unhide a worksheet in Google Sheets, in the Menu, select View > Hidden sheets. The number in parentheses indicates how many sheets are hidden.
- Select the sheet that needs to be unhidden.
- Repeat the process for each sheet that needs to be made visible.