How to Unhide Excel Sheets and How to Hide Sheets in Excel?

One of the most common issues among Excel users is finding a reference to a sheet that doesn’t seem to exist. Although you may quickly assume that the reference or formula is wrong, the solution could be as simple as unhiding the tab or sheet that is hidden.

In this article, you’ll discover the three methods to effectively unhide Excel sheets. First, you’ll learn how to unhide Excel sheets manually. Secondly, how to unhide all sheets at once using VBA. And third, how to unhide all sheets using Custom View. Finally, you’ll learn a quick way to find hidden sheets and how to hide them again using a password.

How to unhide Excel sheets?

If your worksheet contains only a few hidden sheets, then the quickest way is to unhide them by right-clicking on the tabs in Excel. Let’s see how this can be done.

1. 1. Right-click on any tab and then select “Unhide…” from the drop-down menu.

1. 2. Select the sheet to unhide and click “OK”.

Now that you know how to unhide a worksheet in Excel manually, let’s see quicker methods to achieve this in case you have various hidden worksheets.

How to unhide all sheets in Excel?

Depending on the size of data contained in Excel, it is common practice to hide the sheets that may be less important for instant viewing. However, unhiding each by right-clicking on each tab can become a little tedious. Let’s see the two ways to unhide all sheets at once in Excel using VBA and Custom View.

How to unhide sheets in Excel using VBA?

The VBA (Visual Basic for Applications) functionality lets you quickly hide unhide sheets. This is how to unhide sheets in Excel using VBA.

1. 1. Right-click on any tab and select “View Code” from the pop-up menu to launch the Visual Basic Editor.

1. 2. Go to View > Immediate Window.

1. 3. Copy the following line of code and paste into the “Immediate” window.

“for each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet”

1. 4. Then, press “Enter”.

1. 5. Go back to your Excel workbook, and you’ll see that all sheets are now displayed.

Now, let’s see how you can quickly unhide all sheets using an alternative method.

How to unhide sheets in Excel using Custom View?

Like VBA, the “Custom View” functionality in Excel allows you to hide and unhide all sheets at once in Excel. However, to use this method to unhide sheets, you will first need to create a view with all sheets unhidden to be able to activate the unhidden view. Moreover, this functionality won’t work if your data is in table format.

How to create a Custom View for unhidden sheets in Excel?

1. 1. Go to View > Custom Views.

1. 2. Click on “Add…” to access the “Add view” dialog box.

1. 3. Name this view, and click “OK” to save.

Now you’ve created the Custom View where all sheets are displayed, let’s see how to access it to quickly unhide all sheets in Excel.

How to activate a Custom View to unhide sheets in Excel?

You can activate a Custom View anytime you want to unhide all sheets in Excel. Follow these steps to activate your Custom View.

1. 1. Repeat the first step from the previous section.
2. 2. Select the view you created earlier and click “Show”.

And that’s it. All your sheets should now be visible.

How to find hidden sheets in Excel?

There are some sheets in Excel that are very hidden and harder to find, especially if the workbook contains a significant number of tabs. The best option is to use the VBA method listed above. Follow steps 1-4 from the section How to unhide sheets in Excel using VBA from this article.

Now that you’ve seen the three ways to unhide sheets in Excel, let’s see how you can quickly hide sheets in Excel using a password.

How to hide sheets in Excel with a password?

To hide a sheet in Excel with a password, you will need to protect the sheet. Follow these steps to protect an Excel Sheet with a password.

1. 1. Right-click on the sheet you would like to hide with a password and select “Protect Sheet” from the pop-up menu.

1. 2. In the “Password to unprotect sheet” field, create your password. Tick any of the option boxes below to customize user restrictions. Once finished, click “OK”.

1. 3. Excel prompts you to enter the password again to confirm. Click “OK”.

1. 4. Right-click on any tab and you will see that the “Unhide” option is disabled.

Doing this will not only require other users to enter the password to hide/unhide sheets, but it will also restrict them from making any changes to the cells contained in that sheet.

Conclusion

Learning how to hide and unhide sheets in Excel using VBA or Custom View is a great way to save time and boost productivity, no matter the size of your data.

In this article, you have learned three ways to unhide Excel sheets, which can also be helpful in case you want to hide them again later. You have seen how to unhide them individually and in bulk using VBA or Custom View. You have also learned to search for very hidden sheets using a single line of code and how to hide them with a password.

If you would like to explore further ways to collaborate in Excel without losing control of your data, check out the following articles:

• How to Use Excel Sheet View for Easy Collaboration?
• How to Password Protect an Excel File?
• How to Remove a Password from Excel? (Multiple Methods)
• How to Share an Excel File?