Additional Info


  • Excel Book Excerpt

Excel Unhide All Sheets

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Unhide All Sheets

Problem: If you use Group Mode, you can hide a bunch of worksheets in one command.


Figure 1200 Hide many worksheets in one command.

However, there is no way to unhide all of the worksheets in a single command. You have to do Home, Format, Hide & Unhide, Unhide Sheets to get to this dialog. You can not select multiple worksheets here, so you have to repeat that command for every worksheet.


Figure 1201 Unhide sheets one at a time.

Strategy: Use the View Manager. Create one view with the worksheets hidden. Create another view with the worksheets visible.

To solve the current problem, follow these steps:

1. Select View, Custom Views, Add.

2. Assuming the worksheets are currently hidden, use a name such as SheetsHidden.


Figure 1202 Set up a view to remember which sheets to hide.

3. Unhide all the worksheets. If you want a quick way to do this, press Alt+F11. Press Ctrl+G. Type “for each w in activeworkbook.Worksheets : w.visible = true : next" and press Enter. Press Alt+Q.


Figure 1203 Run a macro in the immediate pane to unhide all sheets.

4. Now that the worksheets are unhidden, select View, Custom Views, Add. Use a name such as Unhidden.

5. To quickly switch between the two views, use View, Custom Views. Select the correct view and click Show.


Figure 1204 Switch to a different view.

Gotcha: If any of your worksheets use a table, you can not use views. In this case, I would add a macro to your personal macro workbook with the code from step 3. For a demo of this, search the Internet for Learn Excel 611.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: