How to unhide a very hidden worksheet?
You see in the formula bar that the cell is referred to a worksheet called “VeryHidden”. However you don’t see it on the sheet tab.
Naturally you right-click on a sheet tab in order to unhide the worksheet “VeryHidden”.
The WHAT… moment
You don’t see any “VeryHidden” in the Unhide sheet dialog box!
How are we supposed to unhide a sheet that is not hidden??…. Wait, if it is not hidden, why couldn’t I see it on the worksheet tab?
Don’t doubt your eyesight. The sheet “VeryHidden” is hidden, that’s why you do not see it on worksheet tabs. Then why it is not found under “Unhide sheet”? Simply because the sheet is not only hidden; but very hidden.
Told by its name, a very hidden sheet is very hidden. It means it is very hidden. 😛
So what does “Very Hidden” mean?
We cannot unhide it by the “regular” routine of unhiding sheet. We need to do it in VBE (Visual Basic Editor). Wait… Don’t be scared by anything starts with VB… It is super easy indeed.
First, open VBE
Right click on a sheet tab –> View Code (Hint: Alt+F11 is the keyboard shortcut)
Then you will see the following windows:
- Project window (Left upper)
- Properties window (Left lower)
- Code window (Right upper)
- Immediate window (Right lower)
Tip: Just in case you don’t see any of the windows mentioned, you may go to “View” to have them back.
For the purpose of this blogpost, we will only pay attention to Project (Explorer) window and Properties window.
In the Project window, you will see all opened workbook as VBA Project, and all the worksheets under it (grouped into a folder called “Microsoft Excel Objects“).
It is clear that we have four worksheets in <Sample File – Hide and Seek Worksheet>.
The four worksheets are named as Hidden, Welcome, Visible, VeryHidden respectively.
Now try to click on different objects (worksheets) and observe its properties in the Properties window underneath.
First a Visible sheet
Then a Hidden sheet
Finally a VeryHidden sheet
Now you should be aware of the three different properties of worksheet visibility and know how to set a worksheet into “VeryHidden”. You just need to know the right place to go to, and click the right field.
Obviously, to unhide a very hidden sheet, you need to change the “Visible” properties from “2-xlSheetVeryHidden” to “-1 xlSheetVisible”.
Let’s see it in action
Hidden sheets are Sought!
BTW, don’t put any confidential data or information in a VeryHidden sheet as this is not a safe way to protect your data from being exposed. Read more on Simple trick to look (hack) into hidden sheet.
Hide and Seek – External links
Pingback: “Very Hidden” Worksheets in Excel – Another eDiscovery Challenge | Excel Esquire
A simpler method:
1. Right click on any worksheet tab, for example “Visible”
2. Select the “View Code” option
3. In the lower pane (“immediate”), type:
and press Enter.
After you return to the Excel from the VBA interface, you’ll notice that the hidden sheet is now visible.
LikeLiked by 1 person
Thanks for your suggestion, Meni. VBA is no doubt a powerful tool in Excel.