Learn how to quickly hide selected worksheets in Excel via the right-click menu and how to hide all sheets except active one with VBA.
Normally, when you open Excel, you can see all sheet tabs at the bottom of your workbook. But what if you don't want all of your worksheets to be there? Say, some sheets contain source data referenced by your formulas and you'd rather not show that data to other users. Fortunately, you can easily hide as many sheets as you like as long as at least one spreadsheet remains visible.
How to hide sheets in Excel by right-clicking
The fastest way to hide sheets in Excel is this:
- Select one or more sheets you want to hide. This tip explains how to select multiple sheets.
- Right-click the selection and choose Hide from the context menu.
Done! The selected sheets are no longer in view.
How to select worksheets in Excel
Here's how you can quickly select multiple or all worksheets in Excel:
- To select a single sheet, click its tab.
- To select multiple contiguous sheets, click the tab of the first sheet, hold down the Shift key, and click the tab of the last sheet.
- To select multiple non-contiguous sheets, hold down the Ctrl key while clicking the sheet tabs individually.
- To select all sheets, right-click any sheet tab, and then click Select All Sheets.
- It is not possible to hide absolutely all sheets in a workbook, at least one sheet should remain in view. Therefore, after you have selected all sheets, hold the Ctrl key and click one of the sheet tabs (any tab except the active one) to unselect that sheet.
- Selecting multiple worksheets groups them together; the word [Group] appears after the file name in the title bar. To ungroup the worksheets, click any unselected sheet. If there is no unselected sheet, right-click any of the selected sheet tabs, and choose Ungroup Sheets from the context menu.
How to hide worksheet using the ribbon
Another way to hide worksheets in Excel is by clicking the Hide Sheet command on the ribbon. Here's how:
- Select the sheet(s) you want to hide.
- On the Home tab, in the Cells group, click Format.
- Under Visibility, point to Hide & Unhide, and click Hide Sheet.
Keyboard shortcut to hide Excel sheets
Although Microsoft Excel provides no keyboard shortcut for hiding sheets, one of the following workarounds can work a treat.
How to hide Excel sheet with a key sequence
Select the sheets to be hidden and press the following keys one by one, not all at once: Alt, H, O, U, S
The best thing is that you don't actually have to memorize these keys. Once you press Alt, Excel will show you which key activates which menu:
- H selects the Home
- O opens the Format
- U selects Hide and Unhide.
- S selects Hide Sheet.
Hide sheets with a custom keyboard shortcut
If you want to be able to hide sheets with a single keystroke, use the following simple macro to hide selected sheets, and then assign a key combination of your choosing to execute the macro.
You insert the macro in your Excel in the usual way (the detailed instructions can be found here). After that, carry out the following steps to assign the desired keyboard shortcut to the macro:
- Go to the Developer tab > Code group, and click Macros.
- Under Macro name, select the HideSheet macro, and click the Options button.
- In the Macro Options window, type a letter in the small box next to Ctrl+. If you type a lowercase letter, it will be CTRL + your key. If you capitalize the letter, then it will be CTRL + SHIFT + your key.
For example, you may choose to hide sheets with this shortcut: Ctrl + Shift + H
How to hide all worksheets but active sheet with VBA
In some situations, you may need to hide all worksheets except one. If your Excel file contains a reasonable number of sheets, it's no big deal to hide them manually using one of the methods described above. If you are bored with routines, you can automate the process with this macro:
To add the macro to your Excel, perform these steps:
- Select the worksheet you don't want to hide (that will be your active sheet).
- Press Alt + F11 to open the Visual Basic Editor.
- On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
- Paste the above code in the Code window.
- Press F5 to run the macro.
That's it! All the worksheets except for the active (current) sheet are hidden at once.
How to hide the workbook window
Aside from hiding specific worksheets, Excel also enables you to hide the entire workbook window. For this, you go to the View tab > Window group, and click the Hide button.
As soon as you do that, the workbook window and all sheet tabs will disappear. To get your workbook back, go to the View tab again, and click Unhide.
As you see, it's very easy to hide worksheets in Excel. And it's almost as easy to unhide sheets. If you want to make it more difficult for other people to view or edit some important data or formulas, then make your worksheet very hidden. Our next tutorial will teach you how. Please stay tuned!