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…
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.
Unhide very hidden sheets
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”.
How to unhide hidden worksheets faster?
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”.
How to unhide all hidden worksheets at once?
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.
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 moreDownload Free Trial (No sign-up, download starts directly) More than 10k people on Facebook can't be wrong.
Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.
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.
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Yes, I want to be an Excel expert!
Subscribe to our popular Excel newsletter! You get all this:
Excel tips, tricks and tutorials. 1x per month. No spam. Promised.
Comments 2
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.
Anthony
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?