One of the first tricks Excel users learn is hiding and unhiding sheets. This feature is super useful for keeping your data safe from accidental changes.

How to Hide Sheets in Excel

Hiding sheets in Excel is simple. Follow these steps:

  • Select the sheet you want to hide. To select multiple sheets, hold down the CTRL or Shift key.
  • Right-click on the selected sheet tab.
  • Click “Hide.”

For example, if you have sheets named “January” to “December” and want to hide all except “December”:

  • Select the “January” sheet.
  • Hold down the Shift key.
  • Select the “November” sheet.
  • Right-click any selected sheet tab.
  • Click “Hide.”

How to Unhide Sheets in Excel

Unhiding multiple sheets isn’t as straightforward. If you right-click a sheet tab and choose “Unhide,” you can only unhide one sheet at a time. But don’t worry, there are easier ways to unhide all tabs in Excel:

  1. Using VBA Code
  2. Using the Immediate Window
  3. Using a Macro

Solution 1: Create a Custom View

Did you know you can save custom views in Excel? This handy feature lets you save the hidden or visible states of rows, columns, and sheets.

It’s perfect for showing detailed data in one printout and a summary in another.

Steps to Create a Custom View

  1. Hide the rows and columns you want.
  2. Click the View tab.
  3. In the Workbook Views group, click Custom Views.
  4. Click Add… and name your view.

To return to this view later:

  1. Click the View tab.
  2. Go to Workbook Views and select Custom Views.
  3. Choose your saved view and click Show.

Custom Views for Sheets

You can also save the visible/hidden states of sheets:

  1. Before hiding any sheets, go to the View tab.
  2. In Workbook Views, click Custom Views and Add… to save your view.

To unhide sheets later, repeat the steps and select your “normal” view. Click Show, and all sheets will reappear.

Limitations of Custom Views

Custom views don’t work with Data Tables. If you add a Data Table to any sheet, the Custom Views feature stops working. Since many people use Data Tables, let’s look at other ways to unhide all sheets.

Featured Course

Unlock Excel VBA & Excel Macros

Automate ANYTHING you need done in Excel with VBA and macros. Go from Beginner to VBA Expert and design automations with confidence.
Learn More
Excel vba and macros course cover

Solution 2: Using the VBA Immediate Window

You don’t need a macro-enabled workbook for this technique. You can do it in any Excel workbook.

Steps to Unhide Sheets Using the VBA Immediate Window

  • Open the Visual Basic Editor:
    • Press Alt + F11.
    • Or right-click any sheet tab and select View Code.
  • Activate the Immediate Window:
    • Click View > Immediate Window.
    • Or press Ctrl + G.
  • Run the Macro:
    • Type the following code in the Immediate Window:
for each sh in worksheets: sh.visible=true: next sh
  • Press Enter.

All your hidden sheets will become visible again.

Explanation of the Code

  • for each sh in worksheets: This creates a list of all worksheets and allows us to refer to each one as “sh”.
  • sh.visible=true: This makes the current sheet visible.
  • next sh: This moves to the next sheet in the list and repeats the process.

If you use this code often, save it in a Notepad file for easy copying and pasting.

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

If you often need to unhide sheets in different workbooks, adding a macro to the Quick Access Toolbar (QAT) is a great solution. Here’s how to do it.

Creating the Macro

1. Record the Macro:

  • Click the “Record Macro” button on the Status Bar in the lower-left corner of Excel.
  • Name the macro (e.g., “Unhide_All”). Macro names can’t have spaces.
  • Change “Store macro in:” to “Personal Macro Workbook”.
  • Click OK.
  • Click the “Stop Recording” button on the Status Bar.

2. Edit the Macro:

  • Press Alt + F11 to open the Visual Basic Editor.
  • In the Project Explorer (upper-left), click the plus sign next to “VBAProject (PERSONAL.XLSB)”.
  • Click the plus sign next to “Modules”.
  • Double-click “Module1”.
  • Delete everything in the code window and enter:
Sub Unhide_All()
    Dim sh As Worksheet
    For Each sh In Worksheets
        sh.Visible = True
    Next sh
End Sub

Setting up the QAT Macro Button

1. Add the Macro to QAT:

  • Click the down arrow at the far right of the QAT and select “More Commands…”.
  • In “Choose commands from:”, select “Macros”.
  • Select “Unhide_All” and click “Add>>”.
  • Click “Modify” to personalize the button icon and tooltip. The “Display name” will appear when you hover over the button.

2. Save the Personal Macro Workbook:

  • Click OK.
  • Remember to save changes to the Personal Macro Workbook when closing Excel.

Using the Macro

To unhide all hidden sheets, click the unhide macro button on the QAT. This feature will be available in all open workbooks.

For more tips, check out the Excel VBA For…Each Loop tutorial or the full Excel VBA Masterclass.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.