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