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.

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

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 star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1200 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

Comments  

Permalink 0 Luis
awesome! saved me 15 mins of worthless unhiding of sheets, done in 1 min...appreciat e it!
2013-12-23 19:39 Reply Reply with quote Quote
Permalink -1 Harinder
This was great. Saved me atleast 30 mins from clicking the same buttons again n again
2014-05-13 23:12 Reply Reply with quote Quote
Permalink -1 nancy
thank you so much - this was a huge time saver for me. :D
2015-06-17 14:30 Reply Reply with quote Quote
Permalink -6 Diane
Downloaded free to try and it would not unhide all the worksheets at the same time, still had to do it 1 by 1. Would love it if it worked.
2014-01-07 20:34 Reply Reply with quote Quote
Permalink -5 Diane
I downloaded so that I could try all features before I purchased. Tried to unhide all sheets in a workbook but that option is greyed out. Consequently, I had to unhide them one by one.
2014-01-07 20:45 Reply Reply with quote Quote
Permalink +1 Admin_jay
Quoting Diane:
I downloaded so that I could try all features before I purchased. Tried to unhide all sheets in a workbook but that option is greyed out. Consequently, I had to unhide them one by one.


Please get in touch with me. Or please make sure you have downloaded and installed the latest version from this link: http://www.extendoffice.com/downloads/KutoolsforExcelSetup.exe

Contact me:Jaychivo#ext endoffice.com, replace # with @.

:-)
2014-01-08 02:57 Reply Reply with quote Quote
Permalink -5 Tom
Quoting Diane:
I downloaded so that I could try all features before I purchased. Tried to unhide all sheets in a workbook but that option is greyed out. Consequently, I had to unhide them one by one.
It works fine. Get your s### together Diane! With lots of Love, Tom
2014-01-17 19:37 Reply Reply with quote Quote
Permalink 0 Victor
Excelent, saved me more than enough time to post this "thank you post".
2014-01-15 17:02 Reply Reply with quote Quote
Permalink 0 Troy
Quoting Victor:
Excelent, saved me more than enough time to post this "thank you post".


Ditto...saved me more than enough time to post a 'Thank you'!

Troy
2014-10-30 22:45 Reply Reply with quote Quote
Permalink -5 Tom
Worked like a charm!

Love always,
Tom
2014-01-17 19:25 Reply Reply with quote Quote
Permalink 0 Adam
Excellent! Macro worked perfectly. It really doesn't make sense that MS did not build this in.
2014-01-22 00:47 Reply Reply with quote Quote
Permalink -1 Troy
Quoting Adam:
Excellent! Macro worked perfectly. It really doesn't make sense that MS did not build this in.


Yeah, no kidding. So simple. Oh well, at least it can be added to the default so it will work in all workbooks. :0)
2014-10-30 22:47 Reply Reply with quote Quote
Permalink +1 ravi shanakar
Thnaks. helped a lot
2014-01-25 15:50 Reply Reply with quote Quote
Permalink -1 Dena
THANK YOU!!! The macro worked like a charm!
2014-04-04 18:32 Reply Reply with quote Quote
Permalink +2 mtariqraheem
Thanks..... this code saved my 30 mins.
2014-04-11 07:10 Reply Reply with quote Quote
Permalink +2 Panicos
Thanks. The macro worked and saved me a lot of time
2014-05-01 09:33 Reply Reply with quote Quote
Permalink 0 latha
Thanks you so much !!! this code is awesome!!!
2014-06-30 05:29 Reply Reply with quote Quote
Permalink -1 Markus
Thanks for this! Great!
2014-07-07 10:04 Reply Reply with quote Quote
Permalink 0 Rose
The marco option worked perfectly! Thank You
2014-09-04 08:10 Reply Reply with quote Quote
Permalink 0 JACK
COOl! thanks for sharing very helpful.. meme square
2014-09-14 11:36 Reply Reply with quote Quote
Permalink 0 Ola
Thanks! Using the macro saved me alot of time! Unreal they dont have a built in solution for this.
2014-10-08 09:30 Reply Reply with quote Quote
Permalink 0 Troy
Quoting Ola:
Thanks! Using the macro saved me alot of time! Unreal they dont have a built in solution for this.

It's a Microsoft product...I'm sure they will steal, err, I mean borrow the idea and claim it as their own soon enough. :0)
2014-10-30 22:48 Reply Reply with quote Quote
Permalink 0 NK
Thanks a lot. Saved me a lot of time under pressure :-)
2014-11-18 08:46 Reply Reply with quote Quote
Permalink 0 krupa
I used the Unhide all hidden worksheets by VBA code. Pretty simple for basic user like me
Thanks
2015-03-30 12:49 Reply Reply with quote Quote
Permalink 0 Sunny
Great Job I used that VBA option to unhide all the sheets in just one click and it works thank you so much for your valuable advice.
2015-04-09 04:52 Reply Reply with quote Quote
Permalink -1 Eve
I followed the following, however, it still did not open the hidden 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.

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


--------------------------------------------------------------------------------
2015-04-27 13:27 Reply Reply with quote Quote
Permalink 0 Derick
Make sure the 12345678 are not being copied. I didn't think I was copying them but I was getting an error because they were showing up after I ran the code. I went and deleted them (after they showed up) and re-ran the code. You will get a warning that the previous operation will be undone (or something). Just hit ok, and it should work.
2016-05-26 18:29 Reply Reply with quote Quote
Permalink 0 gater
This week was the first I've encountered needing to unhide many, many, many worksheets and it's frustrating that Excel doesn't have what seems to be an easy option for that! Anyway, searched for an answer and your link was in the top five....worked like a charm and I am NOT macro literate by any means! THANK YOU!
2015-04-28 14:13 Reply Reply with quote Quote
Permalink 0 Kamal
Thank you so much...VBA command worked, saved so much of my time! :)
2015-09-18 02:19 Reply Reply with quote Quote
Permalink 0 Neil
The VBA saved my A$s
2015-09-21 21:05 Reply Reply with quote Quote
Permalink 0 Jamie
Whenever I click any function in kutools tab the entire workbook just closes - please help!
2015-09-25 13:37 Reply Reply with quote Quote
Permalink 0 Sparky
This was great...Took seconds to copy and paste the code and nano-seconds to unhide 30 sheets..

Thanks
2016-02-08 19:55 Reply Reply with quote Quote
Permalink 0 Harun
Awesome! I was looking; "how to unhide all sheets in excel at once" and I've learned to getting things done the right way. Thank you so much.
2016-06-21 19:02 Reply Reply with quote Quote
Permalink 0 Rudolph
Is there a faster way than just pasting that code ?
2016-10-01 07:32 Reply Reply with quote Quote
Permalink 0 Tony G
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.
2016-10-14 23:15 Reply Reply with quote Quote
Permalink 0 aromAz
this thread is very helpful. Thanks!
2016-11-26 12:01 Reply Reply with quote Quote
Permalink 0 Steph
This is very helpful. Thanks!

Is there VBA code I can use to unhide all hidden rows/columns across all tabs at once?
2017-02-08 19:59 Reply Reply with quote Quote

Add comment


Security code
Refresh