Unhide Sheets In Excel

What Is Unhide Sheets In Excel?

Unhide sheets in Excel is an option to bring hidden worksheets to view. Thus, while we reference data from hidden sheets, the option to unhide sheets makes the hidden data visible.

Now, users can unhide Excel sheets when they must show all the hidden worksheet tabs in the file and maintain transparency.

For example, the image below shows a workbook with two worksheets, Worksheet1 and Worksheet4.

Now, consider the file also has two hidden worksheets, Worksheet2 and Worksheet3, which we need to unhide.

Then, we can use the Unhide Sheet option under the Format option in the Home tab to unhide the required sheets one after the other.

Once we choose the Unhide Sheet option, the Unhide window opens, showing all the hidden worksheets in the workbook.

Now, we can select Worksheet2 and click OK in the Unhide window, which will show the Worksheet2 tab after the Worksheet1 tab in the Excel file.

And then, we can select the Unhide Sheet option and repeat the process to unhide the sheet Worksheet3.

In the above example, we had to unhide only two sheets. But if we must make numerous sheets visible or unhide all sheets in Excel for a requirement, the above method will be cumbersome.

We can use VBA macros in such scenarios, which we will see with examples in the following sections.

Top 6 Methods To Unhide Excel Sheets

We shall see the different methods to unhide Excel worksheets.

Method #1 – Using Right Click

The above steps will unhide the required worksheet in the file.

Further, if the total number of worksheets to unhide is very less, we can use the above steps to unhide all sheets in Excel one by one.

Example

Consider a workbook containing a series of worksheets, ExcelMojo_Sheet1, ExcelMojo_Sheet2, and ExcelMojo_Sheet3. And the worksheets ExcelMojo_Sheet1 and ExcelMojo_Sheet2 are hidden, as shown below:

Now, let us learn how to unhide the sheets.

• Step 1: To start with, right-click on a sheet tab in the workbook to open the contextual menu. And choose Unhide from the menu.

Now, the Unhide window will open, listing all the hidden worksheets in the current workbook.

• Step 2: Next, click the ExcelMojo_Sheet1 worksheet name to select it and then, click OK in the Unhide window.

This will unhide the chosen worksheet, ExcelMojo_Sheet1, and make it visible in the workbook.

• Step 3: Then, repeat steps 1 and 2 to unhide ExcelMojo_Sheet2. While the process remains the same, however, the worksheet to choose in the Unhide window will be ExcelMojo_Sheet2.

Method #2 – Unhide Multiple Sheets

We can use unhide sheets in Excel VBA code to make multiple hidden sheets visible easily, as explained below:

1. First, with the current worksheet open, press Alt + F11 to open the VBA Editor.
2. Next, select the required VBAProject and choose the Module option from the Insert tab in the menu to open a module.
3. Then, enter the unhide sheets in Excel VBA code in the module window.
4. Now, click the Play icon to run the VBA code.
5. Finally, go to the active worksheet to view the required multiple hidden sheets, which will now be unhidden.

Example

The active workbook contains a set of worksheets, Multiple_Sheets_Example1, Multiple_Sheets_Example2, Multiple_Sheets_Example3, Multiple_Sheets_Example4, and Multiple_Sheets_Example5. And the last four sheets remain hidden, as shown in the image below.

So, here is how to use the Excel VBA code to unhide multiple worksheets quickly.

• Step 1: To begin with, press Alt + F11 to access the VBA Editor with the active sheet open.

• Step 2: Next, select the applicable VBAProject and then, choose the Module option under the Insert tab.

The module window, Module1, opens, where we must enter the required VBA code.

• Step 3: Now, enter the VBA code to unhide multiple worksheets in an Excel file.

• Step 4: Next, select the Play icon to run the entered code.

Clearly, the code includes commands to show a message box, asking users whether to unhide the specified hidden worksheet.

And if the user chooses to unhide the specified hidden worksheet, the sheet becomes visible in the workbook.

Next, once the user clicks Yes, the message box asks the user whether to unhide the next hidden sheet.

Now, the process continues till the last hidden worksheet.

Finally, as a user, when we click Yes to unhide the last hidden worksheet, the VBA Editor window opens.

