How to Unhide Sheets in Excel (Single or All Sheets)

Whenever you think it’s better to read a tutorial than watching a video, here is a detailed tutorial on how you can unhide sheets in Excel.

Every time you work with data spread all over various Excel worksheets, there may be times when you want to have some of the worksheets hidden.

You may need this to lessen the clutter or avoid making your manager or client confused by only showing them the useful ones.

There are even cases when you need to go through a workbook with hidden sheets that you want to unhide all of those that you can’t see.

Our tutorial will allow you to become aware of how you can unhide Excel worksheets both manually and automatically with the VBA.

We will also provide information on how you can be selective in unhiding worksheets according to a condition or their name. Let’s begin!

Have All Sheets Unhidden At Just One Go

It can be easy to have worksheets unhidden in bulk with VBA.

For example, whenever you have ten hidden worksheets, all you need to do to unhide them is making a simple VBA code.

You may also unhide them based on a specific condition, like only unhiding those with a name that has a particular year or prefix.

Please note that in our tutorial, the methods covered here don’t require people to save their Excel workbook.XLSM or macro-enabled format to use the VBA code.

Use an Immediate Window

Excel has a VB Editor with a prompt window.

Here, you can have a line of code typed in to have it executed in an instant.

These are the steps on how you can use the above line of code if you want to have the sheets unhidden through the immediate window:

  1. Right-click on the workbook’s visible sheets.
  2. Open the VB Editor by clicking on the View code.                                                                                                                     
  3. In the menu, tap on the View option. After that, click on the Immediate window to make it appear in the VB Editor if it is not there yet. 
  4. Have this line of code copied and pasted in the Immediate window: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
  5. At the end of the line, place the cursor.                                               
  6. Tap on the Enter key

Once you complete that, you’re done!

With these steps, you can have all of the workbook’s sheets unhidden.

Close the VB Editor when you’re finished.

It can be great to have this, as anyone can do this on any workbook.

You wouldn’t have to worry about having the workbook saved in a macro-enabled format.

The only thing you need to do is to have the line of code executed so you can instantly unhide all of the workbook’s sheets.

Aside from that, allow us to explain the VBA code that we used in the window to unhide the sheets:

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

This code uses the For Next VBA loop, so you can go through every sheet in the workbook and have the visible property set to TRUE.

The code will end whenever all of the sheets’ visible property changes.

The code above used the colon (:) that is equivalent to the line break.

Even if it appears similar to a single code line, it comes in three parts that two colons separate.

Add Macro to QAT (with One Click)

Whenever you need to unhide the worksheets frequently, there is another excellent way to do this.

In the Personal macro workbook, use the macro code to unhide the sheets and have the icon saved in the Quick Access Toolbar.

Once you complete this one-time process, you can have any sheet unhidden in every workbook just by clicking on a QAT button.

By far, this is the most effective way of unhiding Excel sheets.

It is instrumental whenever you have so many workbooks and hidden sheets that you need to unhide.

Here, the trick is to have the code saved so you can unhide the sheet in your Personal Macro Workbook.

Whenever you have an Excel file opened, the Personal Macro Workbook is what you can always open, but you may not be able to see it.

Make sure to save the macro code to the Personal Macro workbook so that the code will always be available to you.

Once this gets added to the QAT, you can run the macro code with just one click.

Here is the code that you have to include in the Personal Macro Workbook:

Sub UnhideAllSheets()

For Each Sheet In Sheets

    Sheet.Visible = True

Next Sheet

End Sub

Here are the steps on how you can include this code in your Personal Macro Workbook:

  1. At the bottom left of your Excel workbook application is the record macro button. Click on it.
  2. Check out the Record Macro dialog box. Here, have the setting’s Store macro to change to Personal Macro Workbook.
  3. Click on OK so you can start recording the macro.                                                                  
  4. Stop the macro recording by clicking on the Stop macro recording icon at the bottom left of the workbook.
  5. Right-click on the mouse on any sheet tab. After that, click on ‘View Code.’                                                             
  6. Check out the VB Editor. Then, double-click on the Module object that is in the Personal.XLSB workbook.
  7. Have any existing code removed and then copied and pasted.                                               
  8. In the toolbar, click on the Save icon.                                                     
  9. Close the Vb Editor.

