The Robservatory

Robservations on everything…

 

How to securely hide worksheet(s) in an Excel workbook

Important note: Please see the comments—the method I discuss here is secure from casual users. However, the Excel files are just zipped XML files, so it's possible to unzip them and open them with a text editor. When I tested with my dummy salary worksheet, I could see individual salary values, but no actual text—so there wasn't any way to associate a salary with a person. Still, the following should be considered as only a semi-safe solution.

My wife wants to create a budget spreadsheet for the various departments in her company to use. Each department would have their own tab, showing their planned yearly budget by month for the coming year. A super-simplified and unrealistic input worksheet might look like this:

The green areas are the portions that the users would fill out—probably not the department heads, but just someone in the department. Which leads to the problem: There's a worksheet called Salary Info that's used to populate the Salaries line in the workbook. (In my silly example, I just divided the total salary into four equal quarters.)

The problem is that the Salary Info worksheet contains salary information for the entire organization, and this isn't information that should be shared with everyone.The Salary Info sheet might reveal, for instance, that a coworker is leaving in three months—the coworker has told management, but the employee isn't ready to tell the entire company just yet.

So how can you distribute the workbook to all the departments, with the salary info intact, but without revealing that data to everyone?

The answer would appear to be the Hide menu that appears when you right-click on a sheet. Hide a sheet, and poof, it's gone, but all formulas that refer to the sheet still work. Great, except…unhiding a sheet is as easy as choosing Unhide from that same contextual menu.

The real answer is to use an Excel feature called Protect Workbook. Note that this is different than protecting cells so they can't be changed, and it's different than protecting your workbook by requiring a password to access it. I hadn't heard about this feature until I went to research my wife's question on hiding a worksheet within a workbook.

Here's how to use it to really hide a worksheet within a workbook. First, start by hiding the sheet (or sheets) that you'd like to make invisible. Next, select Tools > Protection > Protect Workbook1You can also find a Protect Workbook icon on the Review toolbar, which will open this dialog:

Make sure "Protect structure" is checked, then enter and confirm a password—and make sure you store that password in 1Password or similar, as you'll need it in the future! And that's all there is to it. As shown in the dialog, there are some other restrictions with a protected workbook—mainly that users can't add new sheets without knowing the password to unprotect the workbook. For a template that will be distributed for others to fill in, this wouldn't be an issue.

If a user tries to use the contextual menu on another sheet to unhide the hidden sheet(s), they'll see that Unhide is grayed out…

This method even survives someone who knows Excel's macro language, and tries to unhide the sheet using the Visual Basic Editor; if they try to set the sheet to visible through the editor, they'll get nowhere:

I did a fair bit of searching on this topic, and I haven't found anything to indicate that it's easily defeatable protection—about all I found were references to a bunch of iffy-looking third-party "break XLS protection!" apps that mainly ran on Windows.

4 Comments

Add a Comment
  1. All office files are now zipped XML files (any that end in x like xlsx). As such, you can unzip the files (in terminal) and see the contents.

    It is worth checking if that data is easily viewable with this method. I am not saying it is the case; only that you shoudl confirm. I have no idea!

    1. Bad news Rob! On my quick test with Excel for Mac, 16.26 (19060901), I was able to unzip it and see the contents of that worksheet. It wasn't pretty and I knew what I was looking for but it also isn't too hard to figure it out if someone really wants to know salaries etc.

      1. Justin:

        Dang, I didn't know they were just zip archives ... wild.

        I can see the hidden sheet in the expanded XML, and I can see individual salaries from that sheet...but I don't see any text entries at all—no names, no column headers, nothing. Am I missing something and that data is there? As it is, I see the exposure is someone could see all the company's salaries, but they wouldn't know who makes what.

        That's still not ideal, obviously, and I think I'm going to open a bug on this with Microsoft—what good is a protected hidden sheet if you can still see what's on it?

        thx;
        -rob.

        1. I don't know what else is there. I am not sure how the protection works nor do I know much about the actual storage schema. But if you good about protected workbooks, there are tons of hacks to undo it. It really isn't protected!

          Again, this is just from my cursory reading after your post. I don't use them much and I barely use Excel.

Leave a Reply

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

The Robservatory © 2020 • Privacy Policy Built from the Frontier theme