Unhide multiple sheets in Excel

Microsoft Excel has hundreds of functions and commands, but even so there are some things the Microsoft engineers failed to include. One of my recurrent annoyances is the lack of a command to unhide more than one sheet at a time. Hiding several sheets at once is very easy: you just select them by holding down the Ctrl key and then hide the group from the Format menu. The unhide option on the other hand presents you with a list of hidden sheets where you can only select one item to display it again.

When working with large files and lots of sheets, I prefer to hide the sheets where data is stored or where intermediary calculations are performed, and leave only the final results and charts visible to improve the readability of the file. But Excel makes it hard to bring back these sheets if you want to edit them again.

The only solution I have found to this problem is to create a VBA macro that runs through the list of sheets and toggles their status to visible. The code is very short, only a couple of lines:

Sub Unhide_all_sheets()
' Unhide all sheets with one click
Dim sh As Worksheet
For Each sh In Sheets
sh.Visible = xlSheetVisible
Next
End Sub

You can add this code to your file by opening the Visual Basic Editor (for example with the keyboard shortcut Alt+F11) and pasting the code in a new module. If you also protect your hidden sheets, you can add the line sh.Unprotect in the For Each… Next loop to easily unprotect all the sheets in the file.

To integrate the new function in Excel, the most efficient solution is to create a new menu entry (for older Excel versions, including 2003) or a new button and assign this macro to it. And if you want the macro to be available in all the new files, you should add it directly in the Excel default template, following the instructions in this article.

Update: As someone mentioned in the comments, the code here throws a Type mismatch error in Excel 2010, likely caused by some changes to VBA for this version. Fortunately it's easy enough to fix, just remove line 3 from the code, Dim sh As Worksheet. This allows Excel to guess on its own the type of variable it needs. The change has propagated to Excel 2013 as well, so if you are using the Preview you also need to discard that code line for the macro to work. And, before you ask, no, Excel 2013 doesn't have an easier way to unhide several sheets simultaneously…

Update: More than 10 years after originally writing this article, Microsoft has finally updated Excel with an option to unhide several sheets from the UI, at least for Office 365 subscribers. In the Unhide dialog users can now select multiple sheets by holding down Ctrl or Shift and unhiding those with a single click.