If there are multiple worksheets in an Excel Workbook, you may want to hide few worksheets to reduce the clutter or for other reasons. You will find below the steps to hide and unhide sheets in Excel.
Hide & Unhide Sheets in Excel
All that is required to hide a worksheet in Excel is to right-click on the worksheet that you want to hide and select Hide option in the right-click menu that appears.
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.
However, you can only Unhide one worksheet at a time in Excel. There is really no easy option in Excel to unhide multiple or all worksheets at once.
The only way to unhide all sheets at once in Excel is to execute a Visual Basic Command or build a Macro to Unhide Multiple Sheets in Excel.
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.
