Hide and Unhide Multiple Sheets

Hiding a single worksheet from view is fairly straightforward … you can right-click the tab and select Hide. Hiding multiple worksheets at the same time is also fairly straightforward. You select multiple sheets by holding down Ctrl or Shift, and then right-click and select Hide. It is also fairly straightforward to Unhide a single sheet. You right-click any sheet tab and select Unhide. Then, in the resulting Unhide dialog you select the sheet you want to show and click OK. However … and this is the point of this post … unhiding multiple sheets has historically been a manual process because you had to unhide them one by one. Or, you could use VBA. But, we now have the ability to Unhide multiple sheets at the same time WITHOUT needing any code 🙂

Objective

Before we get into the mechanics, let’s be sure we are on the same page and clear about our goal here.

Let’s say we have several worksheets in our workbook. There are a bunch of sheet tabs at the bottom of our Excel window, like this:

Let’s say we hide Sheet2, Sheet3, and Sheet4 by selecting them, right-clicking a tab, and selecting Hide. We see the remaining Sheet1 and Sheet5 tabs like this:

Now, we would like to unhide Sheet2, Sheet3, and Sheet4 so we can see them again. Historically, this would be accomplished one sheet at a time. Or, if you knew how to program macros you could use a VBA loop.

However, after a recent update it is easy to Unhide all of the hidden sheets at once.

How To

As with unhiding a single sheet, we right-click any visible sheet’s tab and select Unhide.

This reveals the Unhide dialog box, as shown below.

Historically, this dialog did NOT allow you to select multiple sheets from the list. You could only pick a single sheet.

But, after a recent update, this dialog now permits you to select multiple sheets 🙂

Note: depending on your version of Excel, you may or may not have this capability. It is available at the time of this writing to Microsoft 365 subscribers for Windows (version 16.0.13525 or greater), Excel for the Web, and some versions of Excel for Mac (version 16.45 or greater).

Hold down Ctrl or Shift (or CMD on Mac), and then select the sheets you’d like to unhide:

YAY!!!!!!!!!!!!!!!!!

Now we click OK, and bam:

In the scheme of things, this is a fairly minor update … however … it provides an efficiency boost when your workbooks have several hidden sheets that need to be updated periodically.

Also, this enhancement is fairly easy to miss. If you’ve been working in Excel a long time, you are conditioned … you already know that this dialog doesn’t permit you to select multiple sheets, so you gave up trying a long time ago.

So, I wanted to write about this enhancement.

Even small efficiencies are efficiencies … and they can add up over time 🙂

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

9 Comments

  1. jim on April 7, 2021 at 9:21 am

    yes, it’s a very small improvement
    but it was very irritating and is loooong overdue (bad design in the 1st place)
    I’ll still keep the “unhide all” shortcut in my personal macros 😉

  2. Kelly Zaia on April 7, 2021 at 9:56 am

    This is a helpful tip with multiple tab spreadsheets that turn into financial reports. Most of the tabs are hidden so they are not Viewed and incorporated into the final pdf product.

  3. Nasr Laiwalla on April 8, 2021 at 2:27 am

    I use my spreadsheet to keep record of test data from various tests for future reference And quick access. The spreadsheet has many tabs and it is becoming difficult navigate. I did not know the tabs could be hidden and unbidden. This tab will be very helpful and will make navigating the spreadsheet much easier.

  4. Julia on December 7, 2021 at 8:47 am

    I had a very heavy document because of all these hidden sheet, so I had to unhide them all to delete and reorganize.

    This multiple select its still not available on my excel despite it being Excel for Microsoft 365 MSO (16.0.13801.21050)

    So, for this I used the Alt function just using keyboard, my Excel is in English so its like this:
    ALT -> H -> O -> U -> H -> enter

    Hope it helps someone else

  5. Robert Demaree on February 25, 2022 at 8:10 am

    What EXCEL are you working in??

    MY version of Excel laughs at “MULTIPLE SHEET SELECTION.”

    I have to do it the old reliable way–ONE SHEET, ONE UNHIDE.

    • Jeff Lenning on February 25, 2022 at 8:14 am

      Hi Robert! I’m using Excel for Windows 365. (Note: the legacy versions of Excel don’t have that enhancement.)

      • Robert Demaree on February 25, 2022 at 10:41 am

        Rats!
        Oh well, thanks!

  6. Jodie on August 24, 2022 at 5:33 pm

    This is still not working for me and I’m on version 16.64 for Mac.

Leave a Comment