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

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

Take A FREE course with us Today!

The Ultimate Excel Formulas Course

• includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero


GET and TRANSFORM DATA like a PRO
Power Query Excel 365

Learn DAX for Power Pivot and Power BI

 

 

Over to you

Do you use or have you encountered very hidden worksheets?  If so, what were the nature and use of these sheets, and what method did you use to hide and unhide them.  Please do share your experience in the comments below

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

I would suggest that you sign up directly for your own hive wallet and sign into steempress using your wallet. this way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet

>> GET HIVE WALLET NOW<<

If you sign up using the comments section below you will get a Steempress account. Steempress will hold any rewards you earn until you have a hive wallet.

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

Sign Up Now

---

Cross posted from my blog with SteemPress : https://theexcelclub.com/excel-hidden-and-very-hidden-sheets-whats-the-difference/

---