Go Undercover With Hidden Excel Worksheets

hiddenAn Excel workbook certainly isn’t Fort Knox, and the information you store there isn’t too secure. If someone opens your Excel workbook, and is determined to see everything in there, they’ll probably be able to.

However, if your goal is simply to make a workbook easier for people to use, you can hide some of the worksheets, so users don’t accidentally change their contents.

For example, if your data entry worksheet has data validation drop downs, you can store the lists on a different sheet, and hide that sheet.

Hide an Excel Worksheet

To quickly hide a worksheet in Excel 2007, right-click on the sheet tab, and click Hide.

SheetHide01

If you’re using an earlier version of Excel, activate the sheet that you want to hide. Then, click the Format menu, then click Sheet, and click Hide.

SheetHide05

Show an Excel Worksheet

To show the hidden sheet again, right-click any sheet tab, then click Unhide. (In earlier versions of Excel, click the Format menu, then click Sheet, and click Unhide.)

SheetHide02

In the Unhide dialog box, click on a sheet name, and click OK.

SheetHide03

Really, Really Hide an Excel Worksheet

If you want to hide a worksheet a little better, you can use a special technique that keeps it from appearing in the Unhide list.

  • First, to open the Visual Basic Editor (VBE), press the Alt + F11 keys.
  • In the Project Explorer, at the left of the VBE window, locate your workbook.
  • In the Microsoft Excel Objects folder for your workbook, click on the sheet that you want to hide
  • If the Properties window is not showing, press the F4 key to open it
  • At the bottom of the Properties window, in the Visible property, change the setting to -2 – xlSheetVeryHidden
  • Close the VBE and return to Excel

SheetHide04

The sheet is now hidden, and its name won’t appear on the Unhide list.

Watch the Excel Hidden Sheets Video

To see the steps for hiding Excel worksheets, watch this short Excel video tutorial.

______________

You may also like...

2 Responses

  1. Ed says:

    Hi Debra,

    Why stop there?

    Select the sheet you wish to hide. Hide all columns except X, hide all rows except 40.

    Open the VBE.
    Change the (Name) property to x
    Change EnableSelection to xlNoSelection
    Change the Name property to a single space
    Change the ScrollArea to $A$1
    Change the Visible property to xlVeryHidden

    Type the following line into the immeadiate pane and press Enter
    x.cells(2,2).select

    And don’ forget to add this to the code for x

    Private Sub Worksheet_Activate()
    Application.Speech.Speak “evil laugh ha ha ha ha”
    End Sub

  2. Geez, Ed, now everybody will be doing that! ;-) But I like it.

Leave a Reply to Ed Cancel reply

Your email address will not be published. Required fields are marked *