Posts

Excel Hidden and Very Hidden Sheets - What's the difference?

avatar of @theexcelclub
25
@theexcelclub
·
0 views
·
5 min read

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.

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.

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.

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.

    For Each wks In Worksheets

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

    Next

End Sub

Take A FREE course with us Today!

The Ultimate Excel Formulas Course

Become a Power Pivot Hero

GET and TRANSFORM DATA like a PRO

Learn DAX for Power Pivot and Power BI

 

Don’t have a Hive wallet or a Steempress Account?

Have questions?  Please use the Hive powered comments section below and we will do our best to help you.  Alternatively, you can contact us with this link.

Like what you see? I do hope that you will share this article across your social profiles

Community Invitation

  • Excel For All -

Decentralized and tokenized

Join today

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox