Hide and Unhide Multiple Excel Worksheets

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

Share this content

Replies

Please login or register to join the discussion.

avatar
By Joeman
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Jags
Mai Vang
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By SK
DaddyGovernment Knows Best
Jun 26th 2015 01:11

Fantastic macro....thank you so much

Thanks (0)
avatar
By David Ringstrom
bessalisha
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Mike Carr
DaddyGovernment Knows Best
Jun 26th 2015 01:11

Had a dozen sheet hidden - vmt for your macro!

Thanks (0)
avatar
By Matt
DaddyGovernment Knows Best
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Anonymous
DaddyGovernment Knows Best
Jun 26th 2015 01:11

A tip of the hat to you, sir.

Thanks (0)
avatar
By excelent
DaddyGovernment Knows Best
Jun 26th 2015 01:11

You're the man!

Thanks (0)
avatar
By KS
DaddyGovernment Knows Best
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Saleem
DaddyGovernment Knows Best
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By cjgreen
DaddyGovernment Knows Best
Jun 26th 2015 01:11

AWESOME! 2-step process.

Thanks (0)
avatar
By EricO
DaddyGovernment Knows Best
Jun 26th 2015 01:11

Thank you!!

Thanks (0)
avatar
By Benjamin
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
longjie
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Guy Who said u got owned
Jan
Jun 26th 2015 01:11

David totally owned you Benjamin.

Thanks (0)
avatar
By lobe
Jan
Jun 26th 2015 01:11

Apply cold water to area of burn.

Thanks (0)
avatar
By cjgreen
Jan
Jun 26th 2015 01:11

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!

Thanks (0)
avatar
By Nedual
longjie
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By DexterMorgan
Bryce Sanders
Jun 26th 2015 01:11

advertising

Thanks (0)
avatar
By Anonymous
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Anonymous
haile0709
Jun 26th 2015 01:11

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 (0)
avatar
By Mohan
Jun 26th 2015 01:11

thanks It helped me

Thanks (0)
avatar
By Lie
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Ken
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Patirck
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By David Ringstrom
Clinton Lee
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By TLR
Jun 26th 2015 01:11

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 :)

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By KJ
Jun 26th 2015 01:11

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 (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Kimberley
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
By Alex
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Natalia
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By PF
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Kirttoniya
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
By mbmbinu
Oct 21st 2015 18:24

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.

Thanks (0)
avatar
Aug 23rd 2016 06:45

Firstly i would like to say thanks, because your article gives such a good information how to hide or unhide worksheet in a workbook. your article not only in theory its uses in Excel application all command and shows with figurewise all information which helps i think all points me gotted in a good way .
Thank you for sharing a good information. i really appreaciate your knowledge.

Thanks (0)