Do you want to hide sheets for protection or unhide hidden worksheets for visible in Excel 2016/2013/2010/2007? Here 3 effective methods would be talked about in this passage and you can learn how to hide or unhide worksheets in Excel with them.

  1. GUI – Graphical User Interface
  2. VBA – Visual Basic Application
  3. Custom View

Method 1: Hide/Unhide Sheets with GUI

It is easy to hide sheets in Excel, but probably unhide multiple worksheets at once would be not so easy to do, if you cannot find an effective way. But as long as you are familiar with MS Excel, you would know the common Hide and Unhide Sheet command in Excel.

In Excel 2007/2010/2013/2016:

On the Home tab, choose “Format > Hide and Unhide” and select “Hide Sheet“. Or right click the worksheet tab and choose “Hide” option, you could hide single one sheet. Then do the same to hide every worksheet you wish.

hide an excel sheet with command

If you want to hide multiple worksheets at once, just need to make full use of “Ctrl” or “Shift” button here, then all of selected worksheets would be hidden at a time.

1. Hold on Ctrl button and choose all the worksheets tabs one by one you want to hide, and choose Hide option in right menu.

2. Click on the first worksheet tab and hold down the Shift key as you click on the last worksheet tab, and then do like above when you hide single sheet.

hide multiple sheets with GUI

Later, when you need to unhide all the hidden worksheets, you would just have to choose Unhide instead of Hide option with the same progress one by one.

unhide multiple sheets with GUI

select hidden sheet to unhide

 

Method 2: Hide/Unhide Worksheets with VBA

With “Visible” attribute in VBA editor, you can hide and unhide worksheets in Excel through attribute changes.

1. Open VBA editor with Alt + F11 keyboard shortcut.

2. Select the sheet you want to hide in Project window where lists all hidden and visible sheets in your workbook.

3. Change the Visible attribute to “xlSheetVeryHidden” in the lower left pane for selected worksheet.

hide or unhide a sheet with VBA

Close the VBA editor and worksheet becomes hidden. Then if you want to unhide the sheet, open VBA editor again and change the Visible attribute back to “xlSheetVisible“.

But if you want to unhide multiple worksheets at once in VBA editor, please take following VBA codes instead of changing attribute.

1. Press Ctrl + G to open the immediate window in VBA Editor Window.

2. Paste the code and press Enter to make hidden worksheets become visible.

  • Hide sheets: For Each ws In Sheets:ws.Visible=False:Next
  • Unhide sheets: For Each ws In Sheets:ws.Visible=True:Next

hide or unhide multiple sheets with VBA code

 

Method 3: Unhide Multiple Sheets with Custom View

Besides VBA code, Custom View feature provided in Excel could also easily unhide a bundle of worksheets at a time with several clicks.

Tips: This method only works when you have created a custom view before worksheets hidden.

Steps to create a custom view of excel worksheets:

1. Click View tab, and choose Custom Views in Workbook Views group.

choose custom view feature in excel

2. Click Add button in Custom Views window and type a name to create a view of all worksheets in Excel workbook. Click OK to finish creation.

choose to add an original view of excel workbook

give original view a name

Then after you successfully hide worksheets and when you want to unhide them, click View > Custom Views and select the custom view you have added in Custom Views dialog before worksheet hidden. Then click Show button to unhide all hidden worksheets.

choose custom views to unhide excel sheets

select to show original custom view

successfully show multiple hidden sheets with custom view

Note: Hiding sheets is one of ways to protect Excel sheet besides password. If you think it is not the way you like, please protect multiple sheets with other methods.

Related Articles: