Unhide multiple sheets in Excel

Unhide multiple sheets in Excel

Excel provides you with the ability to hide sheets so that they are not visible in the sheet tabs. Conveniently you can either hide one or many sheets at the same time. To hide many sheets you click on the sheets while holding CTRL down and then right click on one of them and click Hide. However, it is not similarly as easy to unhide multiple sheets in Excel.

Hopefully this will be addressed soon by Microsoft but in the meantime there are some options.

Hide and Unhide a regular set of multiple sheets

Firstly, the only inbuilt option that can be used is the Custom Views and this works best where you regularly want the same sheets to be hidden and unhidden.

Let us say you have the spreadsheet below and if you right click on one of the sheets and click UNHIDE you will see the following. The sheets in the red blocks are the sheets that we want to unhide at the same time on a regular basis. If you do it the normal way (right click on a sheet, click unhide you will need to repeat it 5 times).

unhide-multiple-sheets-in-excel

We can create custom views where the one version shows the spreadsheet with all these sheets unhidden and the other version has them hidden.

To create a custom view just set up the spreadsheets as you want to see it in the one view and click on the Custom View button as shown below

unhide-multiple-sheets-in-excel

If you had views they would be listed on the left but for now we can ADD a view. As shown below all you need to do is give it a name and decide whether you want the print settings, hidden rows , columns and filter settings to be remembered.

unhide-multiple-sheets-in-excel

When you click OK, you will then need to change the spreadsheet into the alternate view (by hiding or unhiding sheets, rows, columns, print settings) and then perform these steps again, giving the view a new name (e.g. Presentation View).

Now when you click on the custom view button you will have one or more different view options to choose from. Double click on the one you want or click the Show button.

unhide-multiple-sheets-in-excel

To make it easier, you can get the Custom View button to appear in your Quick Access Toolbar by right clicking on the Custom View button and clicking on the ‘Add to Quick Access Toolbar’ option.

Choose different multiple sheets to unhide

If you are not sure what needs to be unhidden, but it typically involves multiple sheets you will need to use some VBA. Several site offer the code but as an alternate, there are many free Add Ins that do the same thing.

One of the options is http://www.grbps.com/addhid.htm which provides a nice, simple interface to unhide multiple sheets at the same time.