Unhide Sheet in Excel is your indispensable tool for restoring hidden worksheets within your workbooks, ensuring that no critical data remains concealed. This feature simplifies the process of making hidden sheets visible again, allowing you to effortlessly access and work with your valuable information. With ‘Unhide Sheet in Excel,’ you regain control over your workbook’s structure and content, ensuring that your data management is efficient and transparent. Embrace the power of this function to unlock the hidden potential of your spreadsheets, making every hidden sheet a valuable asset in your data-driven endeavors. Whether you’re revisiting archived data or reorganizing your workbook layout, ‘Unhide Sheet in Excel’ empowers you to manage your data with precision and ease.
This Content Covers:
- How to Unhide Worksheets in Excel?
- Manual Technique
- Using Custom View
- How to Unhide Sheets with VBA?
- Unhide All Sheets
- Unhide Worksheets with a Specific Text in the Sheet Name
- Check if a Workbook Contains any Hidden Sheets
- Cannot unhide sheets – problems and solutions
1. How to Unhide Worksheets in Excel?
1.1 Manual Technique
Step 1: Right-click on any available worksheet and select Unhide.
Step 2: From Unhide dialogue box select the sheet you want to unhide and click OK to unhide an Excel sheet.
1.2 By Creating a Custom View
To create a Custom View, all the worksheets need to be visible in the workbook at first then follow the steps below.
Step 1: Go to View>> Custom Views.
Step 2: Click on Add.
Step 3: In Add View dialogue box type a name for your custom view inside the name box and make sure both the boxes are ticked. Click OK.
Step 4: Now you can hide as many worksheets as you wish. To unhide them click on Custom Views and then from the dialogue box select the custom view that you created and click on Show. All the hidden sheets will be visible again.
2. How to Unhide Sheets with VBA?
2.1 Unhide All Sheets
Step 1: Right click on any of the available worksheet name and select View Code.
Step 2: Insert this below given code inside VBA Script Box and click on RUN button. All the Excel
sheets will be visible again.
Sub Unhide_All_Sheets()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Visible = xlSheetVisible
Next wks
End Sub
2.2 Unhide Worksheets with a Specific Text in the Sheet Name
Step 1: Enable the VBA Script Box by repeating Step 1 of the previous method. Now insert the following code inside the VBA Script Box. In my worksheet, I have a sheet named Sales Report. So, inside the formula, I can use Sales or Report as the specific text that I need to unhide the sheet. The text Sales have been used in the picture below.
Sub Unhide_Sheets_Containing()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Name, “Sales”) > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub
Step 2: Click on RUN button and the Sales Report worksheet will be visible in the workbook.
3. Check if a Workbook Contains any Hidden Sheets
To check if a workbook contains any hidden sheets simply right-click on any of the visible worksheets. If the Unhide option is Disabled then you know there are no hidden worksheets in this workbook. If Enabled then click on Unhide option to see the list of hidden sheets.
4. Cannot Unhide Sheets – Problems and Solutions
If you are not able to Unhide a worksheet or worksheets in a workbook, the points below might help you to detect and solve the issue.
- If the Unhide option is disabled or grayed out then there are no hidden worksheets in this workbook.
- The worksheets that are very hidden with a VBA code also can not be unhidden using the unhide option of Excel. To unhide these sorts of sheets, use the following code inside the VBA box.
Sub UnhideVeryHiddenSheets()
Dim wks As Worksheet
For Each wks In Worksheets
If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
Next
End Sub
The unhide option also doesn’t work if the sheets are locked or protected. You will need a password to open a locked sheet. From the Review, tab click on Unprotect Sheet and enter the password to unlock it. If there is Protect Sheet option instead of Unprotect Sheet then there are no locked sheets in this workbook and the reason might be something else.
Application of Unhide Sheet in Excel
- Data Analysis:
- Unhide hidden worksheets to access data for analysis, ensuring that no valuable information remains obscured.
- Report Generation:
- Reveal hidden report templates or data sources to generate reports, simplifying the reporting process and maintaining consistency.
- Data Validation and Auditing:
- Unhide sheets containing data validation rules or audit trails to review and validate data accuracy and compliance.
- Collaboration:
- Share workbooks with colleagues and unhide sheets to allow collaboration on specific sections or tasks, enhancing teamwork and productivity.
- Archiving and History Tracking:
- Archive old or historical data in hidden sheets, providing a clean and organized workbook while preserving historical records.
- Complex Models and Dashboards:
- Unhide hidden sheets with data sources or calculations to support complex models, dashboards, or data visualization, ensuring accurate and up-to-date results.
Unhiding sheets in Excel is a fundamental feature that facilitates data access, collaboration, and organization within workbooks, making it a valuable tool for various tasks and scenarios.
You may be interested:
