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 🙂