How to unhide worksheet in Excel?
In MS Excel, you can hide the worksheet and also unhide it back to be in use. You can hide any worksheet to remove it from view mode. The data of the hidden worksheet is also hidden when the worksheet is hidden, but you can still reference its data through another worksheet. To make the data visible of the hidden worksheet, you have to unhide the worksheet.
Excel users can easily hide and unhide the worksheet. Excel enables several ways to unhide the worksheet. You can choose one of them which best fits you. The Excel users can unhide the worksheet very easily if they want.
In this chapter, we will brief about hidden worksheets and ways to unhide the worksheet.
What if a worksheet is hidden?
When the worksheet is hidden –
- When an Excel worksheet is hidden, its data is also hidden.
- The user can reference the data of the hidden worksheet from another worksheet or workbook.
- If you want to see the data of the hidden worksheet, you have to unhide it to make the worksheet and its data visible.
- If the multiple sheets are hidden of an Excel workbook, only one sheet can unhide at a time.
Unhide a hidden worksheet
When a worksheet is hidden, you cannot see its data. To make the worksheet and its data visible again, unhide the worksheet. Here are the simple steps to unhide a hidden worksheet –
Step 1: Right-click on the existing sheet tabs and click on Unhide option in the list.
Step 2: You will see the hidden worksheet in the panel. Currently, only one worksheet is hidden, i.e., August.
Step 3: Select the hidden worksheet and click OK.
Step 4: The selected worksheet has been unhide successfully. You can now see its data and work with it.
Unhide “Very Hidden” worksheet
Unless the simple hidden worksheet, Excel worksheet can also be very hidden. In that case, simple unhiding method would not work. You require another method to unhide the very hidden worksheet. From the Excel VBA code editor, users can easily unhide the very hidden worksheet.
We have an Excel worksheet that is not simply hidden, it is very hidden. When the worksheet is hidden, it does not display below in the Excel sheet panel. Follow the steps to unhide the very hidden worksheet –
Step 1: Right-click on any of the existing sheet tabs and click on View Code.
Step 2: In the right side of the VBA code editor panel, you will see all hidden and unhidden worksheets available in the currently opened workbook.
Step 3: Click the sheet, which is not available (hidden), and you want to unhide it. For example, Sheet 2 (August) is hidden here.
Step 4: When you select the worksheet, its related properties is enabled inside the properties pane below the sheet window.
Tip: If the properties pane is not currently available in VBA code editor, you can enable it yourself. There is an option Properties Window; click it to make it visible.
Step 5: Change the property Visible to -1-xlSheetVisible by choosing it from the corresponding dropdown button.
Here,
| Visible Property parameter | Description |
|---|---|
| -1 – xlSheetVisible (TRUE) | If this option is selected, sheet is visible. |
| 0 – xlSheetHidde (FALSE) | If this option is selected, sheet is hidden. |
| 2 – xlSheetVeryHidden | This option is selected when the sheet is very hidden. |
Step 6: Now, close the VBA editor.
Step 7: You will now see that the very hidden worksheet is now visible in the workbook and the hidden worksheet (September) unhide successfully.
Unhide multiple worksheets
Sometimes, more than one worksheet is hidden and you want to work them. So, you have to unhide all the hidden worksheets to make them visible. In MS Excel, we have methods to unhide worksheets one-by-one or at once. The method we discussed above is unhiding one worksheet at a time.
If you the first method to unhide the worksheet, you have to repeat the same steps number of times how many worksheets you want to unhide. Because it allows to unhide one worksheet at a time. So, it can consume too much time if hidden worksheets are too much.
Step 1: Right-click on the existing sheet tabs and click on Unhide option in the list.
Step 2: You will see a list of all hidden worksheets in the panel. Select a worksheet here and click OK.
Note: This option does not allow to select multiple hidden worksheets at once.
Step 3: The selected worksheet has been unhide successfully. Similarly, follow the same steps until all worksheets unhide.
Step 4: When you perform the same steps again to unhide another worksheet, you will see only one worksheet in the list.
Like this, you have to perform the same step again and again for each worksheet. This method takes time as each time you have to perform the same steps multiple times.
We have another method to unhide the multiple worksheets at once. To make all the worksheets visible again, follow this method. Here are the steps to unhide the multiple hidden worksheets in one go –
Step 1: Right-click on the existing sheet tabs and click on the View Code option in the list.
Step 2: A VBA code editor panel will open that will contain a list of all worksheets, including hidden and visible.
Step 3: Select the sheet one by one and set its Visible property to -1-xlSheetVisible in the Property pane.
Set the visible property to -1-xlSheetVisible for all hidden worksheets.
Step 4: Close the VBA editor window when all sheets visible property is set to -1-xlSheetVisible. You will now see that all sheets are now visible.
Unhide all hidden worksheets at once
There is no predefined built-in method in Excel to unhide all hidden worksheets in one go. The users have to manually unhide all hidden worksheets one by one. Excel enables several methods for it, as we also discussed in the above example. But this takes time as you have to perform the number of times how many worksheets you want to unhide.
Additionally, you can create VBA code to unhide all the hidden worksheets in one go. So, in this method, we will write VBA code and unhide all the hidden worksheets at once by running the code.
Follow the given VBA steps below:
Step 1: Go to the Developer tab in the Excel ribbon and click on the Visual Basics to open the VBA Editor panel.
Step 2: Inside the VBA panel, navigate to the Insert tab and select the Module button to open a coding window to write the code here.
Step 3: Write the following code in VBA editor that will help to unhide the worksheets.
Step 4: Next step is to run the method written in this code. For this, click the Run button.
Step 5: On running the following code, all the hidden sheets will be unhidden. Close the VBA editor to see the unhidden file.
This one is the best way to choose when several worksheets are hidden. This will save the time of users to unhide the worksheets one by one.
