Unhide All Sheets in Excel (Table of Contents)
Introduction to Unhide Sheets in Excel
In this article, we will see an outline on How to unhide sheets in excel?. Hiding multiple worksheets in an Excel workbook is quite easy, however, unhiding them can be tedious. We can unhide multiple sheets one by one in Excel or use a VBA code to unhide all of these in one go. The need to unhide sheets can be seen when we analyze that the sheet is hidden.
Let us see below some of the commonly used methods or ways using which we can unhide one or all of the hidden worksheets in an Excel workbook.
Examples to Unhide Worksheets in Excel
Let’s look at a few examples to unhide all hidden worksheets one by one in Excel.
Example #1
Let us say we have an Excel workbook with three worksheets (named as Sheet1, Sheet2, Sheet3) that are hidden and we wish to unhide one or all of these hidden worksheets one by one. To do this, we follow the below steps:
- Click on the ‘Home’ tab, and then select ‘Format’ -> ‘Hide & Unhide’ -> ‘Unhide Sheet’ :
On doing this, an Unhide dialog box will appear with all the hidden worksheets. The following screenshot illustrates this.
Now select the sheet that is to be unhidden and then click on ‘OK’. This will display the selected hidden sheet.
So in the above screenshot, we can see that ‘Sheet1’ is now visible to us that was previously hidden. We can repeat the above steps if we wish to unhide the other two sheets.
Example #2
Now let us say we wish to unhide ‘Sheet2’ and ‘Sheet3’ in the same Excel workbook. We use another method to do this:
Right-click on any of the worksheet tabs and then select ‘Unhide’.
On doing this, an Unhide dialog box will appear with all the hidden worksheets. The following screenshot illustrates this.
Now select the sheet that is to be unhidden and then click on ‘OK’. This will display the selected hidden sheet.
So in the above screenshot, we can see that ‘Sheet2’ is now visible to us that was previously hidden.
Now we can repeat the above steps to unhide ‘Sheet3’ as well.
Example #3
Apart from the above two ways that we have used to unhide one sheet at a time, there is another method to open the same ‘Unhide’ dialog box and then unhide the required hidden sheet. Let us see how this method works:
- Press Alt+H+O+U+H. On using this Excel shortcut key, the Unhide dialog box (same as we saw in the above two examples) will appear with all the hidden worksheets. Below is the screenshot:
Now select the sheet that is to be unhidden and then click on ‘OK’. This will display the selected hidden sheet.
So in the above screenshot, we can see that ‘Sheet3’ is now visible to us that was previously hidden.
Example #4
Now let us say in the workbook that we have seen in the above three examples, we once again hide all the three sheets (Sheet1, Sheet2, Sheet3) in them. So this is how the workbook looks like now:
Now we wish to unhide all the hidden worksheets in one go. We have seen in the above three examples that we were not able to unhide all the sheets at once. Using the ‘Unhide’ dialog box, we could only unhide one sheet at a time. But this might be quite time taking in case there are a lot of sheets that are required to be unhidden. So one way to unhide all the hidden sheets in one go is by writing a VBA code in Excel. Let us see how this works:
We write the VBA code in the Visual Basic Editor that can be accessed as follows:
Go to the Developer tab, then click on Visual Basic Editor, or press Alt+F11 to open the Visual Basic Editor window.
On doing this, a window opens as follows:
Right-click on the workbook name in the ‘Project-VBAProject’ pane and then click on ‘Insert’-> ‘Module’ as follows :
Now we can write our VBA code or sub-procedure in this module:
Code:
Sub UnhideAllSheetsCode()
End Sub
Define the variable ‘ws’:
Code:
Sub UnhideAllSheetsCode() Dim ws As Worksheet End Sub
So the first statement of the code defines the variable ‘ws’.
Now in the following statements of the VBA code, we use the ‘For Each’ loop with Worksheet.Visible property to unhide all the hidden worksheets in the current or active workbook.
Code:
Sub UnhideAllSheetsCode()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
The Worksheet.Visible property is used to return or set an xlSheetVisibility value which is used to check or determine if the object is visible. xlSheetVisibility is a way for the user to make the sheets visible by setting the visible property to true. The syntax for visible property is expression.visible, where expression is a variable that represents a Worksheet object.
Now when we run this code manually by clicking on ‘Run’ at the top of the window or by pressing F5, we will see that all the hidden sheets gets unhidden all in one go. Following screenshot illustrates this:
So, we can see in the above screenshot that on running the sub-procedure or the VBA code, all the hidden sheets (Sheet1, Sheet2, Sheet3) in the workbook get visible in one go.
Things to Remember About How to Unhide All Sheets in Excel
- There may be times when we are unable to unhide certain worksheets in an Excel workbook. This may be due to the following reasons:
- The workbook may be protected.
- The worksheets are hidden using a VBA code (i.e. they are ‘VeryHidden’ using the XlSheetVeryHidden property).
- We can also unhide all the worksheets except a particular worksheet in a workbook by writing a VBA code.
- Another method to unhide all sheets in one go that can be used in all the versions of Excel is using the ‘Custom Views’ method.
Recommended Articles
This is a guide to How to Unhide All Sheets in Excel?. Here we discuss How to Unhide All Sheets in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
