How to hide sheets in Excel

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:

  1. Select one or more sheets you want to hide. This tip explains how to select multiple sheets.
  2. Right-click the selection and choose Hide from the context menu.

Hide sheets in Excel via the right-click 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.
Tips:

  1. 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.
  2. 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:

  1. Select the sheet(s) you want to hide.
  2. On the Home tab, in the Cells group, click Format.
  3. Under Visibility, point to Hide & Unhide, and click Hide Sheet.

Hide worksheets by clicking the Hide Sheet command on the ribbon.

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.

Hiding Excel sheets with a key sequence

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.

Sub HideSheet()
    On Error GoTo ErrorHandler

    ActiveWindow.SelectedSheets.Visible = False
    Exit Sub

    ErrorHandler:
        MsgBox Error, vbOKOnly, "Unable to Hide Worksheet"
End Sub

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:

  1. Go to the Developer tab > Code group, and click Macros.
  2. Under Macro name, select the HideSheet macro, and click the Options button.
  3. 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
Assigning a shortcut to the Hide Sheet macro

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:

Sub HideAllSheetsExceptActive()
    Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets
        If wks.Name <> ThisWorkbook.ActiveSheet.Name Then
            wks.Visible = xlSheetHidden
        End If
    Next wks
End Sub

To add the macro to your Excel, perform these steps:

  1. Select the worksheet you don't want to hide (that will be your active sheet).
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the above code in the Module window.
  5. 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.
Hiding the workbook window in Excel

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.
Unhiding Excel's workbook window

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!

You may also be interested in:

One Response to "How to hide sheets in Excel"

  1. JayTee says:

    how do i hide a worksheet based on a cell value (yes\no)? Similar process to hiding a row based on a cell value.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28