Unhiding hidden worksheets in Excel was for a long time troublesome, especially if there were many hidden worksheets in your workbook. Fortunately, Microsoft has just released a new feature in Office 365 (now: “Microsoft 365) for unhiding multiple sheets at once. So far, so good. But what, if you want to unhide “very hidden” sheets? Or, what if you work with older Office versions? Here is everything you need to know!
Contents
In this first part you learn to unhide “normal” hidden worksheets in Excel. This regards the far majority of hidden worksheets. If you “miss” a worksheet from the list of hidden sheets, it’s possible that it is very hidden. For very hidden sheets, please click here to scroll down.
Unhide worksheets manually
The first step: Check if there are any hidden worksheets. Therefore, right click on any sheet name. If the “Unhide” option is greyed out, there are no hidden worksheets in your workbook.
If the “Unhide” button in clickable, click on it. Now you’ll see a list with all hidden (not very hidden) worksheets. Click on any worksheet name in that list and click on OK.
You want to hide a worksheet? It works basically the same way, just click “Hide” instead of “Unhide”. Please refer to this article for more information.
Update 2021: Unhiding multiple sheets at once with built-in methods (Office 365)
It’s finally here: Unhide multiple worksheets at once in Excel. Microsoft has listened to user suggestions and implemented a fast method to unhide several worksheets:
- Right-click on any sheet name.
- Click on “Unhide…” (again: if this button is greyed-out it means that there are no hidden sheets in your file).
- Hold down the Ctrl key on the keyboard and click on the sheet names one by one you want to show.
Alternatively, hold down the Shift key to select many sheets at once.
Please note: This feature is only available in Office 365 since beginning of 2021. If you work with older versions of Office / Excel, you have to use one of the following methods.
Unhiding multiple worksheets at once with older versions of Excel
Again, this is primarily revelant for older versions of Excel (not Office 365 since beginning of 2021; if you are on the current Excel version please scroll up). There is another way of unhiding all hidden and very hidden worksheets: Using VBA. As usual, do the following steps:
- Go to the developer ribbon in Excel (or just press Alt + F11 on the keyboard and skip the next step number two).
- Click on “Visual Basic”.
- Add a new module. Therefore, right click on any sheet and then click on “Insert” and “Module”.
- Paste the following code.
- Press start on the top.
You can follow the steps on the screenshot on the right-hand-side. If you need assistance with adding the VBA module, please refer to this article.
Sub PROFEXUnhideAllWorksheets()
'Unhide all sheets in workbook.
Dim PROFEXWorksheet As Worksheet
'A variable for going through all the worksheets
For Each PROFEXWorksheet In ActiveWorkbook.Worksheets
'Do for each worksheet, no matter if hidden or unhidden
PROFEXWorksheet.Visible = xlSheetVisible
'Make the current worksheet visible
Next PROFEXWorksheet
'End of the loop...
End SubTo speed up unhiding sheets with older versions than the current Office 365 version, you can use keyboard shortcuts. Press the following keys after each other:
Alt –> O –> H –> U –> Enter
Repeat these steps – this should speed up the process of unhiding. There is one more disadvantage: This method doesn’t work for all “very hidden” worksheets in Excel.
Very hidden worksheets you have to unhide within the VBA editor (see below in part 2). Select the very hidden worksheet and set the visibility to show. Therefore click on “Visible” in the properties window (usually on the left bottom corner) and set it to “xlSheetVisible”.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Yes, it sounds strange. Maybe programmer humor… But there is the option to set sheets to “very hidden” in Excel. For unhiding “very hidden” worksheets, you have to open the VBA editor.
- Click on “Visual Basic” on the left hand side of the Developer ribbon.
- You can see a list of all worksheets within the Project Explorer.
- Change the status “Visible” to “xlSheetVisible”.
As unhiding worksheets can be very troublesome, we have included a very simple function into Professor Excel Tools: A worksheet manager.
Just click on “Unhide Sheets” on the “Professor Excel” ribbon. If you don’t have it yet: Download the add-in here.
- Check the summary: How many hidden and very hidden worksheets does your file have?
- Conveniently see all worksheets in a list and select them easily. See also the visibility level of “Visible”, “Hidden” or “Very hidden”.
- Either select all sheets from the list or click one of the easy select button (for example, select all hidden but not very hidden worksheets).
- Action: Decide, what to do with these sheets: Unhide them, hide them or set them to very hidden.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
To unhide the sheets you can rename the workbook to zip, extract, remove all of the state=”veryHidden” attributes from the sheets in xl\workbook.xml, save, re-package up (windows explorer and 7Zip failed, but .Net’s ZipFile.CreateFromDirectory which requires a reference to the System.IO.Compression.FileSystem assembly worked), and then rename back to xlsm. If the startup code hides the form then you will need to open Excel, File->Open, browse to the file, hold SHIFT, and open the file to prevent the startup code from running.
I am trying to figure out a way to unlock a hidden or locked worksheet by creating an action on the previous worksheet. Is this possible?