11 Ways to Hide a Sheet in Microsoft Excel

Do you need to hide a sheet tab in Excel? This post is going to show you all the ways to hide any sheet tab in your workbook.

Excel allows users to create multiple sheets in their workbook solutions. But you might only want the user to see a selection of those sheets.

Excel lets you hide any sheets you don’t need. This is a great way to organize your work and make sure that only the essential information is visible.

This can help to declutter your workbook and focus the user’s attention on the information they need to see and make it easier to navigate your workbook.

There are many ways to hide a sheet from view, so follow this post to find out how to hide your sheets!

Hide a Sheet from the Right Click Menu

This is definitely the most straightforward method to hide a sheet in your workbook.

When you right-click on any sheet tab it will display a list of possible actions for that sheet. This includes the option to hide the sheet.

Follow these steps to hide a sheet.

  1. Right-click on the sheet tab which you want to hide.
  2. Select the Hide option from the menu.

That’s it! Your sheet will be hidden from the sheet tab view.

Your sheet still exists and any formulas that reference that sheet will still continue to work and return the expected results. But you won’t be able to use the trace precedent feature to jump to any references on hidden sheets.

Also, any hyperlinks to the hidden sheet will cease to work while the sheet is hidden. But these features will return to normal when you unhide the sheet.

Hide Multiple Sheets

You don’t need to hide sheets one at a time. You can hide multiple sheets at once.

You first need to group sheets together and that will allow you to hide them all at the same time.

Follow these steps to group adjacent sheets together.

  1. Click on the first sheet which you want to group.
  2. Hold the Shift key.
  3. Click on the last sheet which you want to group.

Follow these steps to group non-adjacent sheets together.

  1. Click on the first sheet which you want to group.
  2. Hold the Ctrl key.
  3. Click on each other sheet that you want to add to the group.

Now you will be able to hide all your grouped sheets. Right-click on any of the grouped sheets and then select the Hide option.

📝 Note: Your workbook must contain at least one visible sheet. You will get a warning stating A workbook must contain at least one visible worksheet if you try to hide all the sheets.

Hide a Sheet from the Home Tab

The command to hide a sheet can also be found in the Home tab.

Follow these steps to hide a sheet from the Home tab.

  1. Select the sheet tab you want to hide.
  2. Go to the Home tab.
  1. Click on the Format option in the Cells section.
  2. Select the Hide & Unhide option.
  3. Select the Hide Sheet option in the submenu.

This will hide the selected sheet!

Hide a Sheet with a Keyboard Shortcut

You can use the Alt hotkey shortcuts to access the Hide Sheet command from the Home tab.

When you press the Alt key you will see the hotkey prompts in the ribbon appear. These will show you what keys need to be pressed to navigate the commands in the ribbon.

Press Alt, H, O, U, then S in sequence on your keyboard to hide the active sheet.

Hide a Sheet from the Navigation Pane

The Navigation pane lists all the sheets in your Excel workbook and allows you to navigate to them with a single click.

If you right-click on any of the sheets listed in the Navigation pane, you will find a few options including the ability to hide the sheet.

Follow these steps to use the Navigation pane to hide a sheet.

  1. Go to the View tab.
  2. Click on the Navigation command in the Show section.

This will open up the Navigation pane on the right side of the workbook.

  1. Right-click on the sheet which you want to hide.
  2. Select the Hide option from the menu.

This will hide the sheet in the workbook.

📝 Note: The sheet will still be listed in the Navigation pane but it will appear grayed out. Clicking on the sheet name in the Navigation pane will no longer navigate you to the sheet.

Hide a Sheet from the Properties Menu

Sheets can also be hidden from the Properties menu found in the Developer tab.

💡 Tip: Check out this post to find out how to enable the Developer tab in your ribbon. The Developer tab is hidden by default.

Follow these steps to hide a sheet with the Properties menu.

  1. Select the sheet you want to hide.
  2. Go to the Developer tab.
  3. Click on the Properties command in the Controls section.

