Hidden and Very Hidden Sheets in Excel – Skip to Video

As most of us know, Excel, being such an amazing tool, gives us the ability to Hide sheets from view.  However, what many are not aware of is that Excel also offers the ability to set sheets as very hidden. 

Hidden sheets are common in large workbooks.  They help to declutter a workbook.  And, they also offer some protection.  By hiding a sheet with important formulas, a user would have to go out of their way to unhide the sheet to edit or delete the formula.  But that is by no means foolproof, as most users know how to unhide a sheet if they really wanted too.  Adding password protection also has its limitations as it can lock other functionalities, we might not want to be locked.  In cases like this, the use of very hidden sheets comes invaluable.

However, very hidden sheets also pose a threat as they can these sheets can be used to hide data or formulas whose purpose is of fraudulent nature.

How to hide and unhide sheets in Excel

To hide a worksheet in Excel, Select the worksheet and right-click.  Then select Hide.

You can see now that Sheet 1 has been hidden as the tab is no longer available.

To unhide a sheet, select a tab, right-click, and select unhide. Then select the hidden sheet you want to unhide.

 Very Hidden Sheets

The difference between hidden and very hidden sheets in an Excel workbook is the ability to find and unhide them.  Very hidden sheets are not exposed when you select Unhide.  You do not even have the option from the tab to make a sheet very hidden in the first place.  So how do you hide and unhide very hidden sheets in Excel?

Burying sheets as deep as very hidden is done via the Developer ribbon.  If the developer tab is not available, you will need to first switch it on.  Under file, select Options, then select Customize ribbon.  Then under Main Tabs, check the box for Developer.

Once the developer ribbon is available, select Visual Basics.

This will open the VBA editor.  On the right of the screen, you will have the Project Explorer and the Properties.

If these are not showing, select View and then select Project Explorer and Properties Window.

The Project Explorer shows all the worksheets in the workbook regardless of their visibility state.  By selecting any of the sheets we can see the properties of the selected sheet in the properties pane.

At the bottom of the Properties panel, we have a Visible option.  Using the dropdown users can select between -1 – xlSheetVisible, 0 – xlSheetHidden, and 2- xlSheetVeryHidden.

If we select 2- xlSheetVeryHidden, the worksheet will be removed from view and will not be available to unhide from the tabs.

How do you know if your workbook has very hidden sheets?

It can be hard to identify very hidden sheets in a workbook.  You can unhide very hidden sheets using the visual basics editor and changing the visual properties of the sheet as we did to make the sheet very hidden in the first place.  But finding the very hidden sheets in the first place can be hard. Maybe the workbook has some, maybe it does not.

To locate unhidden sheets, you could go through all the sheets in the visual basic editor and check their properties, but that would be a long-time consuming exercise. Especially if it is a particularly large workbook with a lot of sheets.  And then sure there might not even be very hidden sheets.

Most Spreadsheet auditing software will include a check for very hidden sheets.  Excel’s own add-in, Spreadsheet inquire will allow you to carry out a workbook analysis that will identify the number and name of very hidden sheets.

Once you have identified a workbook contains very hidden sheets, you can unhide all these sheets together with a bit of VBA.

Unhide Very hidden worksheets with VBA

VBA is not my strong point, so a quick search of the web and I was able to find a code to unhide all very hidden sheets in a workbook.  This code is courtesy of ablebits.com and it will unhide all hidden worksheets with the workbook all in one go.

Sub UnhideVeryHiddenSheets ()

    Dim wks As Worksheet

    For Each wks In Worksheets

        If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible

    Next

End Sub