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.
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:
To unhide a sheet, simply right-click any sheet’s tab and select Unhide. This reveals the Unhide dialog box as shown below.
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.
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:
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.
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!
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?
Our training programs start at $29 and will help you learn Excel quickly.
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”.
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
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.
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.
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
Great Thank you
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
Murad,
Yes, that is true. You can use the Workbook Protection command to prevent a user from unhiding hidden worksheets 🙂
Thanks
Jeff
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
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
Thanks for confirming this Jeff, greatly appreciated.
🙂
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.
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
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?
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
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’?
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
That’s very helpful, and most kind of you to reply. Thank you.
Thanks it’s helpful
I would love to know if I can make a sheet with a macro very hidden at all times
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
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!!!
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
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.
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
Hi Jeff,
Can it be done on multiple (selected) sheets simultaneously?
Appreciate your advice.
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
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!
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
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?
You can accomplish that by applying workbook protection.
Thanks
Jeff
how to open a file which is protected through very hidden and “work book protection command” ?
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
Thanks for the info.
Welcome 🙂
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.
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.
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.
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!
This was such an extremely useful. I have just recently started working with hidden worksheets so clarification was greatly needed!
Lots of great blog posts, Jeff. Can I share them with my associates, or are they behind a paywall?
All of the blog posts are open to the public, and it would be great to share them 🙂