How to unhide all worksheets & sheets in Excel?
This article provides several methods to unhide all hidden sheets & worksheets in Excel step by step.
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
There are two ways to hide all hidden worksheets one by one.
Method 1. Show all hidden worksheets with Format
1. Click Home > Format > Hide & Unhide > Unhide Sheet. See screenshot:
2. Now all hidden worksheets are listed in the Unhide dialog box. Please select the worksheet you want to show, and then click the OK button.
Then the selected hidden sheet is displayed. If you want to show other sheets, please repeat the above steps.
Method 2. Show all hidden worksheets with right click.
1. In the workbook with hidden worksheets, put the cursor on any one of the worksheets and then right click it, and choose Unhide from the context menu. See screenshot:
2. Then an Unhide dialog box pops up with all hidden sheet names listed inside, please select the worksheet you want to show, and then click the OK button. See screenshot:
Note: Unhide all hidden worksheets is not as simple as it can be while there are hundreds of hidden sheets need to be unhide. You may have to click the mouse for hundreds of times for repeating the operations. If you would like to get an easier way, please make it according to the procedures that the following methods provides.
The following short VBA code also can help you display all of the hidden sheets at the same time.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
3. Press the F5 key to run this macro. And the hidden sheets will be displayed at once.
Kutools for Excel’s Unhide All Sheets could help you display all of the hidden sheets quickly and easily.
1. Please apply the Unhide All Sheets utility by clicking Kutools > Show / Hide > Unhide All Sheets, see screenshot:
Then all hidden sheets are shown at once. See screenshots:
Note: The number displaying after the function indicates the total number of hidden worksheets in your workbook. If there is no hidden worksheet, the function will turns gray and cannot be clicked.
- Hide other sheets
- Hide other workbooks
- Unhide all hidden workbooks
- Show hidden ranges (columns and rows)
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...