In Excel, there can be two types of hidden Worksheets.
One is ‘hidden‘ and other one is ‘very hidden‘.
The normal ‘hidden’ mode is known to most of the Excel users. But the ‘very hidden’ mode is less known and Advanced Excel users make use of this feature. Very Hidden Worksheets are often used to store sensitive information and intermediate formulas.
In this tutorial, we will see the difference between hidden and very hidden worksheets.
How to hide and unhide Worksheets?
To hide a Worksheet,
right-click on the corresponding Worksheet tab and select Hide from the options.
We can hide multiple Worksheets using the same method.
What we did here is, sending the Worksheet to normal ‘hidden’ mode.
Now, to Unhide the hidden Worksheet or Worksheets,
right-click on a visible sheet tab > select Unhide…
All hidden Worksheets in the Workbook will be listed in the Unhide dialog.
Select the Worksheet or Worksheets to unhide and click on OK.
What is a Very Hidden Worksheet?
There are 3 modes of visibility for an Excel Worksheet and they are,
3. Very Hidden
Visible mode is when the user can view and access a particular Worksheet.
In both hidden modes, the Worksheet won’t be visible to the user. At the same time ‘very hidden’ mode is quite different from the normal ‘hidden’ mode.
Very Hidden Worksheets won’t be listed in the Unhide dialog and can be accessed only through the VBA interface of Excel. Unless we open the VBA Editor of Excel, we won’t be able to know whether the Workbook contains very hidden Worksheets.
To make a Worksheet Very Hidden,
go to the Developer tab of the Excel ribbon > Visual Basic
Every Worksheet present in the Workbook will be listed on the tree view on the left sidebar of the VBA Editor.
In the Properties window docked on the left side bar, click on the drop-down menu against the label Visible
There will be 3 options.
Right now the Worksheet called Marks is in visible mode (-1 – xlSheetVisible).
To make this Worksheet Very Hidden, select 2 – xlSheetVeryHidden
The Worksheet went into very hidden mode and disappeared from the sheet tabs area.
As the Worksheet is in Very Hidden mode, it won’t be listed in the Unhide dialog.
Now, to make the worksheet visible, go to the VBA Editor > select the sheet called Marks from the left side bar > go to the Properties window > select xlSheetVisible from the drop-down menu against the label Visible.
Macro to Unhide Very Hidden Worksheets
Following is the VBA code to unhide every Very Hidden Worksheet in a Workbook
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
Macro to send a worksheet to Very Hidden Mode
Sheets("Marks").Visible = xlVeryHidden
Macro to send selected worksheets to Very Hidden Mode
The following VBA code when executed will send every selected worksheet to Very Hidden mode.
Sub SelectedSheetsVeryHidden() Dim Ws As Worksheet For Each Ws In ActiveWindow.SelectedSheets Ws.Visible = xlSheetVeryHidden Next Ws End Sub