[3 Proven Ways] How to View and Unhide Excel Hidden Sheets Easily?

Why Excel Sheets Get Hidden?

Sometimes, Excel automatically hides the worksheets, or the user intentionally hides them to keep the data confidential or make the file cleaner. It is better to know why are the sheets hidden in Excel and how they conceal themselves before knowing how to reveal them.

• Accidental Hiding: When handling several sheets, it is easily possible to accidentally right-click a sheet tab and select the option to hide.
• Protecting Sensitive Data: Sheets containing confidential information, calculations, or references are usually hidden by the user who is afraid of accidentally editing or deleting them.
• Simplifying Presentation: Only the relevant sheets are displayed in the shared report to ensure that the workbook is clean and easy to use.
• Automation by Macros: There are macros or VBA scripts that automatically hide identified sheets when some operations, such as data validation or automation tasks, are done.
• File Protection Settings: Workbook protection settings may be applied by administrators in corporate settings to either make some sheets invisible due to compliance or workflow purposes.

Types of Hidden Sheets

• Normal Hidden Sheets: They can be easily uncovered with the help of the standard Excel commands, such as right-click or the ribbon menu.
• Very Hidden Sheets: These are deeper-level hidden sheets that are invisible to VBA. You cannot see or unhide them with the user interface. They will usually include backup information or macro-associated formulae.

Being able to know the difference assists you in the choice of making Excel show hidden sheets.

How to View Hidden Sheets in Excel Manually?

If you find that some Excel sheets are no longer there, then you need not worry, as they are probably hidden. With the built-in methods in Excel, it is easy to view and hide hidden sheets. We may consider two easy methods of doing it without any technical devices.

1. Right-Click to Find Hidden Sheets in Excel

This will allow you to browse all the sheets that are hidden at once with the sheet tab menu. It is a fast and straightforward method of revealing any worksheet within your Excel file.

• Open the Excel workbook and right-click on any visible sheet tab at the bottom.

• Click on "Unhide" from the context menu, and a window will pop up showing all hidden sheets.

• Select the sheet you want to view and click “OK” to show hidden sheets in Excel.

2. Use Ribbon Menu to See Hidden Sheets in Excel

This method allows you to see hidden sheets via the Excel manual interface. It is useful when you want to use menu options to navigate through the hidden sheets.

• Go to the “Home” tab on the top Ribbon and in the “Cells” group, click on “Format”.

• Under “Visibility,” select “Hide & Unhide” and then click “Unhide Sheet”.

• A dialog box will display all hidden sheets. Choose the sheet you want and click “OK," and that's how to find hidden sheet in Excel.

How to See Hidden Sheets in Excel via VBA Method?

VBA or Visual Basic for Applications can be very powerful when the manual approach fails, particularly when the sheets are very hidden. You can easily unhide, count, or inspect all the hidden sheets in your Excel workbook using simple macros.

How to use Macros to view hidden sheets?

First, you need to open the VBA editor and follow these steps to learn how to use a macro:

• Press “Alt + F11” to open the “VBA Editor” and click “Insert” then “Module” to add a new module.

• Copy and paste the desired VBA code based on your goal.

• Press "Alt + F8” and then select the macro and click “Run” to execute the code for how to recover a hidden Excel sheet.

1. How to Show All Hidden Sheets in Excel?

Use this method if you want to unhide every hidden sheet in one click.

• Paste this code in your new module and run the macro:

  “Sub Unhide_All_Sheets()

  Dim wks As Worksheet

  For Each wks In ActiveWorkbook.Worksheets

  wks.Visible = xlSheetVisible

  Next wks

  End Sub”

• All hidden sheets in Excel, including “very hidden” ones, will appear instantly.

2. How to Recover Hidden Excel Sheets and Show Their Count?

This version not only unhides sheets but also tells you how many were hidden.