With these steps, you can make your Personal Macro Workbook visible in the VB Editor to unhide the sheets by placing the code in it.

By this time, you need to have the code included in the Quick Access Toolbar, as this will allow you to use it from any workbook anytime.

Here are the steps on how you can have this code added to the Quick Access Toolbar:

  1. Select the Customize Quick Access Toolbar icon.                                                                         
  2. Tap on More Commands.                                                                                                                                                     
  3. Check out the dialog box of Excel Options. From the drop-down, click on ‘Choose Commands from.’
  4. Select Macros. It will show all of the macros available in your workbook that includes the ones in PERSONAL.XLSB.
  5. Unhide the sheets by choosing the macro code.                                                                                   
  6. Tap on the Add button.                                                                                             
  7. Click on OK.

With these steps, you can have this macro code added to unhide the sheets in the Quick Access Toolbar. 

By this time, whenever you work on a workbook that has hidden sheets, all you need to do is to click on the QAT’s icon code so it can have all sheets unhidden instantly in just one go.

Unhide the Chosen Sheets Based on the Selection of the User

It can also be possible to use the VBA as it gives flexibility to the user in choosing if they want to hide or unhide a sheet.

You can do this by showing a message box. It asks the user to choose if they want to hide or unhide a sheet.

Once selected, the sheet becomes unhidden, or else it will move to another one.

Here is the code that can help you do this:

Sub UnhideSheetsUserSelection()

For Each sh In ThisWorkbook.Sheets

    If sh.Visible <> True Then

        Result = MsgBox(“Do You Want to Unhide ” & sh.Name, vbYesNo)

        If Result = vbYes Then sh.Visible = True

    End If

Next sh

End Sub

This code will go through every sheet of the workbook to check if it is visible or not.

Whenever it’s hidden, a message box is shown with the worksheet’s name.

For users, you can choose if you want to hide or unhide this sheet.

It would work well whenever you have hidden worksheets, and you want to take the call for every sheet individually.

Take note that there is a regular module where you can save this code in the VB Editor.

You may also have it saved in the Personal Macro Workbook.

Whenever it gets saved in a regular module, and you will have to use it eventually, you must have the workbook saved as a workbook that is macro-enabled in .XLSM format.

Manually Unhide Sheets

If there are only a few hidden worksheets, you can have some or all of these worksheets unhidden manually.

For example, your Excel workbook has ten hidden worksheets.

These are the steps on how you can have these worksheets unhidden manually one at a time:

  1. Use your mouse to right-click on an existing worksheet tab.                                                                                                                   
  2. Click on the option Unhide to open the Unhide dialog box. Here, all of the hidden worksheets are listed.                
  3. In the dialog box Unhide, click on the worksheet you want to unhide. However, you can only choose one at a time.
  4. Finally, click on OK.                                                                                                 

These are the steps that can help in unhiding a selected worksheet.

However, Excel does not have built-in functionality that can immediately unhide all of the hidden worksheets.

There is also no way you can choose to unhide more than one worksheet.

Right now, you have to use the dialog box unhide as this is where you can choose to unhide just one worksheet. 

You cannot unhide worksheets in bulk, but you can hide the majority of worksheets

Whenever you want to have worksheets hidden, you may choose a lot of worksheets at the same time by holding the CTRL key and clicking on the tab name of the worksheet.

After that, right-click the mouse and choose the option Hide.

Despite that, Excel does not have an in-built functionality that can have all hidden worksheets unhidden immediately.

There is also no way you can choose more than one worksheet to unhide.

Right now, you have to use the dialog box unhide as this is where you can choose a worksheet that you want to unhide.

Even if there may be no inbuilt functionality when it comes to unhiding in bulk, it can be easy for you to do this with just a VBA macro code.

Make Sheets That Have A Specific Text in their Name Unhidden

Through VBA, you can have sheets unhidden based on their name.

As an example, your workbook may have sheets with years in the name.