This will open the Properties menu for the selected sheet. You will be able to see the sheet Name listed in the various properties.

  1. Select xlSheetHidden from the Visible options.

This will hide the sheet!

Hide a Sheet with Very Hidden

There is a very interesting option in the Properties menu that allows you to hide a sheet in such a way so that a user won’t be able to unhide it easily.

The xlSheetVeryHidden option in the Visible property hides the sheet but it also hides the options to unhide that sheet. This option can only be changed from the VBA editor once enabled.

This makes it less likely a user will unhide a sheet you don’t want them to see.

Hide a Sheet from the VBA Editor

The Properties menu can also be accessed in the Visual Basic Editor (VBE).

Go to the Developer tab and select the Visual Basic command to open the VBE. You can also press the Alt + F11 keyboard shortcut to open the editor.

Follow these steps to hide a sheet from the Properties window in the VBA Editor.

  1. Go to the View tab.
  2. Select the Properties Window option from the menu.

💡 Tip: You can also open the Properties window using the F4 keyboard shortcut.

This will open the Properties window in the editor and you should see it docked just below the Project window.

  1. Select the sheet from the Microsoft Excel Objects folder in the Project explorer.
  2. Set the Visible property to xlSheetHidden in the Properties window.

Your sheet will now be hidden in the Excel application.

Hide Multiple Sheets with VBA

You have seen how to hide a sheet from the VBA editor, but you can actually use some VBA code to automate the process of hiding your sheets.

Suppose you need to hide multiple sheets in your workbook based on criteria such as the sheet name starting with a given text string. It could be a pain manually hide them all.

You can use some VBA code to save the effort!

Go to the View tab in the VBA editor and select the Module option to create a new module for your code.

Sub HideSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 4) = "2021" Then
        ws.Visible = False
    End If
Next ws
End Sub

Paste the above code into the module in the editor.

This code will loop through all the sheets and check if the sheet name starts with 2021. If it does, then it will hide the sheet.

Hide Multiple Sheets with Office Scripts

Another way to automate hiding your sheets is with Office Scripts.

Office Scripts is a JavaScript based language available for Microsoft 365 business plans in Excel on the web.

You can create a similar code that will hide all the sheets starting with a given text value.

Go to the Automate tab in Excel online and press the New Script command to open the Code Editor.

function main(workbook: ExcelScript.Workbook) {
    let sheets = workbook.getWorksheets();

    for (let sheet of sheets) {
        if (sheet.getName().startsWith('2021')) {
            sheet.setVisibility(ExcelScript.SheetVisibility.hidden)
        };
    };
}

Paste the above code into the Script Editor and press the Save script button. You can then press the Run button to execute the script.

It will loop through all the sheets in the workbook and hide any sheets with a name that starts with 2021!

Hide All Sheet Tabs from the Excel Options Menu

There is an option that allows you to hide all the sheet tabs. This doesn’t hide the sheets, they will all still be visible and useable. It will only hide the tabs that are used to navigate between sheets.

Follow these steps to hide all the sheet tabs with the Excel Options menu.

  1. Open the Excel Options menu.
  2. Go to the Advanced options.
  3. Uncheck the Show sheet tabs option.
  4. Press the OK button.

This will hide all the tabs in the Excel app, but you will still be able to navigate to different sheets using other methods.

Conclusions

Hiding sheets in Excel can allow users to focus on the information they need to see and prevent them from being overwhelmed.

Hiding sheets is easy and there are several ways to accomplish this such as from the right-click menu, the Home tab, or with a keyboard shortcut.

There are several other less obvious ways to hide sheets in the Developer tab and VBA editor. These will even allow you to create very hidden sheets that are harder for users to discover and unhide.

If you need to hide a lot of sheets or you will be hiding the same sheets repeatedly you can automate the process with VBA and Office Scripts.

Do you use hidden sheets in your workbooks? Do you know any other tips for hiding your sheets? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