• Paste the VBA code below and run your Macro in the VBA editor:

  “Sub Unhide_All_Sheets_Count()

  Dim wks As Worksheet

  Dim count As Integer

  count = 0

  For Each wks In ActiveWorkbook.Worksheets

  If wks.Visible <> xlSheetVisible

  Then wks.Visible = xlSheetVisible

  count = count + 1

  End If

  Next wks

  If count > 0 Then

  MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets"

  Else

  MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets"

  End If

  End Sub”

• Excel will automatically display all previously Excel hidden sheets, and a pop-up will show how many were unhidden.

3. How to Check Hidden Sheets in Excel that You Select?

If you want to see which specific sheets are hidden before unhiding them:

• Paste and run the code below to check hidden sheets:

  “Sub Unhide_Selected_Sheets()

  Dim wks As Worksheet

  Dim MsgResult As VbMsgBoxResult

  For Each wks In ActiveWorkbook.Worksheets

  If wks.Visible = xlSheetHidden Then

  MsgResult = MsgBox("Unhide sheet " & wks.Name & "?", vbYesNo, "Unhiding worksheets")

  If MsgResult = vbYes Thenwks.Visible = xlSheetVisible

  End If

  Next

  End Sub”

• You’ll get a pop-up list of all Excel hidden sheets, and you can then decide which to unhide manually.

How to Unhide Very Hidden Sheets in Excel via Custom Views?

The Custom Views feature offered by Excel is another way of displaying hidden or filtered data. With the custom view, you can also add in the hidden rows, columns, and filters, and, therefore, it is easy to regain the entire view of all the worksheets.

• Open your Excel workbook and go to the “View” tab on the top ribbon.

• Click “Custom Views” and click “Add," then enter a new name (e.g., "Full View").

• Check the box that says, “Include Hidden Rows, Columns, and Filter Settings” and click “OK” to save.

• Now, choose the view you just created and click “Show”.

This will override hidden settings applied via filters or view modes and help you reveal all visible data, including hidden sheets. However, before these steps, you must know how to check hidden Sheets in Excel. It helps save time by only using this method if there are hidden sheets.

How to Recover Unsaved Excel Sheets?

In case you shut your workbook without saving or lose Excel files in the middle of the editing process, 4DDiG Windows Data Recovery will come to the rescue. It is a strong application that is used to restore deleted, unsaved, or corrupted Excel files within a short time.

Key Features of 4DDiG Data Recovery:

• Recovers Excel, unsaved Word, PowerPoint, and over 2,000 file types.
• Works on HDDs, SSDs, USB drives, and memory cards.
• Restores files lost due to crashes, formatting, or accidental deletion.
• Offers file preview before recovery.

Steps to use 4DDiG Windows Data Recovery:

• Download 4DDiG Windows Data Recovery software on your PC and install it. Launch after installing it and click "Hard Drive" on the left menu, then select the drive that you want to scan.
• 4DDiG automatically scans the drive and gives you a lot of filter and document type options to shortlist the results. You can find your files under the "Unsaved Document" tree. Click on it then the "Recover" button to get back your lost files.
• Click on any recoverable file to check its content preview, and if you want to recover it, click "Recover" in the preview window.
• Now, you only need to specify the location for saving the files and click “Save Now” to securely recover the once lost Excel file back on your storage.

FAQs About Excel Hidden Sheets

This may occur when the sheet is marked as very hidden with the help of VBA or when the workbook is secured. To unhide it, you will have to either use VBA or unprotect it.

Click on any sheet that is visible and right-click, and see whether the Unhide option is visible. In case it is greyed out, then there are no sheets that are normally hidden. A VBA code can also be used to check hidden sheets.

Yes. In case the workbook is not secured with a password, you can unhide normal sheets using the Ribbon or through right-click.

By the interface, it is easy to view and unhide “hidden” sheets. “Very hidden" sheets are those that are hidden through VBA, i.e., are not visible until a code is run to reveal them.

Conclusion

Excel sheets can be hidden for arranging documents cleanly or for security. This guide has discussed how to view and unhide Excel hidden sheets through manual, VBA, and custom view ways, whether they are hidden by accident or intentionally.

If you have lost some valuable Excel files or even forgotten to save them, Tenorshare 4DDiG is a fast and effective approach to restoring them.