How to unhide all worksheets & sheets in Excel?

This article provides ways to unhide all hidden sheets & worksheets in Excel step by step.

Unhide all hidden worksheets one by one in Excel

Unhide all hidden worksheets by VBA code

Unhide all hidden worksheets by Kutools for Excel with one click

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Unhide all hidden worksheets one by one in Excel

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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:

doc-unhide-worksheets1

2. And all of the hidden worksheets have been list in the Unhide dialog box, then select the worksheet you want to show.

doc-unhide-worksheets2

3. And then click OK, the selected hidden sheet will be display. If you want to show other sheets, please repeat the above steps.

Method 2. Show all hidden worksheets with right click.

1.Put the cursor on one of the worksheets which in the same workbook with the hidden worksheet and right click. See screenshot:

doc-unhide-worksheets3

2. And all of the hidden worksheets have been list in the Unhide dialog box, then select the worksheet you want to show. See screenshot:

doc-unhide-worksheets4

3. And then click OK, the selected hidden sheet will be displayed.

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.


arrow blue right bubble Unhide all hidden worksheets by VBA code

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.


arrow blue right bubble Unhide all hidden worksheets by Kutools for Excel with one click

Kutools for Excel’s Unhide All Sheets could help you display all of the hidden sheets quickly and easily.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30days. Get it Now.

After installing Kutools for Excel, apply Unhide All Sheets according to these steps:

1. Please apply the utility by clicking Kutools > Show / Hide > Unhide All Sheets, see screenshot:

doc-unhide-worksheets5

2. And all of the hidden sheets have been shown at once. See screenshots:

doc-unhide-worksheets6
-1
doc-unhide-worksheets7

Note: The number displaying after the function indicates the number of hidden worksheets in your workbook. If there is no hidden worksheet, the function will turns gray and cannot be clicked.

Tip: The Unhide All Sheets of Kutools for Excel is quite handy. Unhide All Sheets feature description.

Related articles:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh