How to Make an Excel Spreadsheet Highly Hidden and Show It (2024)

This tutorial clarifies the difference between hidden and highly hidden worksheets, explains how to make a worksheet highly hidden, and how to display highly hidden worksheets in Excel.

Are you annoyed because you can't find the worksheet that one of your formulas refers to? The sheet will not appear among other tabs at the bottom of your workbook, nor inshow offdialog box. Where on earth could that sheet be? It's just very hidden.

As everyone knows, an Excel worksheet can be visible or hidden. In fact, there are two levels at which worksheets hide:hiddenInvery hidden.

It is very easy to show a sheet that was normally hidden. All you need to do is right-click on a visible worksheet and clickshow offand select the sheet you want to view. Highly concealed sheets are a different story. If the workbook only contains highly hidden worksheets, you cannot even open itshow offdialog box, becauseshow offthe command is disabled. If the workbook contains both hidden and highly hidden worksheets,show offdialog box will be available, but highly hidden sheets will not be shown there.

How does Excel technically distinguish between hidden and highly hidden worksheets? By means ofVisibleproperty of the sheet, which can have one of these values:

xlSheetVisible(or TRUE) - the sheet is visible
xlSheetHidden(or FALSE) - the sheet is hidden
xlSheetMegetSkjult- the leaf is very hidden

While anyone with Excel can switch between TRUE (visible) and FALSE (hidden).show offofHidecommando's, fromxlVery hiddenvalue can only be set from the Visual Basic Editor.

From the user's perspective, what is the difference between hidden and highly hidden worksheets? It's simply this: a very hidden sheet cannot be made visible through the Excel user interface, the only way to display it is with VBA. So if you want to make some of your worksheets much harder to show others (for example, worksheets that contain sensitive information or intermediate formulas), apply this higher level of sheet hiding and make them very hidden.

How to Make Excel Spreadsheets Very Hidden

As already mentioned, the only way to make a sheet highly hidden is to use the Visual Basic Editor. Depending on how many worksheets you want to hide, you can proceed with one of the following methods.

Make a worksheet highly hidden by changing its Visible property

If you only want to hide one or two sheets completely, you can change thisVisibleproperty for each sheet manually. This is how you do it:

PressAlt+F11or click onVisual basisbutton onDeveloperthe tab. This opens the Visual Basic Editor with the Project Explorer window in the top left panel, showing a tree view of all open workbooks and their worksheets.
PressF4or clickDisplay>Characteristics. This will force itCharacteristicswindow that appears just below the Project Explorer (see screenshot below). IfCharacteristicsthe window is already there, skip this step :)
In the Project Explorer window, click the worksheet you want to make super hidden to select it.
I thinkCharacteristicswindow, you need to setVisibleownership2 - xlSheetVeryHidden.

That is it! So fastVisibleproperty is changed, the corresponding sheet tab at the bottom of your workbook disappears. Repeat the above steps for other worksheets as necessary and close the Visual Basic Editor window when you are done.

Make active worksheet highly hidden with VBA code

If you need to hide worksheets at regular intervals and find it annoying that you have to do it manually, you can automate the task with a single line of code. Here is the macro that makes an active worksheet very hidden:

SubVeryHiddenActiveSheet() ActiveSheet.Visible = xlSheetVeryHiddenEnd Sub

If you are writing a macro for other users, you may want to consider situations where a workbook contains only one visible sheet. As you may recall, it is not possible to hide absolutely all worksheets in an Excel file (whether you make them hidden or very hidden); at least one leaf must remain visible. So to warn your users about this limitation, wrap the above macro in oneIn case of an errorblock as follows:

SubVeryHiddenActiveSheet()Op Wrong Go to FoutHandlerActiveSheet.Visible = xlSheetVeryHiddenExit Sub FoutHandler: BerichtBox"A workbook must contain at least one visible worksheet.", kun vbOK,"Cannot hide the spreadsheet"End Sub

Make multiple worksheets well hidden with VBA code

If you want to setall selected sheetsTo be highly hidden, go through all selected sheets one by one in an active workbook (ActiveWindow) and change theirVisibleownershipxlSheetMegetSkjult.

Show a highly hidden worksheet by changing its Visible property

To see a highly hidden worksheet again, you just need to change itVisibleproperty back toxlSheetVisible.

PressAlt+F11to open the Visual Basic editor.
I thinkVBA-projectwindow, select the worksheet you want to view.
I thinkCharacteristicswindow, you need to setVisibleownership-1 -xlSheetVisible.

Finished!

Show all highly hidden sheets with VBA

If you have quite a few very hidden worksheets and you want to make them all visible again, this macro will do the trick:

SubUnhideVeryHiddenSheets()Weakto softenIfWorksheetNasty Elkto softenIWorksheetIfwks.Visible = xlSheetVeryHiddenThenwks.Visible = xlSheetVisibleFollowingEnd Sub

Remark.This macro is displayed onlyvery hidden sheets, not normally hidden worksheet. If you absolutely want to show all hidden sheets, use the below.

Show all hidden and highly hidden sheets at once

To display all hidden sheets in active workbook at once, you just need to setVisibleproperty of each sheet to SAND orxlSheetVisible.

SubShowAllSheets()Weakto softenIfWorksheetNasty Elkto softenIActiveWorkbook.Worksheets wks.Visible = xlSheetVisibleFollowingto softenEnd Sub

Using macros from highly hidden sheets

Follow these common steps to insert one of the above macros into your Excel workbook:

Open the project folder where you want to hide or show worksheets.
PressAlt+F11to open the Visual Basic editor.
Right-click in the left paneThis workbookand selectInsert>modulefrom the context menu.
Paste the code into the code window.
PressF5to run the macro.

To preserve the macro, make sure you save your file as an Excel workbook with macros (.xlsm). For detailed step-by-step instructions, seeHow to insert and run VBA code in Excel.

Alternatively, you can do that toodownload our sample workbookwith macros and run the desired macro directly from that project folder.

The sample workbook contains the following macros:

VeryHiddenActiveSheet- makes an active sheet very hidden.
VeryHiddenSelectedSheets- make all selected sheets strongly hidden.
VisVeryHiddenSheets- shows all highly hidden sheets in an active workbook.
Show all sheets- shows all hidden sheets in an active workbook (normally hidden and highly hidden).

To run the macros in Excel, follow these steps:

Open the downloaded workbook and enable the macros when prompted.
Open your own project folder.
Tap in your workbookAlt+F8, select the desired macro and clickrace.

For example, you can make all selected worksheets highly hidden:

I hope this short tutorial has shed some light on Excel's highly hidden worksheets. Thanks for reading and hopefully see you on our blog next week!

Sample project folder for download

Very hidden sheet macros(.xlsm-bestand)