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 little bored with all the learning and no play so this post is about 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 reasons. 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.

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

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

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, but you can’t unhide all the hidden worksheets in one click. You will have to unhide one by one.

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

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 worksheet tab, and select 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 feedbacks in the comments.

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.