, , , , , , , , , /

Making Excel worksheets Very Hidden

by     

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

  1. 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

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address