Hide and Unhide Multiple Excel Worksheets

Share this content

By David Ringstrom, CPA

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.
excelguideFive Frequent Frustrations in Microsoft Excel
Excel has always been the accountants choice, but many agree that it lacks a certain user friendliness. We hope that this guide to making Excel more user friendly will make your life easier. Download it for free today!
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.


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!

Thank you for your cogent feedback. I've asked my editor to revise the first paragraph to make it clear the Custom Views feature appears on page 2 of the article, and to also add a sentence after Figure 2 to make it clear that the article continues on to the next page. I'm glad you were able to make sense of the article to get to the technique itself, and thank you again for your suggestions.

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


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