If there are multiple worksheets in an Excel Workbook, you may find the need to hide few worksheets to reduce the clutter or for any other reason.
This can be easily achieved by right-clicking on the worksheet that you want to hide and selecting the Hide option that becomes available in the right-click menu.
Similarly, you can Unhide Sheets in Excel by right-clicking on any active or visible worksheet and this time selecting the Unhide option in the right-click menu.
Hide & Unhide Sheets in Excel
Before going ahead with the actual steps to Hide Sheets in Excel, you might be interested in knowing that you can Hide Multiple Sheets at once in Microsoft Excel.
However, when it comes to un-hiding or revealing hidden sheets, Microsoft Excel does not provide the option to unhide multiple sheets.
The only way to unhide all sheets at once in Microsoft Excel is by using Visual Basic Command or by building a Macro to unhide multiple sheets at once.
1. Hide Multiple Sheets in Excel
Follow the steps below to hide multiple sheets in Excel
1. Select the first worksheet that you want to hide.
2. Press & Hold the CTRL Key on the keyboard of your computer and select other worksheets that you want to hide.
3. Once all the Worksheets are selected, right-click on one of the selected worksheets and click on the Hide option.
This will immediately hide all the selected worksheets in the Excel workbook.
2. Manually Unhide Sheets In Excel
If you are only dealing with a few hidden worksheets, you can manually unhide some or all the hidden worksheets by following the steps below.
1. Right-click on any existing or visible worksheet tab and click on the Unhide option.
2. On Unhide dialog box, select the worksheet that you want to unhide and click on OK.
Note: You can select only one worksheet at a time.
3. Repeat the above steps (1~3) to unhide other worksheets.
As mentioned above, there is no in-built option in Excel to quickly select and unhide multiple or all the hidden worksheets.
3. Unhide All Worksheets in Excel Using VB Editor
While there is no built-in option in Excel to unhide all the worksheets at once, you can execute a simple code in visual basic editor to unhide all the worksheets at once.
1. Right-click on any visible worksheet and select View Code option to open Visual Basic Editor.
2. On Visual Basic Editor screen, click on the View tab and select Immediate Window in the drop-down menu.
3. In Immediate window, type For each sheet in Thisworkbook.sheets: sheet.visible=true: Next sheet and Hit the Enter key on the keyboard of your computer.
Once above code is executed, all the hidden worksheets will become unhidden.
4. Unhide All Worksheets in Excel Using Macro
Perhaps the best way to Unhide All Worksheets in Excel is to create a Macro in Personal macro workbook and save the Macro icon in the Quick Access Toolbar.
Once the Macro icon is available in Quick Access Toolbar, you can unhide all sheets in any workbook by clicking on the Macro icon.
1. Click on the View Tab > Macros > Record Macro.
2. In Record Macro dialogue box, select Personal Macro Workbook option in ‘Store Macro’ section and click on OK.
3. Next, click on the View Tab > Macros > Stop Recording option.
4. Now, right-click on any worksheet tab and click on View Code option.
5. On Visual Basic Editor screen, double-click on Module object under ‘Personal.XLSB’ entry > remove all existing code and add the Code to UnhideAllSheets in Personal.XLSB Module window.
Below is the code that you can copy and paste in the Module window.
Sub UnhideAllSheets()
For Each Sheet In Sheets
Sheet.Visible = True
Next Sheet
End Sub
6. After adding the code, click on the Save icon in the toolbar and close the Vb Editor.
The next step is to add UnhideAllSheets Code icon to the Quick Access Toolbar, so that you can Unhide All Worksheets at once by simply clicking on this icon.
7. Click on Customize Quick Access Toolbar icon and select More Commands in the drop-down.
8. In the Excel Options dialog box, select Macros in ‘Choose Commands from’ section > select the Macro to UnhideAllSheets and click on the Add button.
9. Click on OK to save the change.
10. Now, if you take a look at Quick Access Toolbar, you will see UnhideAllSheets icon.
This allows you to Unhide all worksheets at once by simply clicking on the UnhideAllSheets icon in the Quick Access Toolbar.