So, we can now go to the active workbook to see all the required multiple sheets unhidden in the file.

Furthermore, the specified worksheet remains hidden if we choose No in the message box. And then, the code continues the unhiding sheets process with the message box asking us whether to unhide the next worksheet.

Method #3 – Unhide All Worksheets Except Particular Worksheet

We may need to make all the sheets visible in Excel, leaving a particular worksheet hidden. In such a scenario, we can use a VBA macro to unhide sheets in Excel according to our requirements. And the steps are:

1. With the current worksheet open, press the shortcut keys Alt + F11 to open the VBA Editor.
2. Choose the required VBAProject and select the Module option from the Insert tab in the menu to open a module.
3. Enter the VBA macro to unhide sheets in Excel, leaving out a specific sheet in the module window.
4. Select the Play icon to execute the VBA code.
5. Go to the active workbook to view all the sheets, leaving the worksheet we intend to keep hidden.

Example

The workbook contains worksheets, Monthly_Income, Monthly_Savings, Monthly_Expenses, and Monthly_Expenses_Misc, with the last three sheets hidden.

If the requirement is to unhide all the hidden worksheets, except the Monthly_Expenses_Misc worksheet. Then, we can use VBA coding to achieve the desired outcome.

• Step 1: To begin, with the current worksheet open, access the VBA Editor using the Alt + F11 keys.

• Step 2: Next, after selecting the required VBAProject, choose the Module option from the Insert tab.

As we inserted Module1 in the chosen VBAProject in the previous example, we see Module2 listed below Module1,and the Module2 window opens.

• Step 3: Then, enter the VBA code to unhide all worksheets, leaving out the Monthly_Expenses_Misc sheet.

• Step 4: Now, choose the Play icon to run the code.

Finally, when we open the active workbook, we see all the worksheets except for the Monthly_Expenses_Misc sheet.

The code contains a For loop. It checks each worksheet in the workbook to confirm if the sheet name is not Monthly_Expenses_Misc.

If the specific sheet name is not Monthly_Expenses_Misc, the condition holds. And hence, the command to set the sheet’s Visible property assigns the value -1 (xlSheetVisible) to it. And, as the value of -1 interprets as displaying the sheet, the specific worksheet becomes unhidden.

But if the sheet name matches Monthly_Expenses_Misc, the condition is false. And thus, the sheet remains hidden.

Method #4 – Unhide Only Specific Excel Sheet

We can unhide a specific Excel worksheet using VBA coding, and the procedure is as follows:

1. First, with the active worksheet open, press the excel keyboard shortcut Alt + F11 to access the VBA Editor.
2. Next, click the required VBAProject and choose the Module option under the Insert tab in the menu to open a module.
3. Then, enter the VBA code to unhide a specific sheet in the module window.
4. Now, click the Play icon to run the VBA code.
5. Finally, go to the current workbook to view the required sheet unhidden.

Example

The current workbook contains a series of worksheets, 2019_Sales_Report, 2020_Sales_Report, and 2021_Sales_Report, with the first two sheets hidden.

Now, let us use VBA coding to unhide only the 2020_Sales_Report sheet.

• Step 1: First, keep the above-shown worksheet open and press Alt + F11 to access the VBA Editor.

Step 2: Next, choose the required VBAProject and select Insert → Module.

The Module3 window opens, as we created Module1 and Module2 in the previous illustrations.

• Step 3: Then, enter the VBA macro to unhide the required sheet.

• Step 4: Now, select the Play option to execute the code.

Once the code gets executed, we see that the sheet 2020_Sales_Report unhides in the active workbook.

The code contains a For loop. It checks each worksheet in the workbook to confirm if the sheet name is 2020_Sales_Report.

If the specific sheet name is 2020_Sales_Report, the condition holds. And hence, the command to set the sheet’s Visible property assigns the value -1 (xlSheetVisible) to it. And, as the value of -1 indicates to display the sheet, the specific worksheet becomes unhidden.

But if the sheet name does not match 2020_Sales_Report, the condition is false. And thus, the sheet remains hidden.

Method #5 – Unhide All Excel Sheets

We can unhide all worksheets in an Excel file using the Immediate Window option in the VBA Editor. And the method is as follows:

