- \n
- How to Unhide Worksheets in Excel?<\/strong><\/a>\n
- \n
- Manual Technique <\/strong><\/li>\n
- Using Custom View<\/strong><\/li>\n<\/ul>\n<\/li>\n
- How to Unhide Sheets with VBA?<\/strong><\/a>\n
- \n
- Unhide All Sheets <\/strong><\/li>\n
- Unhide Worksheets with a Specific Text in the Sheet Name<\/strong><\/li>\n<\/ul>\n<\/li>\n
- Check if a Workbook Contains any Hidden Sheets<\/strong><\/a><\/li>\n
- Cannot unhide sheets – problems and solutions<\/strong><\/a><\/li>\n<\/ol>\n
1. How to Unhide Worksheets in Excel?<\/strong><\/h2>\n
1.1 Manual Technique <\/strong><\/p>\n
Step 1: <\/strong>Right-click on any available worksheet and select Unhide.<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-1.png\")
<\/p>\nStep 2: <\/strong>From Unhide <\/strong>dialogue box select the sheet you want to unhide and click OK <\/strong>to unhide an Excel sheet.<\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-2.png\")
<\/p>\n1.2 By Creating a Custom View<\/strong><\/p>\n
To create a Custom View<\/strong>, all the worksheets need to be visible in the workbook at first then follow the steps below.<\/p>\n
Step 1: <\/strong>Go to View>> Custom Views.<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-3.png\")
<\/p>\nStep 2: <\/strong>Click on Add.<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-4.png\")
<\/p>\nStep 3: <\/strong>In Add View<\/strong> dialogue box type a name for your custom view inside the name box and make sure both the boxes are ticked. Click OK.<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-5.png\")
<\/p>\nStep 4: <\/strong>Now you can hide as many worksheets as you wish. To unhide them click on Custom Views <\/strong>and then from the dialogue box select the custom view that you created and click on Show<\/strong>. All the hidden sheets will be visible again.<\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-6.png\")
<\/p>\n2. How to Unhide Sheets with VBA?<\/strong><\/h2>\n
2.1 Unhide All Sheets <\/strong><\/p>\n
Step 1: <\/strong>Right click on any of the available worksheet name and select View Code.<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-7.png\")
<\/p>\nStep 2: <\/strong>Insert this below given code inside VBA Script Box <\/strong>and click on RUN <\/strong>button. All the Excel<\/p>\n
sheets will be visible again.<\/p>\n
Sub Unhide_All_Sheets()<\/strong><\/p>\n
\u00a0 Dim wks As Worksheet<\/strong><\/p>\n
\u00a0<\/strong>For Each wks In ActiveWorkbook.Worksheets<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 wks.Visible = xlSheetVisible<\/strong><\/p>\n
\u00a0 Next wks<\/strong><\/p>\n
End Sub<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-8.png\")
<\/p>\n2.2 Unhide Worksheets with a Specific Text in the Sheet Name<\/strong><\/p>\n
Step 1: <\/strong>Enable the VBA Script Box<\/strong> by repeating Step 1<\/strong> of the previous method. Now insert the following code inside the VBA Script Box<\/strong>. In my worksheet, I have a sheet named Sales Report.<\/strong> So, inside the formula, I can use Sales<\/strong> or Report<\/strong> as the specific text that I need to unhide the sheet. The text Sales <\/strong>have been used in the picture below.<\/p>\n
Sub Unhide_Sheets_Containing()<\/strong><\/p>\n
Dim ws As Worksheet<\/strong>\u00a0\u00a0<\/strong><\/p>\n
\u00a0\u00a0\u00a0 For Each ws In ActiveWorkbook.Worksheets<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If InStr(ws.Name, “Sales”) > 0 Then<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ws.Visible = xlSheetVisible<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/strong><\/p>\n
\u00a0 \u00a0\u00a0Next ws<\/strong><\/p>\n
\u00a0<\/strong>End Sub<\/strong><\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-9.png\")
<\/p>\nStep 2: <\/strong>Click on RUN<\/strong> button and the Sales Report<\/strong> worksheet will be visible in the workbook.<\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-10.png\")
<\/p>\n3. Check if a Workbook Contains any Hidden Sheets<\/strong><\/h2>\n
To check if a workbook contains any hidden sheets simply right-click on any of the visible worksheets. If the Unhide<\/strong> option is Disabled<\/strong> then you know there are no hidden worksheets in this workbook. If Enabled<\/strong> then click on Unhide<\/strong> option to see the list of hidden sheets.<\/p>\n
![\"Unhide](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Unhide-Sheet-11.png\")
<\/p>\n4. Cannot Unhide Sheets – Problems and Solutions<\/strong><\/h2>\n
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.<\/p>\n
- \n
- If the Unhide option is disabled or grayed out then there are no hidden worksheets in this workbook.<\/li>\n
- 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.<\/li>\n<\/ul>\n
Sub UnhideVeryHiddenSheets()<\/strong><\/p>\n
\u00a0 Dim wks As Worksheet<\/strong><\/p>\n
\u00a0 For Each wks In Worksheets<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible<\/strong><\/p>\n
\u00a0 Next<\/strong><\/p>\n
End Sub<\/strong><\/p>\n
\u00a0<\/strong>The unhide option also doesn\u2019t 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<\/strong> and enter the password to unlock it. If there is Protect Sheet <\/strong>option instead of Unprotect Sheet<\/strong> then there are no locked sheets in this workbook and the reason might be something else.<\/p>\n
Application of Unhide Sheet in Excel<\/strong><\/h2>\n
- \n
- Data Analysis<\/strong>:\n
- \n
- Unhide hidden worksheets to access data for analysis, ensuring that no valuable information remains obscured.<\/li>\n<\/ul>\n<\/li>\n
- Report Generation<\/strong>:\n
- \n
- Reveal hidden report templates or data sources to generate reports, simplifying the reporting process and maintaining consistency.<\/li>\n<\/ul>\n<\/li>\n
- Data Validation and Auditing<\/strong>:\n
- \n
- Unhide sheets containing data validation rules or audit trails to review and validate data accuracy and compliance.<\/li>\n<\/ul>\n<\/li>\n
- Collaboration<\/strong>:\n
- \n
- Share workbooks with colleagues and unhide sheets to allow collaboration on specific sections or tasks, enhancing teamwork and productivity.<\/li>\n<\/ul>\n<\/li>\n
- Archiving and History Tracking<\/strong>:\n
- \n
- Archive old or historical data in hidden sheets, providing a clean and organized workbook while preserving historical records.<\/li>\n<\/ul>\n<\/li>\n
- Complex Models and Dashboards<\/strong>:\n
- \n
- Unhide hidden sheets with data sources or calculations to support complex models, dashboards, or data visualization, ensuring accurate and up-to-date results.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n
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.<\/p>\n
You may be interested:<\/p>\n
- \n
- Financial Dashboards<\/a><\/li>\n
- Sales Dashboards<\/a><\/li>\n
- HR Dashboards<\/a><\/li>\n
- Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
- Sales Dashboards<\/a><\/li>\n
- Financial Dashboards<\/a><\/li>\n
- Unhide hidden sheets with data sources or calculations to support complex models, dashboards, or data visualization, ensuring accurate and up-to-date results.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n
- Data Analysis<\/strong>:\n
- Unhide Worksheets with a Specific Text in the Sheet Name<\/strong><\/li>\n<\/ul>\n<\/li>\n
- Using Custom View<\/strong><\/li>\n<\/ul>\n<\/li>\n
- Manual Technique <\/strong><\/li>\n