Unhide All Hidden and Very Hidden Worksheets in Excel at Once
Unhiding hidden worksheets in Excel can be troublesome, especially if there are many hidden worksheets in your workbook. Usually, you would right click on any worksheet name on the bottom of the window and press “Unhide”. You can then choose one (and only one) worksheet at the same time for unhiding. After unhiding three worksheets like this, you will start feeling annoyed. After 10, you are going to hate Excel…
Contents
Unhide worksheets manually
Unhide worksheets manually by right clicking on any other worksheet.
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.
How to hide or unhide a “very hidden” worksheet in 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”.
The answer to this question (like many other Excel questions…) is: 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 above). 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”.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
- Learn how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
Steps for pasting the VBA function to your Excel Workbook
There is a faster way of unhiding all hidden and very hidden worksheets: Using VBA. As usual, do the following steps:
- Go to the developer ribbon.
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 Sub |
Expert tip: Professor Excel Tools for comfortably unhiding all worksheets
Unhide all worksheets at once with the Excel add-in “Professor Excel Tools”
As unhiding worksheets can be very troublesome, we’ve included a very simple function into Professor Excel Tools: Unhide all hidden as well as very hidden worksheets with just two clicks:
- Click on the ‘Unhide Sheets’ button within the Professor Excel ribbon.
- A message box will pop up, saying how many hidden and very hidden sheets there are in your current workbook. If you want to unhide them, click OK.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Comments 1
JohnnyNoBrakes
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.