Don’t forget to share this post

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.
How To Unhide Excel Sheets How To Hide Right click tab
How To Unhide Excel Sheets (& How To Hide) - Right-click tab
  1. 2. Select the sheet to unhide and click “OK”.
How To Unhide Excel Sheets How To Hide Click OK
How To Unhide Excel Sheets (& How To Hide) - 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 Share an Excel File for Multiple Users?

There are different ways to share an Excel file. Here's how to share an Excel file with multiple users for easy collaboration

READ MORE
How to Share an Excel File for Multiple Users

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.
How To Unhide Excel Sheets How To Hide View Code
How To Unhide Excel Sheets (& How To Hide) - View Code
  1. 2. Go to View > Immediate Window.
How To Unhide Excel Sheets How To Hide Immediate Window
How To Unhide Excel Sheets (& How To Hide) - 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”.
How To Unhide Excel Sheets How To Hide Enter code
How To Unhide Excel Sheets (& How To Hide) - Enter code
  1. 5. Go back to your Excel workbook, and you’ll see that all sheets are now displayed.
How To Unhide Excel Sheets How To Hide All sheets unhidden
How To Unhide Excel Sheets (& How To Hide) - All sheets unhidden

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.
How To Unhide Excel Sheets How To Hide Custom Views
How To Unhide Excel Sheets (& How To Hide) - Custom Views
  1. 2. Click on “Add…” to access the “Add view” dialog box.
How To Unhide Excel Sheets How To Hide Add
How To Unhide Excel Sheets (& How To Hide) - Add…
  1. 3. Name this view, and click “OK” to save.
How To Unhide Excel Sheets How To Hide Name view and save
How To Unhide Excel Sheets (& How To Hide) - Name view and 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”.
How To Unhide Excel Sheets How To Hide Show Custom View
How To Unhide Excel Sheets (& How To Hide) - Show Custom View

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 Password Protect an Excel File
How to Password-Protect an Excel File?

If you work with important data in Excel, you probably want an extra layer of protection. Here's how to password protect an Excel file

READ MORE

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.
How To Unhide Excel Sheets How To Hide Protect Sheet
How To Unhide Excel Sheets (& How To Hide) - Protect Sheet
  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”.
How To Unhide Excel Sheets How To Hide Create password
How To Unhide Excel Sheets (& How To Hide) - Create password
  1. 3. Excel prompts you to enter the password again to confirm. Click “OK”.
How To Unhide Excel Sheets How To Hide Confirm Password
How To Unhide Excel Sheets (& How To Hide) - Confirm Password
  1. 4. Right-click on any tab and you will see that the “Unhide” option is disabled.
How To Unhide Excel Sheets How To Hide Unhide is disabled
How To Unhide Excel Sheets (& How To Hide) - Unhide 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.

How to hide Google Sheets data with Layer?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published May 24 2022, Updated Sep 23 2022