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.