You would want to have all of the ones with the year 2019 unhidden. 

Here is the code that you can use if you want to unhide every sheet that has the text 2019:

Sub UnhideSheetsWithSpecificText()

For Each ws In ThisWorkbook.Worksheets

    If InStr(ws.Name, “2019”) > 0 Then

        ws.Visible = xlSheetVisible

    End If

Next ws

End Sub

Here, it goes through every worksheet in the workbook by using the For Next loop.

The condition If Then will check the worksheet’s name.

If it has the specified text that is 2019, it will have the visible property changes to become visible.

Whenever the specified text is not included in the name, it will be left as is.

You can have this code modified if you want to hide sheets based on the name’s text.

As an example, whenever you want to have all the worksheets that have the text ‘2019’ hidden immediately, this is the code that you can use:

Sub HideSheetsWithSpecificText()

For Each ws In ThisWorkbook.Worksheets

    If InStr(ws.Name, “2019”) > 0 Then

        ws.Visible = xlHidden

    End If

Next ws

End Sub

Take note that you can have this code saved in your VB Editor’s regular module.

You may also have it saved in the Personal Macro Workbook.

Whenever it gets saved in a standard module and use again later, you must keep the workbook.XLSM format or a macro-enabled workbook.

Unhide’ Very Hidden’ Sheets

There are times when even if your workbook has some hidden sheets, you may not be able to unhide it manually.

That is because these sheets are not just hidden as they are ‘very hidden.’

Whenever your workbook has hidden sheets, you can right-click on any tab and see the option ‘Unhide’ sheets.

However, if there are no hidden sheets or are ‘very hidden,’ it may not be possible for you to use this option as it will look greyed out.

With the VBA code mentioned earlier, you can still have these ‘very hidden’ sheets unhidden, just as we previously discussed.

Just have this code copied and pasted in the immediate window.

Then, hit enter so it can instantly unhide every hidden and very hidden sheet.

Do this for every sheet in this workbook. 

Sheets: Sheet.Visible=True: Next Sheet

Use Custom View to Unhide Selected or All Sheets

Here is a less known method whenever you want to have selected or all sheets unhidden quickly.

Excel has the ‘Custom View’ functionality that lets you make and save views that you can immediately return to once you click a button.

As an example, pretend that you have 10 worksheets in your Excel workbook.

You can make a view wherein you can see all of the ten sheets.

You can eventually hide some of the sheets.

If you want to see all of the sheets again, you can do this by choosing the custom view you already saved.

Try not to worry, as you won’t lose any changes you made after creating the custom view.

Custom view only takes you back to your created Excel view.

Whenever the worksheets that were visible when you made the view became hidden, you can unhide the sheets by choosing the custom view.

Custom View has the intended use to allow the users to make various views.

As an example, for analysts, you can create various views of your organization’s different departments.

You can have a particular set of worksheets, columns, rows, or cells visible for one department.

It can be different for another department.

After getting these views, instead of manually changing them, activate the department’s perspective to show every relevant column, row, or worksheet for them.

With these steps, you can make a customized view in Excel:

  1. Start by unhiding all of the worksheets.
  2. Click on the View tab.
  3. Choose Custom Views.                                                                                                            
  4. In the dialog box of Custom Views, choose Add to open the Add view dialog box.    
  5. For this view, enter any name where all of the sheets or selected sheets will be visible.                          
  6. Click on OK.                                                                                                                      

Once you create a view, you can ask Excel to have this view activated anytime.

It can make all of the sheets visible when you made the view.

Here are the steps on how you can activate or show a custom view:

  1. Click on the View tab.
  2. Tap on Custom Views                                                                     
  3. In the dialog box of Custom Views, choose the view you want to appear.
  4. Click on the Show button.                                                                      

With this, it would instantly unhide sheets and show those visible when you created that custom view.

Get to Know How You Can Unhide A Single Sheet or All Sheets in Excel with ExcelMaster

Make yourself an Excel expert with us! Become aware of how you can unhide all sheets or a single sheet in Excel with ExcelMaster NOW!

Leave a Comment