Excel allows you to hide sheets to keep your workbooks tidy and free from clutter. It also means users can’t edit data or formulas in sheets which you don’t want them to manipulate. In this post, I will show you how to unhide sheets in Excel. As with hiding sheets, unhiding them is also very simple and there are a couple of ways to do this. The first is by using the mouse and the second is by using the Excel ribbon. If your sheet is very hidden, then I will explain how to unhide them too. Finally, I will show you how to unhide all sheets in one go to save valuable time.
Using the Mouse
To unhide a sheet using the mouse, then follow these steps.
- Right-click any of the existing tabs.
- In the shortcut menu, select Unhide.
- The Unhide dialog box will appear. Select the sheet that you want to unhide under Unhide sheet. In this example, only one sheet is hidden in the workbook which is Sheet2. Next, press the OK button.
- This will instantly make the sheet visible in the workbook.
Using the Excel Ribbon
The second way to unhide a sheet is by using the Excel ribbon. To do this, follow these steps.
- Select any sheet tab.
- From the Home tab, press the Format button under the Cells group. From the menu select Hide & Unhide. In the next menu, select Unhide Sheet.
- As with the previous method, the Unhide dialog box will appear. Select the sheet that you want to unhide under Unhide sheet and then press the OK button.
- The sheet will be unhidden.
How to Unhide Very Hidden Sheets
You can make your sheets very hidden for extra security. To learn more about this, then please see my post called “3 Awesome Ways on How to Hide Sheets in Excel“. To unhide very hidden sheets, then follow these steps.
- Open the Visual Basic Editor by clicking on the Developer tab. Under the Code group click on the Visual Basic button. You can also press the shortcut keys Alt + F11 to access the Visual Basic Editor.
- Select the sheet that is very hidden in the Project Explorer. In this example, Sheet2 is very hidden so I select this sheet.
- In the Properties Window under the Visible option field , select -1 – xlSheetVisible from the drop-down box.
- Sheet2 is now unhidden.
How to Unhide All Sheets in Excel at Once
The problem with the previous methods I showed of unhiding sheets in Excel, is that you can only unhide one sheet at a time with the mouse or Excel ribbon. To unhide each sheet, you would have to follow the same steps again, depending on the number of sheets you want to hide. I will now show you a way to unhide all sheets in Excel VBA and more specifically, using the Immediate Window. Here are the steps to do this.
- Open the Visual Basic Editor by clicking on the Developer tab and then under the Code group click on the Visual Basic button. You can also press the shortcut keys Alt + F11 to access the Visual Basic Editor.
- Click on View from the menu and then click on Immediate Window. The Immediate Window will appear in the Visual Basic Editor at the bottom of the window.
- In the Immediate Window, copy and paste the following code: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
- Place the curser at the end of the line.
- Now, press Enter on your keyboard and all the sheets will be unhidden in one go. You can then close the Visual Basic Editor.
That is how you can unhide sheets in Excel. You have learnt two different ways on how to unhide sheets using the mouse and Excel Window. You have also learnt how to unhide very hidden worksheets and even how to unhide multiple sheets in one go. If you liked this tutorial then please send me a message in the comments section below. Please also give me a like and share this post to others who you think would find this post helpful.
Do you Want Even More VBA Macros at Your Disposal?
In this post, I have shown you one macro to increase your productivity by unhiding multiple sheets in one go. Do you want to have more codes which you can simply add to your workbooks to save you valuable time? If the answer is yes, then you are in luck. I have written a book called Excel Bible for Beginners: Excel for Dummies Book Containing the Most Awesome Ready to use Excel VBA Macros. This book includes a free Excel workbook that contains lots of valuable macros that you can simply download and then copy and paste straight to your workbooks! This book will save you time, enable you to work smarter, increase your efficiency and reduce stress.
Some of the VBA macros in this Excel for dummies book include:
- Auto fit all columns and rows
- Insert multiple columns and rows in one go
- Create a backup of the current workbook and save it
- Create an email message and attach a workbook to it
- Highlight misspelled words
- Create a chart
- Format all charts in a worksheet
- Word count an entire worksheet
- Create a pivot table
- Save a selected range as a PDF
- Create a data entry form
- Create a table of contents
- And much more!
To learn more about this Excel book or to purchase it from Amazon, then press either of the buttons below.
