Very Hidden Sheets in Excel

by

A very hidden sheet doesn't appear in the Unhide list. You can only hide or show it from the VBA editor, which makes it useful for lookup tables and calculations you don't want users to change by accident.

HiddenRight-click tab → Hide; shows in Unhide
Very hiddenVBA editor → Visible = 2; not in Unhide
SecurityNone: it's a convenience, not protection

Three visibility settings

Setting Value How to change it
Visible −1 xlSheetVisible Normal
Hidden 0 xlSheetHidden Right-click a tab → Hide / Unhide
Very hidden 2 xlSheetVeryHidden Only from the VBA editor or a macro

Hide and unhide normally

Right-click a sheet tab → Hide. To bring it back, right-click any tab → Unhide, select the sheets (several at once in Microsoft 365) and click OK.

Hide on the sheet tab menu
Unhide dialog listing hidden sheets

Make a sheet very hidden

  1. Press Alt + F11 to open the VBA editor.
  2. Select the sheet in the Project pane on the left. Press F4 if the Properties window isn't showing.
  3. Set Visible to 2 – xlSheetVeryHidden.
Worksheets listed in the VBA editor
Visible property set to xlSheetVeryHidden

The tab disappears, and the sheet isn't listed in the Unhide dialog.

Unhide dialog without the very hidden sheet

To show it again, set Visible back to -1 – xlSheetVisible. This works in any workbook, including .xlsx files; no macros are needed.

With macros

To Code
Very-hide one sheet Sheets("Marks").Visible = xlSheetVeryHidden
Very-hide the selected sheets ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden
Show every very hidden sheet Loop with If ws.Visible = xlSheetVeryHidden Then ws.Visible = xlSheetVisible

The full macros, with steps to run them, are on the very hidden sheet macro page.

Sub ShowVeryHiddenSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = xlSheetVeryHidden Then ws.Visible = xlSheetVisible
    Next ws
End Sub

Make it harder to undo

Anyone who knows the VBA editor can unhide the sheet. To stop that:

  1. In the VBA editor: Tools → VBAProject Properties → Protection, tick Lock project for viewing and set a password.
  2. Save as .xlsm and reopen.
  3. Also use Review → Protect Workbook (structure) so sheets can't be inserted, renamed or unhidden normally.

Even then, Excel protection stops accidents, not determined people. Don't use hidden sheets for passwords or confidential data; keep those in a separate file.

Things to know

  • At least one sheet must stay visible; Excel won't let you hide the last one.
  • Formulas on other sheets still read very hidden sheets normally.
  • To check a workbook for very hidden sheets, open the VBA editor and look at each sheet's Visible property.

Related