Updated May 29, 2023
Unhide All Sheets In Excel (Table of Contents)
Introduction to Unhide Sheets in Excel
Hiding multiple worksheets in an Excel workbook is quite easy; however, unhiding them can be tedious. We can see the need to unhide sheets when we analyze the hidden sheet. We can unhide multiple sheets one by one in Excel or use a VBA code to unhide all of these in one go. In this article, we will see an outline of How to unhide sheets in Excel.
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 Sheet1, Sheet2, and 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 to be unhidden and then click ‘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 to unhide the other two sheets.
Example #2
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 to be unhidden and then click ‘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 we have used to unhide one sheet at a time, another method opens 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 dialogue 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 to be unhidden and then click ‘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, in the workbook we have seen in the above three examples, we again hide all three sheets (Sheet1, Sheet2, Sheet3). So this is what the workbook looks like now:
Now we wish to unhide all the hidden worksheets in one go. In the above three examples, we have seen that we could not unhide all the sheets at once. We could only unhide one sheet at a time using the Unhide’ dialog box. But this might be quite a time taking if many sheets 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, which can be accessed as follows:
Go to the Developer tab, 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’.
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
wsVisible = xlSheetVisible
Next ws
End Sub
The Worksheet. Visible property returns or sets an xlSheetVisibility value to check or determine if the object is visible. xlSheetVisibility allows the user to make the sheets visible by setting the visible property to true. The syntax for the visible property is expression .visible, where expression is a variable representing a Worksheet object.
When we manually run this code by clicking on ‘Run’ at the top of the window or pressing F5, we will see that all the hidden sheets get unhidden in one go. The 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 cannot 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 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
