Make an Excel Worksheet Very Hidden

Unhide Dialog with Very Hidden Sheet

Did you know that Excel has two levels of hidden worksheets?  Excel has “hidden” worksheets, and, “very hidden” worksheets. This post walks through the differences, and how to hide worksheets at each level.

By default, all new worksheets are visible.  A visible worksheet’s tab appears in the bottom of the Excel window, enabling the user to click the tab in order to navigate to the worksheet.  Easy enough, let’s move on.

Visible Property

In the real world, an object is either visible or it is not.  For example, Harry Potter is typically visible.  However, when he throws on that sweet cloak of invisibility, he is not visible.  His visible status changes to false.  In the real world, the visible status of an object is either true or false, it is either visible or it is not.  In Excel however, the visible property of a worksheet object has three possible values: true, false, or….very hidden.

Hidden

When a worksheet has a visible property value of true, it is visible and can be seen and selected by the user.  When a worksheet has a visible property of false, it is hidden, and can no longer be seen from within the standard Excel user interface.  That is, the sheet’s tab is gone.  The sheet itself however, is not gone, and formulas can still retrieve values stored on a hidden sheet.  It just disappears from the user interface.

It is pretty easy to hide a sheet.  Although, if we are trying to be technical, we should say it this way: it is pretty easy to change a worksheet’s visible property from true to false.

All five worksheets in my workbook are visible in the following screenshot.

Visible Excel Worksheets

To hide a sheet, simply right-click the sheet’s tab and select hide.  You can also hide a sheet using the following ribbon command:

  • Home > Format > Hide & Unhide > Hide Worksheet

You can also hide a sheet using the following keyboard shortcut:

  • Alt+o, h, h

Sheet2 is hidden in my workbook, as shown in the screenshot below:

Hidden Worksheet

To unhide a sheet, simply right-click any sheet’s tab and select Unhide.  This reveals the Unhide dialog box as shown below.

Unhide Sheet Dialog

Pick the hidden sheet and click ok.  You can also unhide a sheet using the following ribbon command:

  • Home > Format > Hide & Unhide > Unhide Worksheet

You can also unhide a sheet using the following keyboard shortcut:

  • Alt+o, h, u

Now, here is where it gets fun, creating very hidden sheets.

Very Hidden

The third property value is very hidden.  The difference between a hidden sheet and a very hidden sheet is simply this: very hidden sheets do not appear in the Unhide dialog box.

Now, before we get to the mechanics of how to set a sheet’s property to very hidden, let me set up a bit of background that will be useful.  First, we need to understand that in Excel, there is a world outside of the typical Excel user interface.  What you see when navigating inside the Excel application window is a subset of everything available in Excel.  A primary example is macros.  Macros are not edited within the standard interface, they are edited within a utility application called the Visual Basic Editor (VBE).  The VBE is a powerful and useful utility, and Excel power users spend quite a bit of time in it.  It is inside the VBE where we change the visible property of a worksheet to very hidden.  Here is how the VBE refers to the Visible property values:

  • True = xlSheetVisible
  • False = xlSheetHidden
  • Very Hidden = xlSheetVeryHidden

You still with me?  Great, let’s get into the VBE now.

Visual Basic Editor

There are a couple of different ways to open the VBE.  You could use either of the following keyboard shortcut:

  • Alt+F11
  • Alt+t, m, v

Or, if you are more of a ribbon person, you could use the following ribbon command:

  • Developer > Visual Basic  [Note: Microsoft does not ship Excel with the Developer ribbon tab visible, so, you’ll need to turn it on.  In Excel 2010/2013: customize the ribbon and check the Developer tab.  In Excel 2007: go into options and check the “Show developer tab in ribbon” checkbox]

At this point, you should have the VBE open, and it should look a little something like the screenshot below.

Excel Visual Basic Editor

In the top left panel is the Project Explorer, where you can use the tree to navigate to any open workbook, and to any sheet.  Double-click the sheet you want to update.

[Note: if you don’t see the Project Explorer, simply select View > Project Explorer]

