Daily Dose of Excel

Do you have worksheets in your workbook that contain sensitive data? Do these workbooks contain other data that you need to be available to all? Are you looking for a way to keep your sensitive data under wraps? Well look no further. I have the answer.

You can’t.

If a user knows how, they can see that worksheet. If they don’t, you have a couple of options. First, you can hide the sheet through the user interface. Choose Format > Sheet > Hide and voilà, your sheet is gone. You have to keep at least one sheet visible, so don’t try this on your single sheet workbooks. To unhide that sheet, Format > Sheet > Unhide will display a dialog box of all the hidden sheets. As far as I know, there’s no way to prevent a user from unhiding a sheet that appears in that dialog. That is, there’s no password scheme or way of protecting the sheet or way of protecting the workbook that prevents the unhiding of a sheet. Unfortunately, even your two-index-finger-typing boss knows, or can figure out, this method.

If you want to take the next step, you’ll need to learn a little VBA. Don’t be frightened, I’ll hold your hand all the way through. There is a way to hide a sheet, using VBA, so that it doesn’t appear in the Unhide dialog box. You can implement this method without putting macros in your workbook too.

To accomplish this, you’ll open the Visual Basic Editor (VBE), select your workbook, and enter some commands into the Immediate Window. To see the sheet, you’ll do the same thing except the commands will be different. Here are the steps:

Alt+F11 to open the VBE
Cntl+R to view and set focus on the Project Explorer Window
Click the project that says VBAProject(YouWorkbookNameHere) to make it “ThisWorkbook”
Cntl+G to view and set focus on the Immediate Window
Type ThisWorkbook.Sheets("MySheetName").Visible = xlSheetVeryHidden and press Enter.
Alt+F4 to close the VBE

This picture shows that the VBA Project for Book1 is selected in the Project Explorer window and an appropriate command was entered into the Immediate Window.

You should be back in Excel and the sheet should be hidden. Navigating to Format > Sheet > Unhide should display a dialog box that does not show your sheet. Note that if the sheet you just hid is the only hidden sheet, then that menu command will be disabled as if there are no hidden sheets.

The downside to this is that if the user knows how to do what you just did, then can do the same thing to unhide it. The only difference is what you type in the Immediate Window.

Type ThisWorkbook.Sheets("MySheetName").Visible = xlSheetVisible and press Enter.

If the person from whom your trying to hide the sheet know his way around the VBE, there’s not much you can do about. Further, if that person reads this blog, this post is probably of little use to you. Sorry for letting the cat out of the bag.

I’ve never noticed the “Allow Users to Edit Ranges” option under Tools > Protection until Shane pointed it out the other day. This seems like it could be fairly useful if you have decent domain level groups established. One way I can see to use this is in cross-departmental collaboration. What? Man do I sound like an über-dork. Maybe an example is in order. Here’s a really complicated budget worksheet:

Now I only want certain people to be able to change certain numbers. Since I was so diligent setting up the users and groups in my domain, it’s fairly easy to assign a group to a cell. I’ve already assigned the revenue number to the gSales group, which means that anyone in the gSales group can modify that number, but anyone else will need a password. Now I want to restrict the COGS and Operations budgets.

This menu command brings up the following dialog. You can see that I’ve already set up a restriction on B3.

I click on the New button to create a restriction for a different range.

The operations folks will be responsible for the COGS number and their part of the overhead. I use the comma (union operator) to put both ranges in one, but I could just set them up separately.

The Permissions button brings up the domain users dialog. It’s empty for me, but I can choose Add and go through several more dialogs to get to the group I want. I don’t show all those steps here, but it’s not too difficult. I find my gProduction group and assign it to this range. Now anyone in that group can modify B5 or B11.

I’m in the production group, so I can go crazy on COGS, but if I try to change the revenue number, I get

You have to protect the sheet, but you don’t have to unlock these cells - apparently this process takes care of that for you. Now that I’ve got Sales and Production set up, I’ll add ranges so Marketing can change their budget and Accounting can change theirs.

We all keep sensitive data in Excel. Whether it’s your secret checking account or the phone numbers of your mistresses, you don’t want your wife opening just an old Excel file she wants. To prevent that, you can keep your sensitive Excel files on a floppy and hide it in the toilet tank, or you can password protect the files.

On the Save As dialog, there’s a Tools menu.

General Options shows the Save Options dialog.

Here, you can enter a password to open the file and one to modify it. If you enter both, you’ll be prompted twice (a feature I never liked). I once inherited a file that used both. Did you guess the rest? They were the same password. How stupid is that?

The read only check box presents yet another prompt. Even after you’ve entered the password to modify, it warns you that you should open the workbook as read only unless you want to modify it. I just typed in the password, of course I want to modify it.

I rarely password protect my Excel files. If I did, they would all have the same password because there’s no way I’m going to remember more than a few passwords. Also, Excel’s password protection is notoriously easy to crack. At my office, I’ve protected the personal financial statements of all the owners. By the time I needed to update those files, I’d forgotten the password. I found a program that would crack it, but it only gives you the first three letters unless you pay. Fortunately, the first three letters was all it took to spark my memory. Now I have that password safely stored on a sticky note on my monitor (just kidding).

If I worked for Microsoft, I would have made the password prompt look like this

You only get one prompt. If you know the second password, it would be assumed that you know the first. I suppose that’s one less password to crack, but I doubt that would increase the snooping very much. And if you enter the second one, no prompt for read-only.

One aspect of Excel’s protection is the ability to hide the contents of a cell. The cell still displays, but the formula bar remains empty when the cell is selected. This is useful if you don’t want the users to see the formula for the cell.

On the Protection tab of Format>Cells, check the Hidden box.

Then protect the sheet using Tools>Protection>Protect Sheet. With the Hidden property checked and the sheet protected, the formula is no longer visible to the user.