• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Hide And Unhide Sheets in Excel

How to Hide And Unhide Sheets in Excel

By: Waseem Patwegar

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 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.

Hide Sheets in Excel

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.

Unhide Sheets in Excel

2. On Unhide dialog box, select the worksheet that you want to unhide and click on OK.

Unhide Pivot Table Dialogue Box

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.

View Code Option in Excel

2. On Visual Basic Editor screen, click on the View tab and select Immediate Window in the drop-down menu.

Open Immediate Windows in VB Editor

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.

VB Code to Unhide All Worksheets in Excel

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.

Record Macro Option in Excel

2. In Record Macro dialogue box, select Personal Macro Workbook option in ‘Store Macro’ section and click on OK.

Record Macro Dialogue Box in Excel

3. Next, click on the View Tab > Macros > Stop Recording option.

Stop Recording Macro Option in Excel

4.  Now, right-click on any worksheet tab and click on View Code option.

View Code Option in Excel

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.

Macro Code to Unhide All Worksheets in Excel

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.

Customize Quick Access Toolbar Icon in Excel

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.

Add UnhideAllSheets Macro Icon to Quick Access Toolbar in Excel
9. Click on OK to save the change.

10. Now, if you take a look at Quick Access Toolbar, you will see UnhideAllSheets icon.

UnhideAllSheets Icon in Quick Access Toolbar

This allows you to Unhide all worksheets at once by simply clicking on the UnhideAllSheets icon in the Quick Access Toolbar.

Related
  • How to Hide Cells, Rows and Columns In Excel
  • How to Hide Zeros in Excel
  • How to Freeze Rows and Columns in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Gadget Guide

  • Should You Buy MacBook or Windows Laptop
  • Chromebook vs Windows Laptop
  • 12 Reasons to Switch to Chromebook
  • Kindle E-Reader Comparison
  • Best Asus Laptops Under 500




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved