The best way to Unhide Sheets in Excel (All In One Go)

Watch Video – The best way to Unhide All Sheets In Excel

In case you like studying a tutorial over watching a video, beneath is an in depth written tutorial on unhiding sheets in Excel.

While you work with knowledge that’s unfold throughout a number of worksheets in Excel, you could need to cover just a few worksheets. This may very well be to keep away from the muddle or to not present some knowledge to your consumer/supervisor by hiding some worksheets and solely preserving the helpful ones seen.

And in some instances, you might have a workbook that has some hidden sheets and also you need to unhide some or all of those worksheets.

On this tutorial, I’ll present you some strategies to unhide worksheets in Excel (manually in addition to routinely utilizing VBA). I will even present you methods to selectively unhide worksheets primarily based on the title or a situation.

So let’s get began!

Unhiding Sheets Manually

For those who solely have just a few worksheets which are hidden, you possibly can manually unhide some or all of those worksheets.

Suppose you could have an Excel workbook that has 10 worksheets which are hidden.

Beneath are the steps to manually unhide worksheets (one by one):

Proper-click on any of the present worksheet tabClick on on the Unhide choice. It will open the Unhide dialog field that lists all of the hidden worksheetsWithin the Unhide dialog field, click on on the worksheet that you just to unhide (you possibly can solely choose one by one).Click on OK.

The above steps would unhide the choose worksheet.

Observe: Sadly, there isn’t any in-built performance in Excel to shortly unhide all of the hidden worksheets (or a method to choose a couple of worksheet and unhide it). As of now, it’s essential use the unhide dialog field the place you possibly can solely choose one worksheet to unhide.

You may cover worksheets in bulk, however not unhide in bulk

If you wish to cover worksheets, you possibly can choose a number of worksheets without delay (maintain the management key and click on on the worksheet tab title), right-click and click on on the Conceal choice.

Sadly, there isn’t any in-built performance in Excel to shortly unhide all of the hidden worksheets (or a method to choose a couple of worksheet and unhide it). As of now, it’s essential use the unhide dialog field the place you possibly can solely choose one worksheet to unhide.

Whereas there’s no-inbuilt performance to unhide in bulk, you possibly can simply do that with a easy VBA macro code.

Unhide All Sheets At One Go

With VBA, you possibly can simply unhide worksheets in bulk.

For instance, when you have 10 hidden worksheets, you possibly can create a easy VBA code to unhide all of the worksheets or you possibly can unhide primarily based on a situation (reminiscent of unhide solely these the place there’s a particular prefix or yr within the title).

Observe: The strategies coated on this tutorial doesn’t require you to save lots of an Excel workbook in a macro-enabled format (.XLSM) to make use of the VBA code.

Utilizing Fast Window

VB Editor in Excel has an instantaneous window the place you possibly can sort a line of code and immediately execute it straight away.

Beneath are the steps to make use of this above line of code to unhide sheets by way of fast window:

Proper-click on any of the seen sheets within the workbookClick on View code. It will open the VB Editor.Click on the View choice within the menu after which click on on the Fast window. It will make the Fast window seem within the VB Editor (if not there already).Within the Fast window, copy and paste the next line of code: For every Sheet in Thisworkbook.Sheets: Sheet.Seen=True: Subsequent SheetPlace the cursor on the finish of the roadHit the Enter key

That’s it!

The above steps would immediately unhide all of the sheets within the workbook.

As soon as accomplished, you possibly can shut the VB Editor.

The perfect half about that is that you are able to do this on any workbook. You don’t want to fret about saving the workbook in a macro-enabled format. Simply execute a line of code and immediately unhide all of the sheets within the workbook.

Let me additionally shortly clarify the beneath VBA code that now we have used within the fast window to unhide sheets:

For every Sheet in Thisworkbook.Sheets: Sheet.Seen=True: Subsequent Sheet

The above code makes use of a For Subsequent VBA loop to undergo all of the sheets within the workbook and set the seen property to TRUE. As soon as the seen property of all of the sheets is modified, the code will finish.

The colon (:) used within the code above is equal to a line break. Whereas it seems like a single line of code, it has three elements to it that are separated by two colons.

By Including Macro to QAT (with One Click on)

In case you need to unhide worksheets very often, one other great way may very well be to have the macro code to unhide sheets within the Private macro workbook and save the icon within the Fast Entry Toolbar.

That is only a one time course of and after getting it accomplished, you possibly can then unhide sheets in any workbook by merely clicking on a button within the QAT.

That is by far probably the most environment friendly method to unhide sheets in Excel (most helpful whenever you get plenty of workbooks with hidden sheets and you need to unhide these).

The trick right here is to save lots of the code to unhide sheets within the Private Macro Workbook.

A Private Macro Workbook is one thing that’s at all times open whenever you open any Excel file (you possibly can’t see it although). While you save a macro code to the Private Macro workbook, this code is now at all times obtainable to you. And whenever you add this to the QAT and also you run the macro code with a single click on.

Beneath is the code that it’s essential add to the Private Macro Workbook:

Sub UnhideAllSheets()
For Every Sheet In Sheets
Sheet.Seen = True
Subsequent Sheet
Finish Sub

Beneath are the steps so as to add this code to the Private Macro Workbook:

Click on on the file macro button (it’s on the backside left of the Excel workbook software)Within the Document Macro dialog field, change the Retailer macro in setting to – Private Macro Workbook.Click on OK. It will begin recording the macroClick on on the Cease macro recording icon (on the backside left of the workbook). It will cease the macro recording Proper-click on any sheet tab after which click on on ‘View Code’Within the VB Editor, double-click on the Module object within the Private.XLSB workbookTake away any present code and duplicate and paste the above code.Click on the Save icon within the toolbarShut the Vb Editor

The above steps can help you make the Private Macro Workbook seen within the VB Editor and place the code to unhide sheets in it.

Now all it’s essential do is add this code to the Fast Entry Toolbar so to use it anytime from any workbook.

Beneath are the steps so as to add this code to the Fast Entry Toolbar:

Click on on the Customise Fast Entry Toolbar icon.Click on on Extra Instructions.Within the Excel Choices dialog field, click on on the ‘Select Instructions from’ drop-downClick on on Macros. It will present you a listing of all of the macros within the workbook (together with those in PERSONAL.XLSB)Choose the macro code to unhide sheetsClick on on the Add buttonClick on OK.

The above steps would add this macro code to unhide sheets within the Fast Entry Toolbar.

Now, everytime you get a workbook that has some sheets hidden, you simply must click on on the code icon within the QAT and it’ll immediately unhide all sheets in a single go.

Unhide Sheets With Particular Textual content within the Identify

With VBA, you may as well unhide sheets primarily based on the title.

For instance, suppose you could have a workbook that comprises sheets with years within the title and also you need to unhide all those the place the yr is 2020.

You need to use the beneath code to unhide all of the sheets with the textual content 2020 in it:

Sub UnhideSheetsWithSpecificText()
For Every ws In ThisWorkbook.Worksheets
If InStr(ws.Identify, “2020”) > zero Then
ws.Seen = xlSheetVisible
Finish If
Subsequent ws
Finish Sub

The above makes use of the For Subsequent loop to undergo every worksheet within the workbook. The IF Then situation then checks the title of the worksheet and if it comprises the required textual content (which is 2020 on this code), it can change the seen property to make it seen.

And if the title doesn’t include the required textual content, it can go away it as is.

You may also modify this code to cover sheets primarily based on the textual content within the title.

For instance, if you wish to shortly cover all of the worksheets the place the title comprises the textual content ‘2020’ in it, you need to use the beneath code:

Sub HideSheetsWithSpecificText()
For Every ws In ThisWorkbook.Worksheets
If InStr(ws.Identify, “2020”) > zero Then
ws.Seen = xlHidden
Finish If
Subsequent ws
Finish Sub

Observe: It can save you this code in an everyday module inside VB Editor or it can save you this within the Private Macro Workbook. In case you reserve it in an everyday module and wish to make use of it once more later, it’s essential save the workbook as a macro-enabled workbook (.XLSM format).

Unhide Chosen Sheets (Primarily based on Consumer Choice)

You may also use VBA to provide the person the flexibleness to decide on whether or not to unhide a sheet or not.

This may be accomplished by exhibiting a message field that asks the person to pick out whether or not to unhide a sheet or not. If chosen, it unhides that sheet, else it strikes to the subsequent one.

Beneath is the code that can do that:

Sub UnhideSheetsUserSelection()
For Every sh In ThisWorkbook.Sheets
If sh.Seen <> True Then
End result = MsgBox(“Do You Wish to Unhide ” & sh.Identify, vbYesNo)
If End result = vbYes Then sh.Seen = True
Finish If
Subsequent sh
Finish Sub

The above code goes by way of every sheet within the workbook and checks whether or not it’s already seen or not. If it’s hidden, then it reveals the message field with the title of the worksheet.

As a person, now you can determine whether or not you need to hold this sheet hidden or unhide it.

This will work properly when you have some worksheets which are hidden and also you need to take a name for each sheet individually.

Observe: It can save you this code in an everyday module inside VB Editor or it can save you this within the Private Macro Workbook. In case you reserve it in an everyday module and wish to make use of it once more later, it’s essential save the workbook as a macro-enabled workbook (.XLSM format).

Here’s a tutorial the place I present methods to save the code within the common module in Excel (seek for the ‘The place to place this code’ part on this article)

Unhide All or Chosen Sheets Utilizing Customized View

This can be a much less recognized methodology in case you need to shortly unhide all of the worksheets (or some chosen worksheets).

‘Customized View’ is performance in Excel that permits you to create and save views which you can shortly resort to with a click on of a button.

For instance, suppose you could have an Excel workbook with 10 worksheets. You may create a view the place all these 10 sheets are seen. Sooner or later, when you have some sheets hidden and also you need o return to the view the place all of the sheets had been seen, you are able to do that by choosing the already saved customized view.

Don’t fear, you don’t lose any modifications you made after creating the customized view. All customized view does is takes you again to the Excel view whenever you created it. So if some worksheets had been seen whenever you created the view and are actually hidden, choosing that customized view would unhide these sheets.

The supposed use of Customized View is to permit customers to create totally different views. For instance, when you’re an analyst, you possibly can create totally different views for various departments in your group. So you possibly can have a particular set of worksheets (or cells/rows/columns) seen for one division and one other set for an additional division. Upon getting these views, as a substitute of fixing this manually, you merely activate the view for a division and it’ll present you worksheets (or rows/columns) related for them solely.

Beneath are the steps to create a customized view in Excel:

Unhide all of the worksheets to start withClick the View tabClick on Customized ViewsWithin the Customized Views dialog field, click on on Add. It will open the Add view dialog fieldEnter any title for this view the place all of the sheets (or chosen sheets) are seenClick on OK.

As soon as the view is created, you possibly can anytime ask Excel to activate this view (which might make all these sheets seen that had been seen whenever you created the view).

Beneath are the steps to indicate/activate a customized view:

 Click on the View tabClick on Customized ViewsWithin the Customized Views dialog field, choose the view that you just need to presentClick on on Present button

This could immediately unhide sheets and present those who had been seen whenever you created that customized view.

Unhiding Sheets which are ‘Very Hidden’

Generally, regardless of having some hidden sheets in your workbook, you wouldn’t have the ability to unhide it manually.

This may very well be as a result of these sheets should not simply hidden – these are ‘very hidden’.

When you could have hidden sheets in a workbook and also you right-click on any tab title, you’d see the choice to ‘Unhide’ sheets. However when you have sheets are ‘very hidden’ or if there aren’t any hidden sheets, then you wouldn’t have the ability to use this selection (it is going to be greyed out).

You may nonetheless unhide these ‘very hidden’ sheets by utilizing the VBA code that now we have coated above.

Simply copy-paste the beneath code within the fast window and hit enter and it will immediately unhide all of the sheets (hidden in addition to very hidden).

For every Sheet in Thisworkbook.Sheets: Sheet.Seen=True: Subsequent Sheet

I even have a full tutorial on methods to cover sheets and make these very hidden (in case you’re concerned with studying)

You may additionally like the next Excel tutorials: