Very hidden sheets in Excel

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.

What is a very hidden worksheet in Excel?

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:

  • xlSheetVisible (or TRUE) - the sheet is visible
  • xlSheetHidden (or FALSE) - the sheet is hidden
  • xlSheetVeryHidden - the sheet is very hidden

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.

How to make Excel worksheets 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.

Make a worksheet very hidden by changing its Visible property

If you want to completely hide just one or two sheets, you can change the Visible property of each sheet manually. Here's how:

  1. Press Alt + F11 or click the Visual Basic button on the Developer tab. This will open the Visual Basic Editor with the Project Explorer window in the top-left panel displaying a tree of all open workbooks and their sheets.
  2. Press F4 or click View > Properties. This will force the Properties window to appear just below Project Explorer (please see the screenshot below). If the Properties window is already there, skip this step :)
  3. In the Project Explorer window, click on the worksheet you want to make very hidden to select it.
  4. In the Properties window, set the Visible property to 2 - xlSheetVeryHidden.

Make an Excel worksheet very hidden

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.

Make active worksheet very hidden with VBA code

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:

Sub VeryHiddenActiveSheet() ActiveSheet.Visible = xlSheetVeryHidden End Sub

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:

Sub VeryHiddenActiveSheet() On Error GoTo ErrorHandler ActiveSheet.Visible = xlSheetVeryHidden Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheet" End Sub

Make multiple worksheets very hidden with VBA code

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.

Sub VeryHiddenSelectedSheets() Dim wks As Worksheet On Error GoTo ErrorHandler For Each wks In ActiveWindow.SelectedSheets wks.Visible = xlSheetVeryHidden Next Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheets" End Sub

How to unhide very hidden sheets in Excel

Now that you know how to completely hide sheets in Excel, it's time to talk about how you can view very hidden sheets.

Unhide a very hidden worksheet by changing its Visible property

To be able to see a very hidden worksheet again, you just need to change its Visible property back to xlSheetVisible.

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the VBAProject window, select the worksheet you want to unhide.
  3. In the Properties window, set the Visible property to -1 - xlSheetVisible.

Done!
Unhiding a very hidden sheet in Excel

Unhide all very hidden sheets with VBA

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:

Sub UnhideVeryHiddenSheets() Dim wks As Worksheet For Each wks In Worksheets If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible Next End Sub

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.

Unhide all hidden and very hidden sheets at a time

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.

Sub UnhideAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub

How to use Very Hidden Sheets macros

To insert any of the above macros in your Excel workbook, perform these usual steps:

  1. Open the workbook where you want to hide or unhide sheets.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the code in the Code window.
  5. Press F5 to run the macro.

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:

  • VeryHiddenActiveSheet - makes an active sheet very hidden.
  • VeryHiddenSelectedSheets - makes all selected sheets very hidden.
  • UnhideVeryHiddenSheets - unhides all very hidden sheets in an active workbook.
  • UnhideAllSheets - shows all hidden sheets in an active workbook (hidden normally and very hidden).

To run the macros in your Excel, you do the following:

  1. Open the downloaded workbook and enable the macros if prompted.
  2. Open your own workbook.
  3. In your workbook, press Alt + F8, select the macro of interest, and click Run.

For example, here's how you can make all of the selected worksheets very hidden:
A macro to 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!

Sample workbook for download

Very Hidden Sheets macros (.xlsm file)

Table of contents

52 comments

  1. Hi Team,

    As I need to unhide a sheet and go to particular cell in that sheet use if condition, Kindly sort out

  2. Hello
    Thanks for sharing valuable tips
    I dowloaded a free spreadsheet on the web. I have been looking for the code in vain.
    How to find the code out and go trough it fr needed changement pls

  3. Thank you very much. I learn something new today. It was very useful to me.

  4. I HAD ONE WORKBOOK THAT CONTAINS 23 SHEETS IN IT AND I SAVED THAT DATA BEFORE I LEFT THE OFFICE DAY BEFORE YESTERDAY. NEXT DAY WHEN I OPEN THAT TO START WORK ON IT AGAIN IT SHOWING ONLY 11 SHEETS OTHER SHEETS ARE NOT SHOWING

  5. Here is what I did. I had my xlsx file open.
    I wanted to unhide the entire worksheet (there was only 1 worksheet in this workbook)
    I clicked on the upper left hand corner of the sheet to highlight the entire worksheet
    I clicked on View & then Hide (I'm not sure why as I was trying to UN-hide a few hidden rows.
    Then the file shut down and closed.
    I found the file but cannot open it at all.
    I try VBX (Alt-F11) but would not open the excel file reporting an error 'could not be loaded'.
    Now what? Any help is appreciated. I can find the file but cannot open it.
    Part 2 - When I try to change the name of the file it says 'File In Use, close the file and try again'. But No way is it open unless it is so hidden I can't see it?
    Thanks Dan

  6. Hi. i want to check if you can password protect very hidden sheets.

  7. I want to check if my excel file has any hidden tabs / hidden columns/ hidden rows using asp.net and c# code. Can anyone help me how to code this?

  8. HOW NICE THANKZ VERY HELPFULL TO ME

  9. Great work.
    I tried to unhide a worksheet using your visual basic editor version.
    When I try to change from 0-xlSheetHidden to -1-xlSheetVisible i get the error
    "Unable to set the visible property of the Worksheet class"
    I recognize that all right mouse click function are greyed out.
    Is there a way to sort this

    • Hi! Did you find a way to solve it? I'm having the same problem in my workbook and I didn't find anything helpful.

      • I am also having this issue - have either of you figured out how to resolve it?
        Thanks!

        • I had the same issue and fixed it once I realized the sheet itself was password protected, when I meant to protect the workbook. Once I put in the password, I was unable to unhide the sheet

    • Have you tried saving the file as .xlsm that's what worked for me.

  10. Whenever I hide a sheet that contains a pivot table I use in a macro. The macro stops working. Any way to get around this? Thanks for your help

  11. I have tried to run the unhide very secret sheets macro and it generates the following error.
    Run - time error '1004'
    Method 'Visible' of object'-worksheet' failed

    Can anyone point me in the right direction?

  12. I have created an Excel worksheet called "BI" some time ago with a button called "Start" that starts a macro. If I set this worksheet to Design Mode and I move the cursor to this button the cursor changes to a hand with a pointed finger and not to an arrowed cross like other buttons that I have recently added. If I double click on the name of the sheet in VBA Project explorer it shows me the other buttons but not this one. Is this button very hidden? How do I unhide it?

  13. YES! Thank you!!!!

  14. Does anyone know whether a Very Hidden Sheet is calculated if a user has Auto Calc on / hits F9 / calculate on the Excel Interface?

    Essentially I have a Macro process that will utilise \ calculate the Very Hidden Sheet when running, but I dont want the sheet to Calculate from the Excel Interface side. I want it to ONLY calculate when running the Macro - the end user still does need to be able to calculate the data on the Visible / Standard Hidden sheets.

    Cheers,

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)