Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

Unhiding All Worksheets Within an Excel Workbook

by
May 16th 2016
Share this content

Although you can quickly hide as many worksheets within a workbook as you like, we’re still limited to unhiding individual worksheets one at a time – unless you’re aware of Excel’s Custom Views feature. Fortunately, there is another way to avoid the agony of manually unhiding worksheets one at a time.

In this article I’ll show you how to use a single line of programming code to unhide the worksheets. Programming code in Excel is often referred to as macros. In this case we’re not creating a permanent macro, but rather typing a line of code to run on demand.

To hide a worksheet, as shown in Figure 1, you can choose Format, Hide and Unhide, and then Hide Sheet on Excel’s Home menu. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide.

Alternatively, you can also hide multiple sheets at once by holding down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. Yet another technique is to select a group of sheets by clicking on the first worksheet tab and then holding down the Shift key as you click on the last worksheet tab within the group. You’ll then hide the sheets as discussed above.

UnhideWorksheets1

Figure 1: There are a variety of ways to hide worksheets in Excel.

While Excel makes it easy to hide worksheets within a workbook, you can only unhide multiple worksheets manually, one-by-one. To unhide a single worksheet, right-click on a visible worksheet’s tab and select Unhide from the context menu.

Alternatively, on Excel’s Home menu choose Format, Hide & Unhide, and then Unhide Sheet. If you carry that task out once or twice you’ll quickly see why right-clicking on a visible tab is far more efficient. Either set of actions will display the Unhide dialog box, which lists all currently hidden worksheets, from which you can choose one worksheet at a time.

UnhideWorksheets2

Figure 2: Unfortunately, you must unhide worksheets one at a time.

You probably don’t have the time or inclination to unhide more than a couple of worksheets in this fashion, so instead we’ll use a bit of programming code to instantly display all worksheets at once:

  1. As illustrated in Figure 3, press Alt-F11 on your keyboard to display Excel’s Visual Basic Editor. Mac users should press Fn-Alt-F11. Although it looks like a separate program, it’s a hidden aspect of Excel that most users haven’t seen before.
  2. Click the View menu within the Visual Basic Editor.
  3. Select Immediate Window from the View menu, or press Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).
  4. At this point the Immediate window will appear on-screen. This is a special area where any programming code you type will be executed immediately, hence the name.
  5. Type the following line of programming code into the Immediate window exactly as written below, and press Enter.

For Each s In Sheets: s.Visible = True: Next

The downside of the Immediate Window is you don’t get any direct feedback if your programming code worked, other than seeing that all of your worksheets are now visible within the workbook. Error prompts will appear if you press Enter when the line of code is either incomplete or contains typographical errors.

You may also encounter an error if the workbook is protected by way of the Protect Workbook command on Excel’s Review menu.

  1. You can safely exit the Visual Basic Editor once you’ve run the line of code.

UnhideWorksheets3

Figure 3: A single line of code in the Immediate Window will unhide all worksheets in the workbook.

The aforementioned line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you want a little insight as to what the macro is doing:

  • For Each sets up a loop.
  • s is a variable that serves as a temporary placeholder for a worksheet to be acted on.
  • Sheets is a collection of all worksheets within the workbook. This actually includes other types of sheets as well, meaning Chart Sheets and Macro Worksheets. We could be more specific and use the Worksheets collection instead, but Sheets results in less typing.
  • Each worksheet has a Visible property, and in this case we’re setting it to True. The setting gets set to False when you hide a worksheet.
  • Next simply instructs Excel to skip to the next worksheet in succession, until all have been processed.

If you were to store this within a formal macro, the code might take this form:

            For each s in Sheets

                        s.Visible

            Next

The Immediate Window only allows us to execute a single line of code at a time, so the colons allow us to string three lines of code together into a single line that can be executed.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.