Hide and Unhide Multiple Excel Worksheets

40
It's easy to hide worksheets in Excel, but unhiding multiple worksheets within a given workbook can be a tedious exercise. Users who don't know otherwise are relegated to unhiding worksheets one at a time. On the second page of this article I discuss using Excel's Custom Views feature to hide or unhide multiple sheets at once. There are a couple of different ways to hide worksheets in Excel. For instance, carry out these steps to hide a single worksheet: Excel 2007 and later: As shown in Figure 1, on the Home tab choose Format, Hide and Unhide, and then Hide Sheet. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

Damn MS, 2012 and they still don't have a way to unhide more than 1 sheet at a time. These guys are dumbs beyond anything

When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets is a different story, however. Excel only allows you to unhide one at a time. If you have many worksheets you want to unhide, this can be very tedious.
The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook:
Sub UnhideAllSheets()
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet
End Sub

Fantastic macro....thank you so much

Jags, thank you for enriching my article with your macro. Joe78man, if you view the second page of my article you'll see that the Custom Views feature allows you to hide and unhide multiple sheets without requiring a macro.

Had a dozen sheet hidden - vmt for your macro!

Thanks so much. Had an older macro for an older version and have no clue how to debug. You saved me a ton of time.

A tip of the hat to you, sir.

You're the man!

Thank you Jags! You just saved me hours of time and some painful carpal tunnel. This article didn't explain how to unhide multiple sheets that were hidden before creating a show-all custom view, so since I inherited my workbook with hundreds of tabs already hidden, I was up a creek.

Hi jag thanks for sharing these really useful macro but I need to make it more specific. With the same macro, I want to unhide all the very hidden sheets only but not the hidden ones.

Please help me on this.

AWESOME! 2-step process.

Thank you!!

"In this article I'll explain a technique that allows you to hide and unhide multiple worksheets at once."

Where do you explain how to unhide multiple sheets at once? You don't. This is a misleading article and should be retitled, "Hide and Multiple Excel Worksheets with Ease, and then discover that you have to unhide them 1 by 1"

Your comment leads me to believe that you didn't notice the second page of the article within which I explain Excel's Custom Views feature.

David totally owned you Benjamin.

Apply cold water to area of burn.

It really would help if you can change the wording at the beginning to say that you CAN unhide multiple sheets using the 'custom views' feature - described later in the article. I did like Benjamin and MANY others and read this statement which caused me to STOP reading and count this article as useless:

"Now that your sheets are hidden, making them visible again calls for repetitive action, as every version of Excel requires you to unhide worksheets one at a time."
I'm glad I had time to read through comments to see that there was a 2nd page and the answer to my issue was there.
Thanks for your help and for posting this article!

I am really curious why splitting this into 2 pages is necessary.

advertising

"Now that your sheets are hidden, making them visible again calls for repetitive action, as every version of Excel requires you to unhide worksheets one at a time"
I have over 700 hidden tabs. Microsoft, you suck.

Yes I saw it thanks David. It has been a life saver. I can't believe that Microsoft would not include something so basic.

thanks It helped me

No this is misleading if you are not a memevber of the forum!

I'm using Microsoft Excel App for Macs' - circa 2010/11. Can't find the "Hide or Unhide" commands. Can anyone help please?

I too had to read the comments to see the value in reading the second page. Please consider telling the reader the information they are looking for is on the second page...or change the construction of the article to feature this information on the first page...let's face in the internet world the first page all most of us will fool with....otherwise thanks for the excel-lent information.

I appreciate your thoughts. I'd already amended the article such that the first paragraph includes the sentence "On the second page of this article I discuss using Excel's Custom Views feature to hide or unhide multiple sheets at once.", and then at the bottom there's "Click "next" to read the second page of the article where I discuss Excel's Custom Views feature." I'm not clear what else I can do. :-)

Thank you for this article. However, I must 1/2 side with Benjamin on this one, as it does not tell you how to unhide multiple sheets at once- in my own example the "multiple sheets" may not be the SAME multiple sheets each time I need to unhide.
Having said that, the information is valuable to create the 'Base' views that you discuss here, and from there, the task of rehiding and/or unhiding (still one at a time) is less infuriating :)

My article was based on functionality that is readily available to the average user. In your case the solution would be to use Visual Basic for Applications to create a dialog box that shows you all of the sheets in the workbook and allows you to hide or unhide based on making selections from the list. Creating multiple custom views would probably be easier unless for your purposes the mix of sheets is constantly changing.

Unhide all hidden worksheets by VBA code (this is what I was personally hoping to find in this article, but the custom views could come in handy as well)

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.

Thanks, KJ, another reader had posted a similar version of the VBA code in the comments already. I appreciate your addition to the discourse!

Custom views didn't work for me. But the above macro worked perfectly!! Thanks a lot :)

The catch with Custom Views is the "Unhide" view has to be in place prior to you hiding the worksheets. If your worksheets are already hidden, then the macro can unhide them, and from there you can use Custom Views to hide/unhide your worksheets.

Wooow!! this was so annoying to do, and now this is way easier!! thanks, now i can finish my Powerbank project.

This is wonderful, you've just saved me so much time with this Custom View. Thank you!!

Fabulous! It's one of my favorite features in Excel. Thank you for the feedback!

Many thanks for the Custom Views tips and also many thanks to the people who posted the macro. Very useful to start with.

I'm glad it worked so well for you! Thanks for the feedback.

Useless.. I wanted to how to unhide all the sheets but it says i can unhide only one at a time.. Waste of time

It's also a waste of time to comment on an article that you didn't read closely enough. You either missed the second page of the article that shows the Custom Views technique, or you blew past the comment that a reader shared that offers a macro that will unhide your presently hidden worksheets.

Another way for MS Excel 2013 is to add the command "Unhide Sheet" on the left top quick access tool bar, And then keeping the mouse on the dark little circle repeat the Loop "Left Click,Enter,Left Click, Enter" till all the desires sheets unhide.