Using Excel for offices or projects often means having numerous sheets in your workbook. If they are not properly managed, this could quickly become chaotic and stressful.
Suppose you need to work on Sheet 55 and Sheet 66, all of the sheets in between would be a stumbling block. What if you could hide unwanted sheets until you needed them?
In this article, we will walk you through on how you can hide or unhide sheets on your excel files.
Hide Excel Sheet
Check out these 6 different methods to hide sheets in your workbook.
Using Keyboard Shortcut
Excel has hands down 200 plus keyboard shortcuts to perform tasks in your workbook. So, why not use the keyboard shortcut to your advantage?
Using shortcuts for basic tasks like Hiding Excel Sheets will help you increase your work efficiency. Additionally, this method is best if you want to hide only one sheet.
If you’re new to Excel shortcuts, do remember that shortcuts that begin with Alt is a ribbon shortcuts. So, you do not have to press the keys at once like the other shortcuts. The best thing about these shortcuts is once you enter the Alt key, you’ll see the other shortcut letters in the Ribbon itself. Thus, you wouldn’t have to mug up the shortcuts.
At the time of writing this article, these Ribbon shortcuts are only available in Windows.
Firstly, open Excel workbooks and head to the Sheet you want to hide. Then, use the given keyboard shortcut.
Keyboard Shortcut: Alt, H, O, U, S
From Excel Ribbon
If you’d rather prefer the Excel Ribbon to manually hide Sheet, this method is for you. You can find the default Hide Sheet Tab in the Format menu of the Home Tab.
- On your workbook, click the Sheet you wish to hide. For multiple sheets, press down the Ctrl key and select other Sheets.
- Go to Home Tab. From the cells group, click Format.
- Below Visibility, select Hide & Unhide > Hide Sheet.
From Navigation Pane
Another way to hide your Excel Sheets is from the Navigation Menu. I know many of you might be wondering why go through the Navigation option when you could hide from Sheet Tab or Ribbon.
Well, this is because when you have an extremely large number of sheets, you need to keep scrolling to locate the specific sheet. But, with the Navigation menu, you can see all Sheet lists in one view.
On your worksheet, go to the View Tab. From the Show group, click Navigation. In the right Navigation panel, you can see lists of Sheets of your current workbook. Right-click on the Sheet and pick Hide. The hidden sheet will be immediately greyed out.
From Sheet Tab
When you right-click on the Sheet tab, you can find a Hide option in the flyout menu. I personally use the Sheet Tab to hide multiple sheets, adjacent sheets, or non-adjacent sheets quickly.
To hide one sheet, you can just right-click on the Sheet name and pick Hide.
But, for multiple sheets, follow these steps.
- Firstly, select your Sheet Tab with one of these methods.
- Select Multiple Adjacent Sheets: If you wish to hide the adjacent sheets, select the First Sheet. While pressing down the Shift key, select the Last Sheet. When you do this, Excel will select all the Sheets from start to end.
- Select Multiple Non-Adjacent Sheet: If you need to select multiple non-adjacent sheet, we will use the Ctrl key instead. Select the First sheet. Press down the Ctrl key and select the other Sheet you wish to hide.
- Select All Sheets: To select all sheet tabs, right-click on one of the Sheets. Then, choose Select All Sheets from the fly-out menu.
- Now, right-click on one of the selected Sheet name and choose Hide.
Run VBA Code
If you need to hide sheets more frequently, it’s better you create a macro and run VBA code. After creating the Macros, you could just run Macros whenever required. So, no more repeating the steps to hide sheets.
Here, we have created a VBA code to hide a single worksheet from your workbook. You can simply copy and paste the code. But, note that you should specify your Sheet Name and the total length of the Sheet Name that you wish to hide in the code.
- Right-click on the Sheet Tab and choose View Code.
- Microsoft Visual Basic for Applications window will appear on the screen. Copy the VBA code mentioned in the box and paste them into your Module.
Sub HideExcelSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, “Total length of Sheet Name”) = "Your Sheet Name" Then ws.Visible = False End If Next ws End Sub
- Now, on “Total length of Sheet Name”, enter the total number of characters of your Sheet Name. Then, on “Your Sheet Name,” type in the Sheet name. For example, we’ve entered “Number” as our Sheet name and 6 as the total characters in the name.
- To run the code, press F5 key. If prompted, select HideExcelSheet in the Macro window and click Run.
In Excel’s Developer tab, you can find the Properties menu to view and edit your worksheet properties. So, you can also hide the Sheet from the Properties menu.
But, be careful when you choose the VeryHidden menu for your sheet. Pick this option only if your Sheet contains confidential and important information.
Before you begin, check Developer Tab in your Excel Ribbon and Add the tab if needed.
- Open Excel Workbook and go to the Sheet you wish to hide.
- Click Developer Tab. From the Controls section, select Properties.
- On the Properties window, go to the Visible menu at the bottom. Expand the drop-down menu and choose one of the following options.
- 0 – xlSheetHidden: Pick this to Hide Sheet and Unhide them later from the Unhide Window.
- 2 – xlSheetVeryHidden: Choose this to Very Hide sheet. VeryHide Sheet won’t appear in the Unhide Window.
Unhide Excel Sheets
We have four different methods to unhide Sheets in Excel. You could unhide all the sheets (except for the VeryHidden Sheets) you’ve hidden using any one of the above methods. Follow whichever you find the most convenient.
Using Keyboard Shortcuts
First, we have the keyboard shortcut to quickly unhide the Excel Sheets. As mentioned earlier, this is a ribbon shortcut. So, just enter the keys individually.
- Firstly, open Excel Workbook with hidden sheets.
- Enter the given shortcut.
Keyboard Shortcut: Alt, H, O, U, H
- On Unhide window, select the Hidden Sheet. To Unhide multiple sheets, press down Ctrl key and select the Numerous sheets.
- Click OK.
From Sheet Tab
Apart from shortcut keys, another easiest way to unhide sheets is from the Sheet Tab.
- Hover over the Sheet Tab at the bottom.
- Right-click on any one of the available Sheets > Unhide.
- On Unhide window, select the Sheet names you wish to unhide.
- Click OK.
From Excel Ribbon
If you find it difficult to work with shortcuts, let’s unhide the Excel Sheets from the Ribbon. Check out these steps.
- On your Excel Sheet, go to Home Tab and click Format in the cells group.
- Under Visibility, select Hide & Unhide > Unhide Sheet.
- On Unhide window, select the Hidden sheets you wish to unhide. To select multiple sheets, use the Ctrl key.
- Click OK.
Run VBA Code
You can run the VBA code if you need to unhide the sheets more often. Here, we have created the VBA code to unhide all hidden sheets at once.
- On your sheet, press Alt + F11 keyboard shortcut to open the Visual Basics Editor window.
- On Microsoft Visual Basic for Applications window, go to Insert tab > Module.
- Now, copy the code given in the box and paste them into the Module.
Sub UnhideAllSheets() 'Unhide all worksheets in workbook Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Application.ScreenUpdating = True End Sub
- Press F5 to run code.
How to Hide/Unhide Sheet Tab?
If you also wish to hide the Sheet Tab along with Sheets, you can do it from the Excel Options. When you do this, you can see only the current sheet with no Sheet Tab.
- Open the Excel workbook and click the Sheet you want to display.
- Go to the File tab and click on the Options menu at the bottom.
- On Excel Options, navigate to the Advanced menu from the left panel.
- Scroll to locate the Display options for this workbook. Then, do one of the following:
- To hide Sheet tabs, Untick the option for Show sheet tabs and click OK.
- To display Sheet Tabs, tick the box for Show sheet tabs and click OK.