1. To begin with, right-click an unhidden or visible sheet tab and choose View Code from the contextual menu.
2. Now, the VBA Editor opens, showing the Microsoft Excel Objects in the current workbook.
3. Next, choose Immediate Window in the View tab in the menu to open the Immediate window.
4. Then, enter the For loop-based code to unhide all the worksheets in the current workbook.
5. Ensure the cursor is at the end of the code and press Enter.

The above steps will unhide all the worksheets in the active worksheet in one go.

Example

The active workbook contains six worksheets, SUM(), MIN(), MAX(), LARGE(), SMALL(), and IF(), with all worksheets hidden except MAX() sheet.

If the requirement is to unhide all the hidden worksheets in the active workbook. Then, here is how to use the Immediate Window option to achieve the required outcome.

• Step 1: First, right-click the visible sheet tab, Max Excel Formula and choose the View Code option in the contextual menu.

The VBA Editor opens, displaying the Microsoft Excel Objects in the current workbook.

• Step 2: Next, select the Immediate Window option from the View tab in the menu.

Now, the Immediate window will open.

• Step 3: Then, enter the For loop-based code to make all the worksheets in the Excel file visible.

For each ws in Thisworkbook.Sheets: ws.Visible=True: Next ws

• Step 4: Now, ensure the cursor is at the end of the code and press Enter.

And once the commands execute and we open the active workbook, we see all the hidden worksheets visible in the order of their creation.

The code uses the For Next loop to set the Visible property of each sheet in the active Excel file as TRUE. And, though the macro appears as a one-line code, it contains three lines separated by two colons (‘:’), which are the line breaks.

Furthermore, this method does not require us to save the workbook as a macro-enabled file. We can save it as a regular Excel Workbook.

[Alternatively, we can use the following VBA code to unhide all the worksheets in an Excel file.

And the steps to execute the above code are the same as explained in the previous methods 2 to 4.

This code also uses the For loop to set the Visible property of each sheet as -1 (xlSheetVisible), indicating to display sheet. And the loop continues till the Visible property of the last sheet in the file gets updated as xlSheetVisible.]

Method #6 – Using Format Option And Unhide In Excel Shortcut

We can unhide sheets using the Unhide Sheet option under the Format option in the Home tab, as explained below:

1. To begin with, in the current worksheet, choose the Home tab and click the Format option. And then, choose the Hide & Unhide option, followed by Unhide Sheet option to open the Unhide window. Otherwise, we can use the Excel shortcut Alt + H + O + U + H to open the Unhide window.
2. Next, click the worksheet we wish to unhide and click OK in the Unhide window. The above steps will make the specific hidden worksheet visible in the Excel file.
3. Then, repeat steps 1 and 2 to unhide each of the remaining sheets, one by one.

Example

The active worksheet includes three worksheets, Excel_Shortcuts1, Excel_Shortcuts2, and Excel_Shortcuts3, with the last two sheets hidden.

Now, let us learn how to unhide the two sheets, one by one, using the Unhide Sheet option in the Format option.

• Step 1: To start with, choose Home → Format → Hide & Unhide → Unhide Sheet in the visible sheet.

Otherwise, we can use the unhide sheets in Excel shortcut, Alt + H + O + U + H, to open the Unhide window.

The Unhide window opens.

• Step 2: Next, click on the Excel_Shortcuts2 sheet name to choose it and click OK in the Unhide window.

And immediately, the hidden sheet Excel_Shortcuts2 becomes visible in the file.

• Step 3: Then, repeat steps 1 and 2 to unhide the second worksheet, Excel_Shortcuts3, as shown below.

Important Things To Note

• The option to unhide sheets in Excel can unhide one sheet at a time.
• The option to unhide Excel sheets will not work if the workbook is protected or the workbook contains very hidden or no hidden worksheets.
• When using the Immediate Window option to unhide all or very hidden sheets, we do not require saving the Excel file as a macro-enabled workbook.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Unhide Sheets In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Unhide Sheets In Excel. Here we learn different methods to unhide sheets in a workbook, along with examples & downloadable template. You can learn more from the following articles –

• Unhide Excel Column
• Hide Column In Excel
• VBA Loops