Hiding Worksheet in Excel
MS Excel or Microsoft Excel is powerful spreadsheet software that allows us to record huge amounts of data across multiple worksheets. An Excel worksheet consists of various cells to record data types, such as text, number, formula, etc. Each Excel cell has a unique address and can be referenced on the same worksheet, same workbook, or different.
Generally, when we create or open an Excel workbook, all the respective worksheets are displayed on the sheet tab, which can be accessed from the bottom area of the Excel window. However, there may be a situation when we do not want a specific worksheet to be displayed in the visible area of the Excel window. We can hide the corresponding worksheets using the Hide feature in Excel in such cases.
This article discusses the step-by-step tutorials of various common methods to hide desired worksheets in Excel. The article also discusses the various visible properties present in Excel for worksheets.
Note: The corresponding sheets are not deleted when hiding worksheets in Excel. We only hide the desired sheets from the view area. However, their data can still be referenced on other worksheets or workbooks as normal.
The Excel worksheets are hidden or unhidden by changing the values of the visible property. Excel usually has three possible values for the visible property, such as 'True', 'False', and 'Very Hidden':
How to hide worksheets in Excel?
When performing any task in Excel, we usually get various ways to complete them. Likewise, we can also leverage the Hide option in Excel to hide worksheets within the workbook. The following are the most common methods to hide worksheets in Excel:
All the above methods can help make our Excel worksheets hidden, very hidden, and visible. These methods typically change the visible property for the Excel sheets in one way or another. Let us now discuss each method in detail:
Hiding the Worksheets using the Contextual Menu
In Excel, a contextual menu is commonly called right-click menu that contains some options depending on what is selected when using the right-click button via the mouse. To hide a worksheet in Excel using the contextual menu, we must perform the following steps:
Similarly, we can unhide a worksheet by clicking on the 'Unhide' option from the contextual menu. Clicking on Unhide button displays the list of hidden sheets, and we can select the desired sheet from the Unhide dialog box and then click on OK.
How to hide multiple worksheets in Excel?
When hiding multiple worksheets in Excel, we only need to select all those sheets before using any of the methods discussed above. Since Excel cannot hide all the sheets, we must leave at least one sheet unhidden (or unselected).
To select multiple worksheets in Excel, we can follow any one of the methods listed below:
Once the desired worksheets are selected, we need to hide them using methods, such as choosing the Hide option from the contextual menu, ribbon, keyboard shortcut, or VBA.
Hiding the Worksheets using the Ribbon
Another easy method of hiding an Excel sheet involves using the Ribbon tools/ shortcuts. In Excel, the Ribbon contains all the built-in commands and their shortcuts at the top position in the Excel window. We can access the Hide option from the ribbon and hide the desired worksheet by following the steps below:
Similarly, we can unhide a worksheet by clicking on Home > Format > Hide & Unhide > Unhide Sheet.
Hiding the Worksheets using VBA
Generally, Excel VBA (Visual Basic for Applications) helps perform various tasks by executing specific code. However, when hiding worksheets in Excel, we can go through the graphical interface of Excel VBA and hide the desired worksheet accordingly. For this, we have to perform the below steps:
Making Worksheets Very Hidden in Excel
To make the desired worksheet very hidden, we must set the visible property as 'xlSheetVery Hidden' from the graphical interface of the VBA editor.
After hiding the worksheet using this method, we cannot unhide the sheet from the Unhide dialog box. In this case, to unhide the worksheet, we must again change the visible property from 'xlSheetVery Hidden' to 'xlSheetVisible' using the VBA editor.
Hiding the Worksheets using the Keyboard Shortcut
Excel has specific shortcut keys for most of the built-in tasks. Using the keyboard shortcuts is the quickest way to access specific Excel features or commands. Unfortunately, there is no definite shortcut for hiding the sheet in Excel. However, the Alt key method works.
The Alt key activates specific keys on the ribbon within the Excel window. We can press the displayed keys and access the respective tool/command/ shortcut accordingly. So, to hide any worksheet in Excel, we have to press the following keys, one at a time, in sequence: Alt, H, O, U, S.
We don't need to remember the specified keys or sequence because Excel displays the respective keys after pressing the Alt key in the active Excel window. The keys in works are as follows:
In this way, we can hide the selected worksheet by pressing the keyboard shortcut keys.
Adding Custom Keyboard Shortcut for Hiding Worksheet in Excel (VBA Codes to Hide Worksheets)
Excel also helps configure any custom keyboard shortcut for any specific task or command. When setting up the custom keyboard shortcut, we can hide worksheets with a single keystroke. In particular, we need to run a simple macro to hide the selected sheets and specify a desired key combination as the shortcut key for executing the corresponding Macro.
We must perform the following step to insert the appropriate Macro and assign specific key combinations:
The above code will look like this in the module window:
In this way, we can configure the custom shortcut key to hide any active worksheet in Excel. Whenever we want to hide the desired worksheets, we need to select the specific sheet(s) and use the assigned shortcut key (which is "Ctrl + Shift + H" in our case).
Note: While specifying the shortcut key for any macro, we must ensure that the custom shortcut key combination is unique. Excel has several predefined shortcut keys. If we assign the custom shortcut keys similar to a predefined key combination, the old action will be replaced by the newly assigned action.
The above code hides sheets from the view area, which can be unhidden from the Unhide dialogue box. To make the selected worksheets very hidden, we must copy-paste the following code by following the same steps.
Using the above code, we change the visible property of the selected worksheets as xlSheetVeryHidden, making our sheet very hidden.
The above codes hide active (or selected) sheets from the view area. When we want to hide all the worksheets except the active worksheet, we can use the following code:
The above code hides sheets from the view area, which can be unhidden from the Unhide dialogue box. We must use the following code in the module window to make all worksheets 'very hidden' except the active worksheet.
Unhide Worksheets using VBA Code
The following code can be executed through the VBA editor to unhide all the hidden worksheets within the workbook:
When we use the above code, Excel goes through each worksheet one by one and makes the ws.Visible property equal to xlSheetVisible. It typically unhides all the sheets.
Tip: To secure our worksheets, we can make them very hidden using VBA and then restrict others to access the VBA window by setting up a strong password under VBAProject Properties.
Limitation of Hiding Worksheet in Excel
While hiding a sheet in an Excel worksheet, we must ensure that at least one sheet is visible at all times. It is not possible to hide all sheets of an Excel workbook. So, if we have a single worksheet in our workbook and try to access the Hide option, Excel deactivates the Hide option. The option is usually grayed out or remains inactive in such cases. This means that there must always be at least one visible sheet in an Excel workbook when hiding the desired sheets.