While working with Excel, we are able to hide or show selected worksheets. In some cases, it is necessary for us to hide worksheets to control or simplify the display of information, or to prevent unwanted changes in the values and formula in some sheets.
Figure 1. Final result: How to unhide sheets
When we encounter files with hidden sheets, we can easily unhide the sheets through either of these methods:
- Right-click method
- Format menu
- Custom View
Right-click method to unhide sheets
This is the simplest method to unhide sheets in Excel. Suppose we have a file with three sheets, but only Sheet1 is visible in the window.
Figure 2. Sample file with hidden sheets
In order to unhide the other worksheets, we follow these steps:
- Right-click the visible sheet tab at the bottom of the workbook named Sheet1 and select Unhide
Figure 3. Selecting the unhide option
The Unhide dialog box will appear, showing a list of sheet names for the hidden sheets. In this case, the hidden sheets are Sheet2 and Sheet3.
Figure 4. Selecting the worksheet to unhide
- We select the sheet we want to unhide, Sheet 2, and click OK.
Figure 5. Output: Unhide a sheet through right-click method
Sheet2 will be instantly unhidden and visible on the window.
Unhide sheet in Format Menu
There is another way to unhide sheets through the Home tab > Format button.
- From the drop-down menu, select Hide & Unhide under Visibility and click Unhide Sheet.
Figure 6. Unhide Sheet option
The Unhide dialog box will appear just like in the previous example. Then we get to select the sheet that we want to unhide, say Sheet3, and click OK.
Figure 7. Selecting to unhide Sheet3
Sheet 3 will then be visible and unhidden.
Figure 8. Output: Unhide a sheet through Format menu
The first two methods show how to unhide sheets one by one. There is another method that allows us to unhide sheets all at once.
Unhide all sheets
In order to unhide multiple sheets or all sheets, we follow these steps:
- Click View tab > Custom Views
Figure 9. Custom View ribbon button
The Custom Views dialog box will appear.
Figure 10. Custom Views preview
- Click Add to launch the Add View dialog box. We enter a name for the custom view, for instance, Unhide all sheets
Figure 11. Add View dialog box
Note that the checkboxes for Print settings and Hidden rows, columns and filter settings are ticked. When we click OK, we have already created a custom view that will show all hidden sheets.
How to unhide all sheets?
Suppose we have hidden Sheet2 and Sheet3 and now we want to unhide them, we only have to click Custom View, select the view “Unhide all sheets” that we have created earlier, the click Show.
Figure 12. Show the selected Custom View
In an instant, we will be able to unhide all hidden sheets at the same time.
Figure 13. Output: Unhide all sheets