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.

Visible Property

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

• True (xlSheetVisible): When the Visible property for a worksheet is set as true, the sheet remains visible in the visible area of an Excel window or sheet tab. Also, the sheet can be selected as usual if desired.
• False (xlSheetHidden): When the Visible property for a worksheet is set as False, the sheet is hidden from the visible area of the Excel window or sheet tab. In this case, the worksheet is hidden from the standard user interface but works as usual. Data and formulas normally work if used as references in other sheets. Also, when we have hidden a worksheet by setting the Visible property to False, it is very easy to unhide the sheet. We typically need to unhide the desired sheet from the Unhide dialog box.
• Very Hidden (xlSheetVeryHidden): The third visible property in Excel is very hidden. In this case, the selected sheets are strictly hidden from the standard user interface and the Unhide dialog box. This is done from VBA and can be combined with password protection, so it is difficult for an unauthorized person to gain access to hidden sheets but easy for the admin.

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:

• Hiding the Worksheets using the Contextual Menu
• Hiding the Worksheets using the Ribbon
• Hiding the Worksheets using VBA
• Hiding the Worksheets using the Keyboard Shortcut

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:

• First, we need to select the specific worksheet from the Sheet tab that we want to hide from the visible area of the Excel window.
  
• Next, we need to press the right-click button on the selected sheet to view the contextual menu and available options.
  
• Lastly, we must select the ‘Hide‘ option from the displayed right-click menu to hide the selected sheet.
  
  The selected worksheet (i.e., Sheet4) is hidden from the Sheet tab in the following image.
  

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:

• When we need to select a single worksheet, we can click on its name from the Sheet tab.
• When selecting multiple contiguous (or adjacent) worksheets, we must first click on the first worksheet, hold down the Shift key on the keyboard and then click on the last worksheet from the Sheet tab. All the worksheets between the first and last clicked sheets will be selected.
• When selecting multiple non-contiguous (non-adjacent) worksheets, we need to click on each sheet name from the Sheet tab while holding the Ctrl
• We can select the ‘Select All Sheets‘ option from the contextual menu to select all worksheets in an Excel workbook. However, this particular method is not helpful when hiding worksheets in Excel.

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:

• First, we must select the desired worksheet from the Sheet tab to hide it from the view area.
• Next, we need to navigate the Home tab on the ribbon. After that, we must click the drop-down icon associated with the ‘Format‘ option from the group named Cells.
  
• In the next window, we need to choose the ‘Hide & Unhide‘ option, as shown below:
  
• Finally, we have to click on the Hide Sheet option, and our selected worksheet will be immediately hidden from the sheet tab or viewable area.
  
  The selected worksheet (i.e., Sheet4) is hidden from the Sheet tab in the following image.
  

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:

• First, we need to press the mouse right-click button on any sheet name from the Sheet tab.
  
• Next, we need to click on the ‘View Code‘ option to go to the VBA editor or VBA window.
  
• In the VBA, we need to click on the worksheet to hide from the left side pane (VBA Editor Project Explorer).
  
• With the sheet selected, we must select the Properties shortcut from the toolbar, as shown below:
  
  Alternately, we can use the keyboard shortcut F4 with the Fn function key to open Properties for the selected worksheet
• In the Properties pane, we must scroll down and locate the ‘Visible‘ option and click the drop-down list next to it.
• Lastly, we must change the visible property from ‘xlSheetVisible’ to ‘xlSheetHidden‘ to make our selected sheet hidden.
  
  We can close the VBA editor and go back to our Excel window after changing the Visible property. Our selected sheet will be instantly hidden from the view area, and it can be restored from the Unhide dialog box.
  

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:

• Pressing the Alt key activates quick shortcut keys in Excel.
• Pressing the H key launches the Home tab on the ribbon.
• Pressing the O key selects the Format option from the category Cells.
• Pressing the U key chooses the Hide & Unhide option from the list under the Format option.
• Pressing the S key chooses the Hide Sheet

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:

• First, we must select any sheet and launch VBA using the shortcut Alt + F11 (with Fn function key) or Alt + T, M, V.
  
• Once the VBA editor is opened, we need to right-click on any objects from the left-side pane. Next, we need to click on Insert > Module to insert a new module within the active workbook.
  
• After inserting the module, we must double-click and paste the following code in the module window.

The above code will look like this in the module window:

• Once we have pasted the above code in the VBA module window, we need to close the window and return to the Excel window. To launch the Macro dialogue box, we need to press the keyboard shortcut Alt + F8 (with the Fn function key).
  
• In the Macro dialog box, we must select the macro name ‘HideSheet’ or any other for which we want to specify the shortcut key. We need to click the Options and assign a shortcut key for the selected Macro.
  

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.