Hide and Seek – #Excel Worksheet

How to unhide a very hidden worksheet?

Situation:

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.

Excel Tip - Hide and Seek Worksheet

Naturally you right-click on a sheet tab in order to unhide the worksheet “VeryHidden”.

Excel Tip - Hide and Seek Worksheet1

The WHAT… moment

You don’t see any “VeryHidden” in the Unhide sheet dialog box! Excel Tip - Hide and Seek Worksheet2

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)

Excel Tip - Hide and Seek Worksheet3

Then you will see the following windows:

  1. Project window (Left upper)
  2. Properties window (Left lower)
  3. Code window (Right upper)
  4. Immediate window (Right lower)

Excel Tip - Hide and Seek Worksheet4

Tip: Just in case you don’t see any of the windows mentioned, you may go to “View” to have them back.

Excel Tip - Hide and Seek Worksheet5

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“).

Excel Tip - Hide and Seek Worksheet4.1

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 sheetExcel Tip - Hide and Seek Worksheet4.2

Then a Hidden sheet

Excel Tip - Hide and Seek Worksheet4.3

Finally a VeryHidden sheet

Excel Tip - Hide and Seek Worksheet4.4

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

Excel Tips - Hide and Seek Worksheet

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.

Next topic:

Hide and Seek – External links

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

3 Responses to Hide and Seek – #Excel Worksheet

  1. Pingback: “Very Hidden” Worksheets in Excel – Another eDiscovery Challenge | Excel Esquire

  2. Meni Porat says:

    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:
    Sheet4.Visible=xlSheetVisible
    and press Enter.
    After you return to the Excel from the VBA interface, you’ll notice that the hidden sheet is now visible.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.