How to show and hide sheets in Excel

If an Excel workbook contains multiple worksheets, you may want to hide some worksheets to reduce clutter or for other reasons. Below are the steps to show and hide sheets in Excel.

Show and hide sheets in Excel

To hide a worksheet in Excel, all you have to do is right-click the worksheet you want to hide and select it Hide option in the right-click menu that appears.

Similarly, you can unhide sheets in Excel by right-clicking any active or visible worksheet and this time selecting Unhide from the context menu.

However, you can only show one worksheet at a time in Excel. There really isn’t an 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 run a Visual Basic command or create 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. Choose the first one worksheet that you want to hide

2. Press and hold the CTRL key on your computer keyboard 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 the Hide Possibility.

Hide tables in Excel

This will immediately hide all the selected worksheets in the Excel workbook.

2. Manually unhide sheets in Excel

If you are dealing with only a few hidden worksheets, you can unhide some or all hidden worksheets manually by following the steps below.

1. Right-click an existing or visible one Worksheet tab and click the unhide Possibility.

Show tables in Excel

2. In the Unhide dialog box, select the worksheet you want to show and click on it OK.

Show Pivot Table dialog box

Note: You can only select one worksheet at a time.

3. Repeat the above steps (1~3) to show other worksheets.

As mentioned above, there is no built-in option in Excel to quickly select and unhide multiple or all hidden worksheets.

3. Unhide all worksheets in Excel with VB editor

While Excel doesn’t have a built-in option to show all worksheets at once, you can run some simple code in the Visual Basic Editor to show all worksheets at once.

1. Right-click on a visible one worksheet and select show code Option to open the Visual Basic Editor.

Show code option in Excel

2. On the Visual Basic Editor screen, click the Show tab and select Instant window in the drop down menu.

Open Immediate Window in VB Editor

3. Type in the immediate window For each sheet in Thisworkbook.sheets: sheet.visible=true: Next sheet and beat those Enter key on your computer’s keyboard.

VB code to unhide all worksheets in Excel

Once the above code is executed, all hidden worksheets will be unhidden.

4. Unhide all worksheets in Excel with macro

Perhaps the best way to unhide all worksheets in Excel is to create a macro in the personal macro workbook and save the macro icon in the quick access toolbar.

Once the macro icon is available in the quick access toolbar, you can expand all sheets in any workbook by clicking the macro icon.

1. Click on that Show tab > macros > record macro.

Record macro option in Excel

2. In the dialog box, select Record Macro Personal macro workbook option in the “Save Macro” section and click OK.

Record macro dialog box in Excel

3. Then click on the Show tab > macros > stop recording Possibility.

Stop recording macro option in Excel

4. Now right click on any tab of the worksheet and click show code Possibility.

Show code option in Excel

5. Double-click on the Visual Basic Editor screen module object under ‘Personal.XLSB’ entry > remove any existing codes and add them Code to unhide all sheets in the Personal.XLSB module window.

Macro code to unhide all worksheets in Excel

Below is the code that you can copy and paste into the module window.

Sub UnhideAllSheets()
For each sheet in sheets
Sheet.Visible = True
next sheet
end sub

6. After adding the code, click the save icon in the toolbar and close the Vb editor.

The next step is to add the UnhideAllSheets Code icon to the quick access toolbar so that you can unhide all worksheets at once just by clicking this icon.

7. Click on Customize the Quick Access Toolbar icon and select More commands in the drop-down list.

Customize the Quick Access Toolbar icon in Excel

8th. In the Excel Options dialog box, select macros In the “Choose commands from” section, select the macro Show all sheets and click the Add button.

Add the UnhideAllSheets macro icon to the Excel Quick Access Toolbar
9. Click on OK to save the change.

10 Now if you look at the Quick Access Toolbar, you’ll see the UnhideAllSheets icon.

Expand All Sheets icon in Quick Access Toolbar

This way you can expand all worksheets at once by just clicking Expand All Sheets icon. in the quick access toolbar.

Related

Leave a Reply

Your email address will not be published.