Log in  \/ 
x
or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account
x
x
Register  \/ 
x

or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account

How to unhide all worksheets & sheets in Excel?

This article provides several methods 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


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


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. 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.

doc-unhide-worksheets2

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:

doc-unhide-worksheets3

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:

doc-unhide-worksheets4

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.

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:

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

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.


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

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Related articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    jO · 1 months ago
    Nice but can't scroll using mouse wheels. Very bad UX.
  • To post as a guest, your comment is unpublished.
    Prafull · 3 months ago
    Thanks a Lot for Sharing VB COde
  • To post as a guest, your comment is unpublished.
    Steph · 6 months ago
    This is very helpful. Thanks!

    Is there VBA code I can use to unhide all hidden rows/columns across all tabs at once?
  • To post as a guest, your comment is unpublished.
    aromAz · 8 months ago
    this thread is very helpful. Thanks!
  • To post as a guest, your comment is unpublished.
    Tony G · 10 months ago
    Nice Job!!!!
    I unhide all sheets in one shot!!! cool!!!!!

    Tkz

    Unhide multiple sheets!!!!

    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.