3 Methods to Unhide All Sheets in Excel (& how to hide)

Good news! If you have Office 365 you can now unhide multiple sheets at once! YAY!

How can you un-hide all Excel sheets at once? Hiding many worksheets at once is easy in Excel. Click on a sheet, hold down the control or the shift key, click on the other sheets you'd like to hide, right-mouse click and select hide.

Now what if you wanted to unhide all the sheets in one go? If you right-mouse click on any sheet name and select unhide - a new window opens with a list of hidden sheets. Unfortunately you can't use the control key or the shift key to select multiple sheets to unhide. You have to unhide tabs one by one.

Find out 3 methods you can use to un-hide:
1. Use Custom views to quickly unhide all sheets - no Excel VBA is needed here
2. Use the Immediate Window in VBA - type in or copy and paste a very simple macro
3. Save the macro in your Excel personal macro Workbook. You can add a button for the macro to your Quick Access Toolbar so it is available for ALL your Excel workbooks - also the ones that are not macro-enabled (just xlsx).

LINKS to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[azstrong]

You are like awesome! I work for the largest healthcare company in the world and loved this feature for Excel. I chose #3 as my method of opening up all sheets, thank you so much!

[sachinrv]

This is good one. Mostly people are wary of using macros; which include me as well. Therefore the one I personally like is custom view; because once the same is defined; Excel takes care of the unhide task by itself. All these years I am habitual using short cut key ALT+O+H+H for hide and ALT+O+H+H+U for unhide. The flip side is you need to logically by-heart the key stokes which can be practiced. Thanks Leila for upload

[LeilaGharani]

Good news! If you have Office 365 you can now unhide multiple sheets at once! YAY!

[wayneedmondson]

Hi Leila.. thanks for the video on un-hiding sheets. That process is always a chore to do one by one. I wrote a small Sub and put it on my QAT with the UP arrow icon to signify to un-hide all worksheets with a click. I never considered doing it on the fly using the Immediate Window.. what a great tip. In that regard, another issue is when I get a workbook from a client with different zoom levels on the worksheets and also with the cell pointer in different locations on each sheet. Before getting started, I like to set all sheet zooms to 100 and move the pointer to cell A1 on each sheet and then start on Sheet1 as the active sheet. In the spirit of your un-hide lesson, I came up with the following to use in the Immediate Window: For Each ws in Worksheets: ws.Activate: ActiveWindow.Zoom = 100: Range("A1").Select: Next ws: Sheets(1).Select. It even works on hidden sheets. While I could put this in a Sub and on the QAT, you showed that it is quick to open the VBE and IW and then type in what you want to do on the fly. Thanks for that tip and the insight to put it to use in other ways. Thumbs up!

[rathinagirisubbiah]

Very nice to see multiple possibilities for a single problem.

[Oppadu]

Very nice tutorial and clear explanation! I suppose 2nd option, using immediate window would be potentially the simpliest way =)

[kgomotsomamunyani]

Life saviour. I have a model due tonight and I somehow hid all sheets but could not make them visible. The VBA method worked. Thank you!!!!

[OrvilleCota]

I used the macros method for a long time and it was very useful. Unfortunately I was facing problems like random crashing of all opened Excels when opening multiple workbooks. I realised it once I reinstalled MS Office. Don't know exactly what caused it though. I'm forced not to use it now. Nevertheless I am grateful to @Laila for sharing her ideas with us. Very inspiring.

[MalinaC]

I love the simplicity od custom views! Thanks Leila for hide-unhide fun!

[bethhunter]

I like the first option to proactively provide a way to unhide the sheets. However, I'm too deep in this workbook to start over; so, the 3rd option - creating a macro - genius! And your step by step tutorial was easy for me to follow! Thank you!

[bayarahmed]

Its amazing how everything is so simple with you ! Many thanks !!!

[mh_hamzah]

I just found out that there is a visual basic editor feature in exel ... it's really very helpful, because there are dozens of hidden sheets, thank you very much for the knowledge..
Thank you very much from Indonesia 😄🇮🇩🇮🇩🇮🇩

[runejensengru]

VBA is the way I do it. Hiding/unhiding all very hidden sheets. Password protected as well. Great work Leila :-)

[walidhammouda]

I bought your 5 Excel courses at once on Udemy.com and I highly recommand them to anyone who wants to improve their skills with Excel/ VBA without getting bored with online courses because your enthousiam is contagious ;)

[krn]

What an interesting video. Love the immediate window idea. Always wondered how to send <enter>. Makes sense though... I use this syntax in a module like this also... For example...
Dim rng as Range:set rng = sheet1.range("A1:A10")

[Toolesun]

Always enjoy Leila's videos and this one in particular is really useful. I like the macro solution because I don't need to ALT F11 and go to notepad each time - very helpful, so thank you!

[johnvanstultj]

Third option opens a world of possibilities for me, thank you very much.

[darrengodkin]

I like the tip/technique for adding a macro button to the quick toolbar section.

Very handy - thanks

[parasapaboritokongapo]

Thank you! I used the 2nd option and then BOOM! Tadahhhh! Fixed <3

[usemybox]

An excellent video from Leila! The first method is so simple to use. Thanks Leila