3 Easy Ways to UNHIDE ALL SHEETS in Excel (with & without VBA)

In this video, I will show you how to unhide worksheets in Excel (with or without VBA).

In Excel, you can hide multiple worksheets at one go but you can not unhide multiple sheets at one go. If you use the inbuilt functionality to unhide sheets, it will only unhide one sheet at a time.

However, there are a couple of methods you can use to unhide all sheets at one go in Excel.

This video covers the following methods to unhide sheets:
-- Manually Unhide one sheet at a time
-- Unhide all sheets using VBA code in the immediate window
-- Unhide all sheets by using a VBA macro code and adding it to the quick access toolbar
-- Unhide sheets using custom view option.

With VBA immediate window, you can quickly run a line of code by simply adding it in the immediate window and hitting the enter key.

Below is the line of code to use in the immediate window in the VB Editor:

For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

As soon as you hit the enter key (after placing the cursor at the end of the line), it will instantly unhide all the hidden sheets.

The good thing about this is that you don't need to save the Excel workbook as a macro-enabled workbook.

Another method is by saving a VBA macro code to unhide sheets in the personal macro workbook and then adding that code to the Quick Access Toolbar (QAT)

Doing this would allow you to work on any workbook and instantly unhide sheets with a click of a button.

Below is the VBA code to save in the personal macro workbook:

Sub UnhideAllSheets()
For Each Sheet In Sheets
Sheet.Visible = True
Next Sheet
End Sub

The third method to unhide sheets is by using custom views. You can save a custom view when all the sheets are visible and then when you have to unhide all the worksheets, you can go back to that view.

This method to unhide sheets is not suited in all situations but can be useful if you're using custom views anyway.

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[MultiSamantha]

OMG! You are a legend. Do you know I have been on the phone for hours trying to recover the previously saved files because I hadn't realised I have somehow hidden all my sheets and the unhide option was not working. I did not know about this view code option to unhide sheets. Not even Microsoft support told me this! You are a legend, seriously so baffled how Microsoft support didn't even know this was an option. THANK

[opossumlover]

I had a data validation tab I couldn’t un hide using the “right click the bottom” method. I never knew about the VBA but your explanation was so helpful! I was able to unhide the tab I needed to and not go insane lol. Thanks!!

[briandennehy]

Very nice solution to a very annoying problem 👌

[wayneedmondson]

Hi Sumit.. thanks for the video. Love the Immediate Window in the VBE and VB in general.. gives so many ways to solve simple problems on the fly and also complex problems, as well. Thanks for sharing your tips. Thumbs up!

[crismorningstar]

This is AWESOME!!!! Thank you very much!!!!

[FlossinBBQ]

Un-hiding several worksheets are a real pain. Thanks for the tip.

[AnilKumar-ihgk]

Y DNT you start VBA programming sir...ur explaining super

[mustafesalaat]

hello sumit

thanks for your video.

using userform. can add - + for example. sumit to paid 100, sumit have paid 100.

to paid have paid to paid
sumit 100 - 100 0

can do so when using userform?

[sharadgupta]

found many video similar to liela gharani.

[nidhitrivedi]

Hi, I am having runtime error '1004'

[PaulFidika]

WARNING! Check to make sure your filetype is NOT .csv. Excel will let you split your csv file into multiple worksheets, save the file, and close the file, all without warning you that the .csv datatype does NOT support multiple sheets. Upon re-opening your .csv file, you'll be pleasantly surprised to learn that ALL OF YOUR WORKSHEETS EXCEPT FOR ONE OF THEM HAS BEEN PERMANENTLY DELETED. Thanks Microsoft, for wasting hours of my fucking life.

Moral of the story: use google sheets instead.

[tharushafernando]

You can unhide all by press Shift + drag down +press ok