Hide and Unhide Multiple Excel Worksheets

fotostorm_iStock_Peeking

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.
  • Excel 2003 and earlier: Choose Format, Sheet, Hide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Hide from the shortcut menu.

Figure 1: Right-clicking on a worksheet tab provides a faster means for hiding worksheets.

You can also hide multiple sheets at once. To do so, you'll first group the sheets. In any version of Excel, hold down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. Or you can hide several contiguous sheets:

  • Click on the first worksheet tab and then hold down the Shift key as you click on the last worksheet tab within the group that you wish to hide.
  • Hide the sheets as discussed above.

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:

  • Excel 2007 and later: On the Home tab, you can choose Format, Hide and Unhide, Unhide Sheet, and then unhide a single sheet. You must repeat this action for each worksheet that you wish to unhide. You can save a couple of steps by right-clicking on a visible worksheet tab and choose Unhide.
  • Excel 2003 and earlier: From any worksheet tab, choose Format, Sheet, and then Unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Unhide from the shortcut menu. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.

Figure 2: Excel's Unhide Sheet command only allows you to unhide one worksheet at a time.

Click "next" to read the second page of the article where I discuss Excel's Custom Views feature.

Fortunately, the tedium of unhiding worksheets one at a time can be eliminated by use of Excel's Custom Views feature. Think of Custom Views as snapshots of workbook settings – such as the hidden or visible status of individual worksheets – that you can toggle at will. To use this feature, make sure that all worksheets are visible and then then carry out these steps:

In all versions of Excel:

  • Choose Custom Views on the View tab or menu.
  • Click Add, and then type a name for your custom view, such as All Sheets, and then click OK.
  • Next, hide any worksheets as needed and then create a second view titled Presentation View, or a name of your choosing.

Figure 3: Create a baseline view that shows all worksheets before you hide any worksheets.

Going forward, you can toggle the view by selecting the Custom Views command and then double-clicking the view of your choice, or click once on the view and then click Show as indicated in Figure 4. 

This article only scratches the surface of what's possible with Custom Views. I'll explore this feature further in future articles.

Figure 4: A custom view allows you to unhide multiple worksheets with just a couple of mouse clicks.

See all articles by David Ringstrom.

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

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
to 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
to DaddyGovernment Knows Best
Jun 26th 2015 01:11

Fantastic macro....thank you so much

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

Had a dozen sheet hidden - vmt for your macro!

Thanks (0)
avatar
By Matt
to 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
to DaddyGovernment Knows Best
Jun 26th 2015 01:11

A tip of the hat to you, sir.

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

You're the man!

Thanks (0)
avatar
By KS
to 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
to 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
to DaddyGovernment Knows Best
Jun 26th 2015 01:11

AWESOME! 2-step process.

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

David totally owned you Benjamin.

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

Apply cold water to area of burn.

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

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

Thanks (0)
avatar
By DexterMorgan
to 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
to 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 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
to Rowan Webb
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
to worldstarhiphop
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
to worldstarhiphop
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 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
to samrey
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 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 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)