SEARCH

How-To Geek

Hide and Unhide Worksheets and Workbooks in Excel 2007 & 2010

Hiding worksheets can be a simple way to protect data in Excel, or just a way reduce the clutter of a some tabs. Here are a couple very easy ways to hide and unhide worksheets and workbooks in Excel 2007 / 2010.

Hiding a Worksheet

Select the Worksheet you’d like to hide by clicking on the tab at the bottom. By holding down the Ctrl key while clicking you can select multiple tabs at one time.

sshot-2

On the Home tab, click on Format, which can be found in the Cells group. Under Visibility,  select Hide & Unhide, then Hide Sheet.

 sshot-3

You can also simply right-click on the tab, and select Hide.

sshot-11 

Your worksheet will no longer be visible, however, the data contained in the worksheet can still be referenced on other worksheets.

 sshot-4

Unhide a Worksheet

To unhide a worksheet, you just do the opposite. On the Home tab, click on Format in the Cells group and then under Visibility,  select Hide & Unhide, then Unhide Sheet.

 sshot-5

Or, you can right-click on any visible tab, and select Unhide.

sshot-12

 

In the Unhide pop up window, select the worksheet to unhide and click “OK.” Note: Although you can hide multiple sheets at once, you can only unhide one sheet at a time.

sshot-6

Very Hidden Mode

While hidden mode is nice, it’s not exactly ultra-secure. If you’d like to pump the security up a notch, there is also Very Hidden mode. To access Very Hidden setting, we’ll have to use the built-in Visual Basic Editor by hitting the Alt + F11 keys.

Select the worksheet you wish to hide from the dropdown list under Properties or by single clicking the worksheet in the VBAProject window. Next, set the Visible property to  2 – xlSheetVeryHidden. Close out of the Visual Basic Editor when finished.

 sshot-14

When the Very Hidden attribute is set on a worksheet, Unhide Sheet is still unavailable from within the Format setting on the Home tab.

sshot-16 

To remove the Very Hidden attribute and display the worksheet again, go back into the Visual Basic Editor by hitting Alt + F11 again and setting the Visible property back to –1 – xlSheetVisible.  Close out of the Editor when finished.

sshot-15

Hiding a Workbook

To hide the entire Workbook, select the View tab, and then click the Hide button.

sshot-8

You’ll see the Workbook has disappeared.

sshot-9

Unhide a Workbook

Select the View tab and click Unhide…

sshot-10

… and your Workbook will be visible again.

sshot-7 

Just a few simple ways to hide and unhide your Excel worksheets and workbooks.

Andrew is a media center geek with some serious Windows skills. He's never far from a WiFi connection or a great cup of coffee.

  • Published 04/6/10

Comments (12)

  1. shivaram

    isnt this was there even in 97, 2000 version also.

  2. Felix Lambiz

    This is great! Thanks a lot. Just out of curiosity, is there a way to show all the hidden sheets at the same time? I tested this on a worksheet that has a lot of sheets and I had to unhide them individually.

  3. Gian

    “show all the hidden sheets at the same time”…Use this code.

    Sub UnhideAll()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = True
    Next ws
    Application.DisplayAlerts = True
    End Sub

  4. kyla

    yall so stupid dum geeks

  5. kyla

    sick nah let me be nice the code is sexy bitch wanna have sex i got big boobs and a fat ass i want to suck dicks and you wanna like me puusy

  6. Jman

    Good stuff to know! Some of us are rather educated and can make a good living with computers. I enjoy my 2008 ZO6 vette. Sorry kyla, you’ll never be there!

  7. Eduardo

    PS3 rules!

  8. Chandrakanth

    Guys, please excuse if am asking for much… i got a file where in i had very hidden 23 sheets. I need to unhide only 4 sheets on a daily basis and need to update the data. In that case, is there any code that you could help with unhiding only those 4 sheets because it is again a teadious one very hiding all the 23 sheets again… Hehehe!

    Tx in Advance!

  9. splympton

    I was just upgraded at work to 2010. I have a spreadsheet that I keep a hidden sheet with a pivot table. At the end of each month I retrieve the hidden sheet. Since updating to 2010 I cannot retrieve it. I read this post and found the instructions to unhide a very hidden sheet with Visual Basic. However in VBA it was showing as being hidden at the regular level where I should have been able to unhide the normal way. When I tried to unhide using VBA it got an error message “Unable to set the visual properties of the worksheet class”

    I am in desparate need of the information on the hidden sheet as I have a time limitation. Any ideas of what is the problem?

  10. eccollet

    I have to same issue as splympton!!!! Please help!

  11. Yoli

    This may be obvious to some folks but I’m having a hard time figuing out how to hide comments in in Excel 2007. Your help is appreciated.

  12. steve R.

    This is a great site ! Very simple, yet provides more ‘advanced’ solutions as well… T H A N K S !!!!

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!