Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Unhiding Multiple Worksheets.

Unhiding Multiple Worksheets

Written by Allen Wyatt (last updated October 19, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets can be a different story, however.

If you are using Excel 2021 or the version of Excel in Microsoft 365, you can unhide multiple worksheets at once. All you need to do is right-click on a worksheet tab and choose the Unhide option from the resulting Context menu. This displays the Unhide dialog box, which lists all the hidden worksheets. At this point you can create a selection set of the worksheets you want to unhide—hold down the Ctrl key as you click on each worksheet you want to unhide, or you can click on the first worksheet and then hold the Shift key down as you click on the last. When you've selected all the worksheets you want to unhide, click the OK button and you are good to go.

This capability is not available in older versions of Excel, however. In those, you can only unhide one worksheet at a time. If you have many worksheets you want to unhide, this can be very tedious.

The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook:

Sub UnhideAllSheets()
    Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
        wsSheet.Visible = xlSheetVisible
    Next wsSheet
End Sub

If you would rather not unhide all the worksheets at once, you can cause the macro to ask about each hidden worksheet and then unhide each that you agree to unhide. The following macro will handle this task:

Sub UnhideSomeSheets()
    Dim sSheetName As String
    Dim sMessage As String
    Dim Msgres As VbMsgBoxResult

    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible = xlSheetHidden Then
            sSheetName = wsSheet.Name
            sMessage = "Unhide the following sheet?" _
              & vbNewLine & sSheetName
            Msgres = MsgBox(sMessage, vbYesNo)
            If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible
        End If
    Next wsSheet
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9636) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Unhiding Multiple Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Custom Labels

There is a whole passel of labels pre-defined in Word. You are not limited to this passel, however; Word allows you to ...

Discover More

Adding Phrases to the Grammar Checker

Word's grammar checker dutifully tries to mark all the questionable grammar in your sentences. If you are tired of a ...

Discover More

Jumping to the Ends of Table Rows

Need to jump from one end of a table row to another? Word provides a couple of handy shortcuts that can make this type of ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Copying a Worksheet

Need to make a copy of one of your worksheets? Excel provides a few different ways you can accomplish the task.

Discover More

Quickly Copying Worksheets

Excel provides a little-known way to copy worksheets simply by clicking and dragging. Here's how to do it.

Discover More

Specifying the Number of Worksheets in a New Workbook

By default, a new Excel workbook contains three blank worksheets. You can (and should) configure Excel to whatever number ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2024-10-25 10:26:32

Tom

Thanks for the information, I accomplish the hide/unhide by using VBA & Macro Buttons. I have a quick access macro that hides a Sheet upon command and I have a menu sheet with multiple macro buttons the will unhide a single sheet or combinations of sheets. This works really well for me.
I so enjoy your email with tips and tricks....

Tom


2024-10-19 11:41:00

J. Woolley

The Tip's macros ignore chart sheets. Here is an improved version of its second macro that includes them.

Sub UnhideSomeSheets2()
    Dim sh As Object, msg As String
    For Each sh In ActiveWorkbook.Sheets
        If sh.Visible = xlSheetHidden Then
            msg = "Unhide the following sheet?" & vbNewLine & sh.Name
            If MsgBox(msg, vbYesNo) = vbYes Then sh.Visible = xlSheetVisible
        End If
    Next sh
End Sub

Notice very hidden sheets (xlSheetVeryHidden) are ignored.
For related discussion, see https://excelribbon.tips.net/T013589_Finding_a_Worksheet_to_Unhide_among_Many_Hidden_Sheets.html


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.