3 Methods to Unhide All Sheets in Excel (& how to hide)

One of the first “tricks” an Excel user learns is to hide and unhide a sheet.

This is an exceptionally useful feature as it allows us to store data in a sheet, such as lists and tables, but keep the user of the workbook from seeing, manipulating, and more importantly, corrupting the information on the hidden sheet.

As with most things in Excel, there is more than one way to hide a sheet or multiple sheets.  One of the easiest methods is to select a sheet (or select multiple sheets using standard Windows CTRL and Shift selection techniques), right-click the sheet tab then select “Hide”.

As an example; suppose you have twelve sheets labeled “January” through “December” and you want to hide all the monthly sheets except “December”.

1. Select the “Jan” sheet
2. Hold down the Shift key
3. Select the “Nov” sheet
4. Right-click on any selected sheet tab
5. Click “Hide”

Unfortunately, unhiding multiple sheets in a single step is not as easy.  If you right-click a sheet tab and select “Unhide”, the proceeding dialog box only allows a single sheet to be selected for the unhide operation.

This means you will have to perform the unhide operation eleven times to restore all the hidden sheets to a visible state.

Never fear, a solution is here (actually, three solutions)

Solution 1 – Create a Custom View

An often-overlooked feature in Excel is the ability to save a custom view.

Custom views can be used to “save” the hidden or visible states of rows and columns.  This is convenient when you wish to show details of data for one printout, but a summarized version of the data in a different printout.

1. Hide the desired rows and/or columns.
2. Click View (tab) -> Workbook Views (group) -> Custom Views -> Add… and give the current configuration a name.

If you change the hidden/visible state of rows and/or columns but then wish to return to the saved configuration, repeat the process (View (tab) -> Workbook Views (group) -> Custom Views), select your saved view then click “Show”.

The screen will immediately return to the desired state.

Custom views also work with the visible/hidden states of worksheets.  If we create a custom view prior to hiding ANY of the sheets (View (tab) -> Workbook Views (group) -> Custom Views -> Add…), we can hide as many sheets as we like.  When it comes time to redisplay all the sheets, we repeat the process and select our “normal” view and click “Show”.

All the sheets have returned.

There is one negative to this process.  Custom views do not work with Data Tables.  The moment you add a Data Table to ANY sheet, the Custom Views feature becomes inoperable.

Because more and more people use Data Tables in their workbooks (and why wouldn’t you?  They’re AMAZING!!!), we need to explore another way of unhiding all hidden worksheets.

Solution 2 – Using the VBA Immediate Window

Right up front, this does not require the use of macro-enabled workbooks.  This technique can be performed in any Excel workbook.

1. Open the Visual Basic Editor by pressing Alt-F11 on the keyboard or right click on any sheet tab and select View Code.

Don’t concern yourself with what you see in the ensuing window; all of that is for another day.

2. Activate the Immediate Window by clicking View -> Immediate Window (or CTRL-G).

Now we will run a macro.  This macro will loop through all the hidden sheets and revert their visibility states to “visible”.  We will use a “For…Each” collection loop to perform this operation.

NOTE:  If you are interested in learning about this command and many other useful things macros can do for you, visit the links at the end of this tutorial.

3. In the Immediate window, type

for each sh in worksheets: sh.visible=true: next sh

(press Enter)

All the sheets have returned to a visible state.

What does that code mean?  Let’s break down the code.

for each sh in worksheets

This establishes a collection (list) of all worksheets and allows us to refer to each sheet individually with the alias “sh”.

sh.visible=true

With the first sheet in the collection, set the visible property to “true”.  This makes the sheet visible to the user.

next sh

This selects the next sheet in the collection and returns to the first statement to repeat the process.

This process will repeat for as many sheets as are in the collection.

If this code is something you will use frequently, you can save the code in a Notepad file and then copy/paste it back into the Immediate Window whenever needed.

Solution 3 – Add a Macro to the Quick Access Toolbar (QAT)

This technique is covered in detail in the Excel VBA course (link below if you are interested in becoming a VBA Powerhouse) but will be summarized here.

If this feature is to be used often across many different workbooks, it’s worth taking the time to set this feature up on the QAT.

We will create a simple macro and store it in a special place in Excel called the Personal Macro Workbook.

Creating the Macro

1. Click the “Record Macro” button on the Status Bar in the lower-left corner of Excel.
   
2. Give the macro a name (“Unhide_All” is a good name.) Macro names cannot contain spaces.
3. Change the “Store macro in:” option from “This Workbook” to “Personal Macro Workbook”.
   
4. Click OK
5. Click the “Stop Recording” button on the Status Bar in the lower-left corner of Excel.
   
6. Open the Visual Basic Editor (Alt-F11).
7. In the Project Explorer panel (upper-left), click the plus-sign next to the entry labeled “VBAProject (PERSONAL.XLSB)”.
8. Click the plus-sign next to the folder labeled “Modules”.
9. Double-click the module named “Module1”.
   
10. Highlight and delete EVERYHTING in the code window (right panel).
    
11. Enter the following code:

Sub Unhide_All()
Dim sh As Worksheet
	for each sh in worksheets: sh.visible=true: next sh
End Sub

Setting up the QAT Macro Launch Button

1. Click the down arrow at the far right of the QAT and select “More Commands…” towards the bottom of the list.
   
2. In the dropdown titled “Choose commands from:” select “Macros”.
   
3. Select the “Unhide_All” macro on the left and click “Add>>” to move the macro to the list on the right.
   
4. Click the “Modify” button to personalize the button icon as well as provide a tooltip. Whatever you write in the “Display name:” filed will appear on the screen when the user hover’s over the launch button on the QAT.
   
5. Click OK.

Whenever you want to invoke the macro to unhide all the hidden sheets, click the unhide macro button on the QAT.

This feature is available for use in all open workbooks.  Because we have updated the Personal Macro Workbook, don’t forget to save the changes to the Personal Macro Workbook when closing Excel.

Additional Resources

Excel VBA For…Each Loop tutorial

Excel VBA – Full Playlist

Free Excel Tool: Unhide Worksheet Utility (select sheets to unhide)

If you don’t have Office 365 and you’d like a free tool that unhides all sheets for you, then this is it!

You can add this tool to your Quick Access Toolbar or to your Excel ribbon by saving it in your Personal Macro Workbook. In this video I show you the steps to do that.

Many Thanks to Daniel Lamarche from Combo Projects for sharing this tool for free with our community members.

Please visit Daniel’s page at:

http://www.comboprojects.com.au/unhide-worksheets-excel/

LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses