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.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

4 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.

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.