Hide and Unhide Excel Worksheets from a Workbook

Hi folks, we hope you are learning a great deal from our series on workbook and worksheet management. If you missed out on any information visit our archives as we have already discussed viewing workbooks and worksheets, making them adept for multitasking. We thought you might be a little bored with all the learning and no play so this post is about to hide and seek.

In some situations, you may want to hide one or more worksheets. Hiding a worksheet may be useful if you want to make the worksheet invisible for some reason. When a worksheet is hidden, its sheet tab is also hidden. An important thing to remember here is that you can’t hide all the worksheets. At least one worksheet must be visible in a workbook.

Hide and unhide a single worksheet from the workbook

In this workbook (below image), there are five worksheets: Revenues worksheet, Sales worksheet, Accounts worksheet, and two blank worksheets Sheet4 and Sheet5.

A workbook with five worksheets

Read More: [Excel VBA Challenge] When you input data into a row in one worksheet, How to hide the same rows in other worksheets?

You can hide a worksheet in two ways. Say I want to hide the Sales worksheet. The first way is right-click on the Sales worksheet tab, a shortcut menu appears. In the list, you will find two options: Hide and Unhide. Unhide option is dim right now as there is no worksheet hidden in this workbook. Select the “hide” command from the list.

Hide an Excel worksheet

Hiding a worksheet from the workbook

The Sales worksheet is hidden. To unhide the hidden worksheet, right-click on any worksheet tab, choose Unhide from the list, Unhide dialog box appears.

Unhide a worksheet

Choose Sales and click OK to unhide the Sales worksheet.

Unhide worksheet from workbook Image 2

Remember one thing here, Sales was in between Revenues and Accounts worksheets. When I unhide the Sales worksheet, it appears exactly in the same location it was before hiding.

In another way, you can use the Hide Sheet command in the Home Ribbon.

Read More: How to use cell references with multiple worksheets in Excel

Hiding more than one worksheet

This time I want to hide three worksheets at a time. I select Sales worksheet tab, press and hold the CTRL key on my keyboard, and select tabs Accounts and Sheet4.

Then I click on the Format drop-down in the Home ribbon, then Hide & Unhide control, and finally click on the Hide Sheet command.

Hiding more than one worksheet

Sales, Accounts and Sheet4 worksheets are hidden from the tab list.

Worksheets are hidden

To unhide again the worksheets, I click again on the Format drop-down in the Home ribbon, then Hide & Unhide control, and then click on the Unhide sheet command.

Unhide worksheets at a time

Unhide dialog box appears. Though you can hide more than one worksheet in one go, you can’t unhide all the hidden worksheets in one click. You will have to unhide one by one.

Not all the worksheets can be hidden

You can’t hide all the worksheets from the list. Let’s try to hide all the worksheets in this workbook. I select all the worksheets, and right-click on the worksheet tab, and select the Hide option from the list.

Microsoft Excel dialog box comes with a warning message: “A workbook must contain at least one visible worksheet”. I click OK, the dialog box disappears.

You cannot hide all the worksheets of a workbook

So that’s it for today folks! Keep excelling, do post us your questions or feedback in the comments.

Read More…

Creating a pivot table in a worksheet connecting to another worksheet in the same workbook

Top Excel Security Tips: Features for securing worksheets and workbooks

Viewing and Scrolling Multiple Excel Workbooks at the Same Time



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply