Hide Worksheets in Excel

There are some things you can do in the VBA Editor that won't trigger the dreaded warning about opening a file with a macro. Hiding a worksheet so nobody can find it is one of those things.

Excel worksheets have a visible property that can take on one of three values.

xlSheetVisible
xlSheetHidden
xlSheetVeryHidden

By default all worksheets are visible. Meaning the visible property is true, or set to xlSheetVisible. When you hide a worksheet, Excel changes the visible property to xlSheetHidden.

Hiding a worksheet is simple, just right click on the worksheet tab and select Hide from the pop-up menu.

However, anybody looking for a hidden worksheet can find it easily by right clicking on any worksheet and click Unhide from the pop-up menu.

Note: If the Unhide button is grayed out there are no sheets hidden by using this method.

Make a Worksheet Disappear

If you really want to keep a worksheet secret from most people, set the visible property of a worksheet to xlSheetVeryHidden.

Here's how you make a worksheet disappear:

Use the keyboard shortcut Alt+F11 to bring up the VBA Editor.
Go to the View menu and select Project Explorer to make sure it's visible (Ctrl+R shortcut)
Go to the View menu and select Properties Window to make sure it's visible (F4 shortcut)
In the Project Explorer window click on the worksheet you want hidden
In the Properties Window select the Visible property
Click the Visible property drop-down menu and select 2 - xlSheetVeryHidden

Close and return to Excel by using the keyboard shortcut Alt+Q

Now MySecretWorksheet doesn't show in the Unhide dialog box, only the MyHiddenWorksheet shows, which was hidden in the normal manner.

If no other worksheets are hidden, then the pop-up box shows a grayed out Unhide option.

The only place to find a VeryHidden worksheet is in the VBA Editor, which most people wouldn't think to look. And if you really want to keep them out, you can set a password to keep them from opening the VBA Editor.

Bonus Tip - Use VBA to Change the Sheet Visible Property

You can change the visible property of a worksheet from the Immediate window in the VBA Editor by using the (Name) property of the sheet.

Open the VBA Editor (Alt+F11)
Show the Immediate window (Ctrl+G)
Type the sheet (Name) followed by a period and visible, a space, then equals sign (=) and select xlSheetVeryHidden from the drop-down list, and hit enter.

For example type: sheet3.visible = xlSheetVeryHidden and hit enter.

The picture below depicts how this is done.

You'll notice in the Properties window that Sheet2 has a Name property and a (Name) property. The (Name) property can only be changed in the VBA Editor Properties window.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

Khushnood Viccaji12 listopada 2010 11:51

Hi, this is something I've used for a few years. It is very effective and useful to keep critical sheets hidden from prying users ! However, there's one other way for a savvy user to figure out if there are xlVeryHidden sheets in a workbook, using VBA (even if the VBA project is password protected). In another workbook, write a sub with the single line Application.Dialogs(xlDialogWorkbookUnhide).Show Executing this code will display the "Unhide sheets" dialog, with ALL hidden sheets listed, whether Hidden or VeryHidden :) Of course, if the workbook structure is password protected, then this code will not work either. Khushnood

Gregoryexcelsemipro.com12 listopada 2010 14:47

I tried this out and it didn't work at first, but found that the workbook with the hidden worksheets has to be the active workbook, and then the code from the other workbook will, as you said, show all the hidden worksheets, both Hidden and VeryHidden. Nice tip. Thanks for the great comment.

John24 lutego 2012 02:22

"hidden worksheets has to be the active workbook, and then the code from the other workbook will, as you said, show all the hidden worksheets" Please, could you explain the steps to do this? Have one workbook active but run the code from another workbook? As soon as I go to the one with the code, doesn't that one then become the active one? Sorry, but I'm a novice on this. Thanks. John

Gregoryexcelsemipro.com24 lutego 2012 03:52

@John, the quote in your comment comes from my response to @Khushnood's comment where he indicates the code Application.Dialogs(xlDialogWorkbookUnhide).Show be run from another workbook, but I couldn't get this to work. I had to use the active workbook, which is the one that has the code. So I created a new workbook, with three sheets. I hid sheet2, then opened the VBA editor and set the sheet3 property to xlSheetVeryHidden. Then I entered the code above into the Immediate widow in the VBA editor an hit enter, whereupon I got a dialog box showing both hidden sheets.