This tutorial clarifies the difference between hidden and very hidden sheets, explains how to make a worksheet very hidden and how to view very hidden sheets in Excel.
Are you exasperated because you cannot find the spreadsheet one of your formulas refers to? The sheet does not appear among other tabs at the bottom of your workbook, nor does it show up in the Unhide dialog box. Where on earth could that sheet be? Simply, it is very hidden.
As everyone knows, an Excel sheet can be visible or hidden. As a matter of fact, there are two levels of worksheet hiding: hidden and very hidden.
Unhiding a sheet that was hidden normally is very easy. All you have to do is right-click any visible worksheet, click Unhide, and select the sheet you want to view. Very hidden sheets are a different story. If the workbook contains only very hidden sheets, you won't even be able to open the Unhide dialog box because the Unhide command will be disabled. If the workbook contains both hidden and very hidden sheets, the Unhide dialog will be available, but very hidden sheets won't be listed there.
Technically, how does Excel distinguish between hidden and very hidden worksheets? By the Visible property of the sheet, which can have one of these values:
While anyone can toggle between TRUE (visible) and FALSE (hidden) by using Excel's Unhide or Hide commands, the xlVeryHidden value can only be set from within the Visual Basic Editor.
From the user's perspective, what is the difference between hidden and very hidden sheets? It is simply this: a very hidden sheet cannot be made visible via the Excel user interface, the only way to unhide it is with VBA. So, if you want to make some of your worksheets much more difficult to unhide by others (e.g. those containing sensitive information or intermediate formulas), apply this higher level of sheet hiding and make them very hidden.
As already mentioned, the only way to make a sheet very hidden is by using the Visual Basic Editor. Depending on how many sheets you want to hide, you can proceed with one of the following methods.
If you want to completely hide just one or two sheets, you can change the Visible property of each sheet manually. Here's how:
That's it! As soon the Visible property is changed, the corresponding sheet tab will disappear from the bottom of your workbook. Repeat the above steps for other sheets if necessary and close the Visual Basic Editor window when finished.
If you have to hide sheets on a regular basis and are annoyed about having to do it manually, you can automate the job with a single line of code. Here's the macro that makes an active worksheet very hidden:
If you are writing a macro for other users, you may want to take care of situations when a workbook contains only one visible sheet. As you may remember, it's not possible to hide absolutely all worksheets in an Excel file (whether you are making them hidden or very hidden), at least one sheet should remain in view. So, to warn your users about this limitation, wrap the above macro in an On Error block like this:
In case you want to set all selected sheets to be very hidden, go through all of the selected sheets in an active workbook (ActiveWindow) one by one and change their Visible property to xlSheetVeryHidden.
Now that you know how to completely hide sheets in Excel, it's time to talk about how you can view very hidden sheets.
To be able to see a very hidden worksheet again, you just need to change its Visible property back to xlSheetVisible.
If you have quite a lot of very hidden sheets and you want to make them all visible again, this macro will work a treat:
Note. This macro only unhides very hidden sheets, not worksheets hidden normally. If you want to display absolutely all hidden sheets, then use the below one.
To show all hidden sheets in an active workbook in one go, you simply set the Visible property of each sheet to TRUE or xlSheetVisible.
To insert any of the above macros in your Excel workbook, perform these usual steps:
To keep the macro, be sure to save your file as an Excel macro-enabled workbook (.xlsm). For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Alternatively, you can download our sample workbook with macros and run the desired macro directly from that workbook.
The sample workbook contains the following macros:
To run the macros in your Excel, you do the following:
For example, here's how you can make all of the selected worksheets very hidden:
I hope this short tutorial has shed some light on Excel's very hidden sheets. I thank you for reading and hope to see you on our blog next week!
Very Hidden Sheets macros (.xlsm file)
Table of contents