Software

Hide Excel sheets - really hide them

If you want to make sure a hidden worksheet stays hidden, don't rely on the easily reversed Hide command. For better security, try this little VBA trick instead.
You can hide an Excel sheet, but users can unhide it just as quickly. If you want a more securely hidden sheet, hide it in the Visual Basic Editor (VBE). Fortunately, the technique is simple for you -- and users won't suspect a thing. First, let's look at the traditional method for hiding a sheet:

  1. Open a new workbook.
  2. Click Sheet2's tab to select it.
  3. From the Format menu, choose Sheet.
  4. From the resulting submenu, choose Hide. Excel hides Sheeet2 and its tab.

To unhide Sheet2, you'd choose Sheet from the Format menu and select Unhide. Then, you'd select Sheet2 in the Unhide dialog box and click OK. (For now, don't unhide Sheet2.) Even users with just a little experience might find this dialog box and unhide a sheet. Excel has a more secure setting known as "very hidden." A very hidden worksheet can't be unhidden using the Excel user interface because it doesn't appear in the Unhide dialog box. The average user won't even know the sheet exists, let alone know how to unhide it. You'll need the VBE to apply this property as follows:

  1. Switch to Excel's VBE (press [Alt]+[F11]).
  2. In the Project Explorer, select the sheet you want to hide. This time, hide Sheet3.
  3. In the Properties window, select 2 - xlSheetVeryHidden from the Visible property's drop-down list. When you do, Excel applies the property and then selects the first sheet, Sheet1, which is a bit distracting. That behavior is normal, so don't let it bother you.

When you return to Excel, Sheet3 isn't visible. Nor does Excel list Sheet3 in the Unhide dialog box. In fact, Excel dims the Unhide command if the only sheets hidden are very hidden sheets. Sheet2 is visible because (earlier) we used the Hide command from the Format menu to hide it. The result is a very stealthy sheet, but it's still vulnerable to savvy users who know the technique. To unhide Sheet3, return to the VBE and choose -1 – xlSheetVisible. (That's a negative 1, which denotes the constant's value.)

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

22 comments
evelynm
evelynm

there are dozens of worksheets i need to deal with, and i would like to hide all sheets or some inactive sheets with one click. Can any one help me with the issue?

john.caulfield
john.caulfield

I get it but why not just hide the sheet as usual and then use tools Protection Protect Workbook and Protect the stucture of the file. Now you can't hide or unhide, reorder sheets etc

melissatutors
melissatutors

This article arrived right when I needed it for an Excel project I'm working on. I had been thinking that the hide sheet function was inadequate. Thanks!

Paul90
Paul90

There is an easier way. First, change the tab to something other than "Sheet n", so that no-one can blindly guess the name. Then hide the sheet as normal using , then, when it is hidden, use and set a password. Without the password, you cannot open up the sheet, and the "Unhide" command is greyed out.

skiddo24
skiddo24

When I want to hide a sheet I use Format -> Sheet -> Hide and then I password protect the workbook which makes "Unhide" unavailable (greyed-out)

mistercrowley
mistercrowley

You do realize that this method does not apply to Excel 2007...

AK Norm
AK Norm

I'm running Excel 2002. The "really hidden" trick works, but I can't unhide the sheet. In the VBE, it does not list the hidden sheet, or any other sheet, except whichever one was selected at the time I went to VBE. Any ideas why the hidden sheet doesn't appear in VBE?

Rande
Rande

I assume this only works in the 2007 version of Excel as the xlSheetVeryHidden property does not exist in 2003. Am I correct?

doncobb
doncobb

When you want to unhide, you need to View-Object Browser to be able to select the hidden sheet. Then it can be unhidden.

basil.cinnamon
basil.cinnamon

This might protect the sheet from the general newbie user, but if you open the workbook in say, Open Office, you can Tools|Sheet|Show the VeryHidden sheet just as you can a regular Hidden sheet. Not quite NSA-grade protection... It would be useful to have a password-protectable Hide command. Anyone know how to do this in VBA?

ssharkins
ssharkins

I recommend that you read the reader responses on this thread. There's a lot of good information there.

Retired007Geek
Retired007Geek

Another approach is to use the VBA properties to hide the project from viewing and password protect the project. Then they can't even see what sheets are in the project w/o the password. Bruce

J.Felland
J.Felland

Actually, It does. I just did it on my computer and I have 2007. Have had it for a long time. Works like a charm.

b.krisanski
b.krisanski

The veryhidden property definitely works in 2003 as I have used it a number of spreadsheets that I have developed. Rather than use the properties dialog I simply use the following couple of lines in my VBA code to Very hide sheets as required. Sample code: Dim ws as Worksheet ws.Visible = xlSheetVeryHidden

ssharkins
ssharkins

Can you describe what's happening? This works in 2003.

Lewis.Athow
Lewis.Athow

It exists in 2003. I just tried it and it works.

nicefred60
nicefred60

with the Object Browser I can select the hidden sheet - now what? ( Excel 2007) I changed property to visible- no effect, I right clicked on sheet 3 in Object Browser and in members option dialog box typed -1 ? xlSheetVisible with no effect.

rocksmth04
rocksmth04

This is a wonderful opinion. The things mentioned are unanimous and needs to be appreciated by everyone rock Accident Attorneys

daniel
daniel

You can password protect all hidden sheet by protecting the workbook then enable the Structure option. In XL2007 hide a any sheet then click the Review tab then click the Protect Workbook. Uncheck the Window option but check the Structure option. Add a password in you want. Click OK. Daniel

victor.gutzler
victor.gutzler

It is possible to hide the columns containing the secret data and then password protect the worksheet. The hidden cells can still be referenced by other non-protected sheets. I do not use Open Office, so I wonder if anyone knows if it can be used to unlock protected sheets in Excel and see the hidden columns...

haytekph
haytekph

"Caution Making a worksheet very hidden is not a security feature. Anyone who really wants to know what resides on a very hidden sheet can easily find out by using the UnhideSheet macro" -- from Micro Office Online I tried unhiding using a macro such as above but it doesn't unhide sheet if the worksheet is password protected.

Editor's Picks