There are two different levels of hidden sheets within Excel, hidden and the less well-known very hidden worksheets.
We can easily hide our worksheets by right-clicking the sheet tab and selecting hide. There’s a more extreme option available.
Very Hidden means the sheet/tab doesn’t show up at all in regular Excel and ensures the sheet cannot be made visible to users through the Excel user interface. Cells and ranges in a Very Hidden sheet can be referenced in the rest of the workbook but the sheet itself doesn’t appear.
A setting in Developer mode needs changing to reveal a Very Hidden sheet.
Why use Very Hidden Excel sheets?
Very Hidden sheets are useful for keeping the ‘under the hood’ parts of a workbook out of sight and reduce the risk of accidental tinkering.
Sheets of constants or interim calculations can be Very Hidden.
Entire reference tables or data imported can be Very Hidden.
Older or future versions of a worksheet could be kept out of sight.
Make a sheet Very Hidden
The Very Hidden function is a setting in Microsoft Visual Basic for Applications (VBA) window.
- Right click on the sheet tab and select view code, or alternatively launch the VBA by the keyboard shortcut ALT + F11 or ALT + t, m, v
2. In the VBAProject window, select the sheet you wish to hide
3. Press F4 or click View > Properties. This will open the Properties window to appear just below the Project – VBAProject window
4. In the Properties window, set the Visible property to 2 – xlSheetVeryHidden.
The Visual Basic Editor has three visible property options:
- xlSheetVisible (or TRUE) – the sheet is visible
- xlSheetHidden (or FALSE) – the sheet is hidden
- xlSheetVeryHidden – the sheet is very hidden
Very Hidden sheet becomes Visible again
Now the worksheet tab doesn’t appear in Excel and there’s no Unhide option. A regular Excel user would not know the Very Hidden sheet exists at all.
If you need to view the sheet again, head back to the Visual Basic Editor via View Code or ALT + F11 or ALT + t, m, v)and change from xlSheetVeryHidden to xlSheetVisible