Then, you’ll be able to view and change the status of the Visible property in the Properties window.

[Note: if you don’t see the Properties window, simply select View > Properties Window]

On the Visible property, use the combo box to change the value to xlSheetVeryHidden, or, to either of the other options.

In the screenshot below, I selected xlSheetVeryHidden from the Visible property combo box:

xlSheetVeryHidden

Once you return to Excel, you’ll notice that the sheet’s tab is not visible.  You’ll also notice that if the workbook contains very hidden sheets, the user won’t have an option to open the Unhide dialog box.  If the workbook contains both hidden and very hidden sheets, then the user can open the Unhide dialog box, however, the very hidden sheets do not appear.  In my workbook, Sheet2 is very hidden, and Sheet3 is hidden, and the Unhide dialog box is shown in the screenshot below.

Unhide Dialog with Very Hidden Sheet

Wow, Sheet2 is very hidden!

If you want to view the sheet again, simply change the property from xlSheetVeryHidden to xlSheetVisible from within the VBE and you’ll be all set.

Now, with great power comes great responsibility, so use this super power with great care!

 

 

This article was written by jefflenning

30 comments:

  1. Shafiullah
    Reply

    But when I change it from veryhiden to visable then it gives me a message “Unable to set the visable property of the worksheet class”.

  2. jefflenning Post author
    Reply

    Shafiullah,
    A workbook must contain at least one visible sheet. Therefore, if you try to change the last visible sheet to veryhidden, you’ll receive the error message “Unable to set the Visible property of the Worksheet class”. Also, when you change a worksheet to veryhidden, then, the visual basic editor automatically changes the properties to the next visible sheet.
    Hope this information helps!
    Thanks,
    Jeff

  3. Dave
    Reply

    Another thing to note is that you cannot change the visible properties of the activeWorksheet. To make the change go back to the Workbook and select a sheet you want visible to make that the activeWorksheet. Then access the BBC and select the sheet you want to hide from the project explorer and change its properties.

  4. Hartmut
    Reply

    Hi Jeff,

    how can I make a veryhidden worksheet visible again? Because I do not see Sheet 2 anymore, I can not select it and change properties.

    1. jefflenning Post author
      Reply

      Hartmut,
      To make a veryhidden worksheet visible again, open the visual basic editor, navigate to the properties for the desired worksheet, and change the Visible status to xlSheetVisible. Please reference the screenshot above (Microsoft Visual Basic for Applications – Training Departmen…) to see the Visible drop down that modifies the Visible status.
      Hope this helps!
      Thanks
      Jeff

      1. Hartmut
        Reply

        Great Thank you

  5. Murad
    Reply

    Hi Jeff:

    Can’t another user just go into the VBE and change the worksheet from very hidden to visible? Is there a way to prevent that from happening?

    Thanks,
    Murad

    1. jefflenning Post author
      Reply

      Murad,
      Yes, that is true. You can use the Workbook Protection command to prevent a user from unhiding hidden worksheets :-)
      Thanks
      Jeff

  6. Ross
    Reply

    Great article Jeff, had no idea about the VeryHidden status but explains a few scenarios I’ve had in the past.

    Quick semi-related question – can macros function when referencing data on Hidden or VeryHidden sheets?

    Thanks,
    Ross

    1. jefflenning Post author
      Reply

      Ross,
      Thanks! Macros, as well as formulas and functions, can indeed operate on data stored on hidden and very hidden sheets…makes for some very interesting possibilities!
      Thanks
      jeff

      1. Ross
        Reply

        Thanks for confirming this Jeff, greatly appreciated.

        1. jefflenning Post author
          Reply

          :-)

  7. Diogo
    Reply

    Hey Jeff,

    I could turn a Sheet Very Hidden, but now I wanted a macro that could make it visible again,
    I tried simply using:

    Sub Unhide()
    Sheets(“Sheet1”).Visible = True
    End Sub

    But when I click the macro assigned button, it says the reference cannot be found.

    1. jefflenning Post author
      Reply

      Diogo,
      Your macro looks good and should work; my best guess is that the sheet name is different than the sheet name in the macro, ie, Sheet1. One pretty easy way to determine the sheet name is to first make the sheet Hidden instead of VeryHidden. Then, start the macro recorder and then unhide the sheet. Then stop the recorder and examine the sheet name used by the macro recorder by checking out the VBA code that is generated. Then, use that sheet reference in your Unhide macro and you should be good to go!
      Hope this helps!
      Thanks
      Jeff

  8. Leah L. Adams
    Reply

    Jeff, thanks for this info… great info. Now, i have another question. Is there any kind of visual … anything… to let the creator of the workbook/worksheet know that you have “hidden” a sheet whether it is hidden or very hidden?

    1. jefflenning Post author
      Reply

      Leah,
      If a sheet is hidden, rather than very hidden, a user will know because the Unhide command is enabled and if selected will display the Unhide dialog which contains a list of all hidden sheets. To my knowledge, there is no visual indication in the standard Excel user interface for very hidden sheets.
      Thanks
      Jeff

  9. Ian Ratcliffe
    Reply

    Thanks for this article. A question that is probably as stupid as it sounds. If a worksheet is set to normal ‘non visible’ but the workbook structure is protected, then the user can’t use the ‘unhide’ command to unhide sheets, so wouldn’t that be much the same as ‘veryhidden’?

    1. jefflenning Post author
      Reply

      Howdy Ian,
      Yes…you are absolutely correct! Workbook protection prevents users from unhiding worksheets. Indeed, this is the approach I use most frequently in practice. Very hidden can help in situations where we are precluded from using workbook protection. But, when possible, I simply hide the sheet and then turn on workbook protection because it makes the workbook easier to maintain. Thanks for the comment!
      Thanks,
      Jeff

      1. Ian Ratcliffe
        Reply

        That’s very helpful, and most kind of you to reply. Thank you.

  10. sridhar
    Reply

    Thanks it’s helpful

    1. cmmomof4
      Reply

      I would love to know if I can make a sheet with a macro very hidden at all times

      1. Kurt LeBlanc
        Reply

        cmmomof4,

        You can certainly very hide a sheet with

        sheets(2).visible = xlsheetveryhidden

        But you can’t hide all sheets. One has to be visible still!

        Hope this helps!
        Kurt LeBlanc

        1. Carol Marston
          Reply

          Hi – I always have a sheet visible, but if it is using a macro that I don’t want anyone to see, it will unhide the sheet with the macro to perform the macro. I was just wondering if there was a way around this…thank you-I appreciate the follow up!!!

          1. Kurt LeBlanc

            Hi Carol,

            It sounds like you are storing the code for your macro in a specific sheet. If you go the VBE and

            Insert>Module

            store the code there, and it should fix the problem you are having:)

            Hope this helps!
            Kurt LeBlanc

  11. HITESH
    Reply

    Dear Sir,
    I tried to veryhidden. Now want to understand how to protect through password so that without password no body can unhidden the file.

    1. Kurt LeBlanc
      Reply

      Hi Hitash,

      To prevent structural changes to a workbook, like adding/removing sheets, you need to protect the workbook. It will give you the option of encrypting it with a password:)

      I hope that helps!
      Kurt LeBlanc

  12. bee
    Reply

    Hi Jeff,
    Can it be done on multiple (selected) sheets simultaneously?
    Appreciate your advice.

    1. Kurt LeBlanc
      Reply

      Yes, but you must have at least one sheet visible. A sample line with an index number as the sheet is:

      Worksheets(1).Visible = xlSheetVeryHidden

  13. Maria
    Reply

    Is there a function that allows for a tab to be hidden with an IF statement? ….I’m looking to make an interactive worksheet— if F3 is YES then Tab 3 will stay visible but if F3 is NO then it will hide Tab 3…Thanks!

    1. Kurt LeBlanc
      Reply

      Hey Maria,

      Excel doesn’t have a function that hides a sheet, but you can create your own macro to do this:) You can have an IF THEN statement that does this for you.

      Let me know if you need anymore help with this and I’ll be happy to assist:)
      Kurt LeBlanc

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.