Unhide sheets in Excel: show multiple or all sheets at once

The tutorial explains how to unhide worksheets in Excel 2016, 2013, 2010 and lower. You will learn how to quickly unhide worksheet by right-clicking and how to unhide all sheets at a time with VBA code.

Imagine this: you open a worksheet and notice that some formulas refer to another worksheet. You look at the sheet tabs, but the referenced spreadsheet is not there! You try to create a new sheet with the same name, but Excel tells you it already exists. What does that all mean? Simply, the worksheet is hidden. How to view hidden sheets in Excel? Obviously, you have to unhide them. This can be done manually by using Excel's Unhide command or automatically with VBA. This tutorial will teach you both methods.

How to unhide sheets in Excel

If you want to see just one or two hidden sheets, here's how you can quickly unhide them:

  1. In your Excel workbook, right-click any sheet tab and select Unhide… from the context menu.
  2. In the Unhide box, select the hidden sheet you want to display and click OK (or double-click the sheet name). Done!

Unhide sheets in Excel via the right-click menu.

Besides the right-click contextual menu, the Unhide dialog can be accessed from the ribbon:

  • In Excel 2003 and earlier, click the Format menu, and then click Sheet > Unhide.
  • In Excel 2016, Excel 2013, Excel 2010 and Excel 2007, go to the Home tab > Cells group, and click the Format Under Visibility, point to Hide & Unhide, and then click Unhide Sheet…

Unhide worksheet using the ribbon.

Note. Excel's Unhide option only allows you to select one sheet at a time. To unhide multiple sheets, you will have to repeat the above steps for each worksheet individually or you can unhide all sheets in one go by using the below macros.

How to unhide sheets in Excel with VBA

In situations when you have multiple hidden worksheets, unhiding them one-by-one might be very time consuming, especially if you'd like to unhide all the sheets in your workbook. Fortunately, you can automate the process with one of the following macros.

How to unhide all sheets in Excel

This small macro makes all hidden sheets in an active workbook visible at once, without disturbing you with any notifications.

Sub Unhide_All_Sheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub

Show all hidden sheets and display their count

Like the above one, this macro also displays all hidden sheets in a workbook. The difference is that upon completion, it shows a dialog box informing the user how many sheets have been unhidden:
Unhide all sheets in Excel with VBA

Sub Unhide_All_Sheets_Count() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets If wks.Visible <> xlSheetVisible Then wks.Visible = xlSheetVisible count = count + 1 End If Next wks If count > 0 Then MsgBox count &amp; " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets" End If End Sub

Unhide multiple sheets that you select

If you'd rather not unhide all worksheets at once, but only those that the user explicitly agrees to make visible, then have the macro ask about each hidden sheet individually, like this:
Unhide multiple sheets with VBA

Sub Unhide_Selected_Sheets() Dim wks As Worksheet Dim MsgResult As VbMsgBoxResult For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetHidden Then MsgResult = MsgBox("Unhide sheet " &amp; wks.Name &amp; "?", vbYesNo, "Unhiding worksheets") If MsgResult = vbYes Then wks.Visible = xlSheetVisible End If Next End Sub

Unhide worksheets with a specific word in the sheet name

In situations when you only want to unhide sheets containing certain text in the their names, add an IF statement to the macro that will check the name of each hidden worksheet and unhide only those sheets that contain the text you specify.

In this example, we unhide sheets with the word "report" in the name. The macro will display sheets such as Report, Report 1, July report, and the like.

To unhide worksheets whose names contain some other word, replace "report" in the following code with your own text.

Sub Unhide_Sheets_Contain() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets If (wks.Visible <> xlSheetVisible) And (InStr(wks.Name, "report") > 0) Then wks.Visible = xlSheetVisible count = count + 1 End If Next wks If count > 0 Then MsgBox count &amp; " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets with the specified name have been found.", vbOKOnly, "Unhiding worksheets" End If End Sub

How to use the macros to unhide sheets in Excel

To use the macros in your worksheet, you can either copy/paste the code in the Visual Basic Editor or download the workbook with the macros and run them from there.

How to insert the macro in your workbook

You can add any of the above macros to your workbook in this way:

  1. Open the workbook with hidden sheets.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the code in the Code window.
  5. Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Download the workbook with the macros

Alternatively, you can download our sample workbook to unhide sheets in Excel that contains all of the macros discussed in this tutorial:

  • Unhide_All_Sheets - unhide all worksheets in an active workbook momentarily and silently.
  • Unhide_All_Sheets_Count­ - show all hidden sheets along with their count.
  • Unhide_Selected_Sheets - display hidden sheets you choose to unhide.
  • Unhide_Sheets_Contain - unhide worksheets whose names contain a specific word or text.

To run the macros in your Excel, you do the following:

  1. Open the downloaded workbook and enable the macros if prompted.
  2. Open your own workbook in which you want to see hidden sheets.
  3. In your workbook, press Alt + F8, select the desired macro, and click Run.

For example, to unhide all sheets in your Excel file and display the hidden sheets count, you run this macro:
A macro to unhide all sheets in Excel

How to show hidden sheets in Excel by creating a custom view

Apart from macros, the tedium of showing hidden worksheets one at a time can be overcome by creating a custom view. If you are not familiar with this Excel feature, you can think of a custom view as a snapshot of your workbook settings that can be applied at any moment in a mouse click. This method is best to be used in the very beginning of your work, when none of the sheets is hidden yet.

So, what we are going to do now is create the Show All Sheets custom view. Here's how:

  1. Make sure all the spreadsheets in your workbook are visible. This tip shows how to quickly check workbook for hidden sheets.
  2. Go to the View tab > Workbook Views group, and click the Custom Views button.
    Custom Views in Excel
  3. The Custom View dialog box will show up, and you click Add…
    Adding a new custom view
  4. in the Add View dialog box, type the name for your custom view, for example ShowAllSheets, and click OK.
    Creating ShowAllSheets view

You can now hide as many worksheets as you want, and when you wish to make them visible again, you click the Custom Views button, select the ShowAllSheet view and click Show, or simply double-click the view.
Use a custom view to show all hidden sheets at once.

That's it! All hidden sheets will be shown immediately.

How to check if a workbook contains any hidden sheets

The fastest way to detect hidden sheets in Excel is this: right-click any sheet tab and see if the Hide… command is enabled or not. If it is enabled, click it and see which sheets are hidden. If it is disabled (greyed out), the workbook does not contain hidden sheets.
Check if a workbook contains any hidden sheets.

Note. This method does not show very hidden sheets. The only way to view such sheets is to unhide them with VBA.

Cannot unhide sheets in Excel - problems and solutions

If you are unable to unhide certain sheets in your Excel, the following troubleshooting tips may shed some light why.

1. The workbook is protected

It is not possible to hide or unhide sheets if the workbook structure has been protected (should not be confused with workbook-level password encryption or worksheet protection). To check this, go to the Review tab > Changes group and have a look at the Protect Workbook button. If this button is highlighted in green, the workbook is protected. To unprotect it, click the Protect Workbook button, type the password if prompted and save the workbook. For more information, please see How to unlock a protected workbook in Excel.

2. Worksheets are very hidden

If your worksheets are hidden by VBA code that makes them very hidden (assigns the xlSheetVeryHidden property), such worksheets cannot be displayed by using the Unhide command. To unhide very hidden sheets, you need to change the property from xlSheetVeryHidden to xlSheetVisible from within the Visual Basic Editor or run this VBA code.

3. There are no hidden sheets in the workbook

If the Unhide command is grayed out both on the ribbon and in right-click menu, that means there is not a single hidden sheet in your workbook :)

This is how you unhide sheets in Excel. If you are curious to know how to hide or unhide other objects such as rows, columns or formulas, you will find full details in the below articles. I thank you for reading and hope to see you on our blog next week!

Available downloads

Macros to unhide worksheets in Excel (.xlsm file)

16 comments

  1. No, I have no comments on the above issues.

    Actually I have an another issue and facing problem to operate. I have an excel workbook along with eleven no’s of excel sheets including an excel sheet named DASH BOARD having with ten no’s of command button by which I want to open/unhide the schedule excel sheet means which sheet I want to open for perform and after that when I close the said work book, all sheets except DASH BOARD work sheet will be in hide situation, and I want to unhide the schedule worksheet by using command button again when I open the said workbook.

  2. How do I edit the unhide/hide box? I have multiple sheets hidden and they are not in order. Would like to put in alphabetical.

  3. Super helpful. I forgot that the my file's workbook structure was protected and I panicked til I read it here. Thank you!

  4. you are a life saver! hidden the sheets so well i could not retrieve them

  5. Thank you with that Very Hidden Sheet trick XD

  6. thanks, really helps me a lot !!!

  7. Great timesavers. Thanks!

  8. Based on the macro that you describe "Unhide multiple sheets that you select" Is there an actual way of unhiding sheets based on range? I have dates in tabs named for example, 01010-09112019 and would like to unhide sheets from dates 07012019-09102019? It would be really great to unhide "Selected sheets" based on range.

    Thank you,

    G

  9. Wow! This is super!
    Thank you for giving us so many options. I have created a macro to unhide all sheets all at once.

  10. Hi Svetlana,
    I shortly sent you a message in response to your email about receiving a error while trying to use your VBA code to unhide the worksheets in the Excel file that i have. Unfortunately, I do not know what happend I cannot see any of the previous messages.

    Thanks,
    Alireza

  11. thanks a lot! exactly what i was looking for! I stored the macro (unhide_all_sheets) in my personal macro workbook (that's always open in the background) to ensure I can use it on any workbook that comes along..! :-)

    1. btw, this works fine on Office 2016 for Mac!

  12. OUTSTANDING tutorial. Super helpful. Efficient, concise, - Elegant.
    Thank you!

  13. Thanks!
    This was very helpful!

  14. Hi Svetlana,

    I have another case, I can see the sheet in an excel workbook but when i open in VBA i can't see it. It sound in this sheet that is not visibile when i open the vba there is macro registered. How can I do it to open in in vba also? thank u

    1. Que?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)