Make an Excel Worksheet Very Hidden

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!

 

 

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

43 Comments

  1. Shafiullah on May 26, 2014 at 2:10 am

    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 on June 5, 2014 at 11:29 am

    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 on June 15, 2014 at 3:59 am

    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 on June 24, 2014 at 12:09 am

    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.

    • jefflenning on June 24, 2014 at 6:33 am

      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

      • Hartmut on June 24, 2014 at 7:05 am

        Great Thank you

  5. Murad on June 15, 2015 at 6:22 am

    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

    • jefflenning on June 15, 2015 at 8:54 am

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

  6. Ross on August 21, 2015 at 11:22 am

    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

    • jefflenning on September 3, 2015 at 6:06 am

      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

      • Ross on September 21, 2015 at 5:45 am

        Thanks for confirming this Jeff, greatly appreciated.

        • jefflenning on September 24, 2015 at 12:07 pm

          🙂

  7. Diogo on August 25, 2015 at 9:06 am

    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.

    • jefflenning on September 3, 2015 at 8:40 am

      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 on September 4, 2015 at 7:49 am

    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?

    • jefflenning on September 17, 2015 at 10:51 am

      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 on March 2, 2016 at 3:25 am

    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’?

    • jefflenning on March 3, 2016 at 5:57 am

      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

      • Ian Ratcliffe on March 3, 2016 at 7:28 am

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

  10. sridhar on March 31, 2016 at 3:39 am

    Thanks it’s helpful

    • cmmomof4 on July 1, 2016 at 7:07 am

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

      • Kurt LeBlanc on July 1, 2016 at 8:57 am

        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

        • Carol Marston on July 2, 2016 at 6:33 am

          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!!!

          • Kurt LeBlanc on July 5, 2016 at 5:29 am

            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 on May 5, 2016 at 5:54 am

    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.

    • Kurt LeBlanc on August 18, 2016 at 11:27 am

      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 on June 14, 2016 at 7:13 pm

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

    • Kurt LeBlanc on June 30, 2016 at 2:32 pm

      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 on August 18, 2016 at 9:07 am

    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!

    • Kurt LeBlanc on August 18, 2016 at 11:09 am

      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

  14. Jittapas on December 24, 2016 at 1:46 am

    I can do the veryhidden, it is very hedden!
    But when I re view by go to Alt+F11 then Visible- xlSheetVisible. It open without password. If I need password to unhide, how do I do?

    • Jeff Lenning on December 27, 2016 at 11:46 am

      You can accomplish that by applying workbook protection.
      Thanks
      Jeff

  15. Jobin Thomas on June 17, 2017 at 3:28 am

    how to open a file which is protected through very hidden and “work book protection command” ?

    • Jeff Lenning on June 17, 2017 at 4:14 am

      If the workbook has workbook or worksheet protection enabled with a password, you may be able to use a third-party application to bypass the password depending on the encryption level. Although I don’t have a specific recommendation, a quick search should reveal some options.
      Thanks
      Jeff

  16. Jobin Thomas on June 17, 2017 at 4:49 am

    Thanks for the info.

    • Jeff Lenning on June 17, 2017 at 4:56 am

      Welcome 🙂

  17. Mohammed Alsabbry on July 29, 2017 at 2:04 am

    thanks Jeff for the very informative article and comments, i received a workbook from the world bank, the table of content indicates that there are four worksheets for assumption and projection, but they are not on the tab,so I was familiar with hidden property,but they are not hidden. I searched and I found your article,so I have from your article that there are also a very hidden property, I checked it via VBA, the worksheets are not there, do you think is there any way they are hidden in different, or they just delete them so not share their assumptions.

  18. Mohammed Alsabbry on July 29, 2017 at 2:12 am

    I have also another question,when re saved the same file received from the World Bank,with the different name, a message came up indicating that some of the format will be lost, when I clicked okay,i noticed all worksheet of graphs are lost, how can such thing happens.

  19. John G on December 4, 2018 at 12:36 pm

    Dear Jeff,

    Thank you for the article. When I set the worksheet.visible property to xlVeryHidden, it breaks my code that accesses a range that I address as the rowsource property for a listbox in a form. How do I address the range once the sheet is veryhidden?

    With ThisWorkbook.Worksheets(“Plates”)
    myString = ThisWorkbook.Worksheets(“Plates”).Range(.Cells(2, 1), .Cells(lstrow, 3)).Address ‘
    lbxPlate.RowSource = (“Plates!” & myString) ‘
    End With
    lbxPlate.Selected(0) = True

    Thanks,

    John G.

  20. Mark on June 17, 2019 at 11:20 am

    I have four worksheets in my workbook that I wish to hide. All four are running web queries when the file is opened and I am wondering if they will still be able to do that if the sheets are hidden? Unfortunately, I can’t test it because the updates are year-end and I would like to know now if this will work or not.

    Thanks!

  21. Kristen on September 19, 2019 at 10:08 am

    This was such an extremely useful. I have just recently started working with hidden worksheets so clarification was greatly needed!

  22. rich salter on December 25, 2021 at 11:01 pm

    Lots of great blog posts, Jeff. Can I share them with my associates, or are they behind a paywall?

    • Jeff Lenning on December 27, 2021 at 8:11 am

      All of the blog posts are open to the public, and it would be great to share them 🙂

Leave a Comment