2

I have an Excel file with a big worksheet, frequently updated and submitted.

Quite a number of rows and columns are hidden in order to make the calculation details and raw data invisible to people receiving the file, and the sheet is password-protected.

However, a user still might read the hidden part by copying and pasting the content to another sheet, or by using a formula such as

='ProtectedSheet'!J9

where J9 is the hidden cell.

I know every wall has a way to break through, yet still, is there a way to protect the content of a hidden Excel cell from being (easily) read out by a "normal" user?

2 Answers 2

2

Farm out the calculation helper cells to a totally different sheet. Then hide that sheet, or use VBA to make it "very hidden" , which means that it cannot be unhidden via the user interface.

This is a quote form John Walkenbach's article http://j-walk.com/ss/excel/usertips/tip036.htm

  • Press Alt-F11 to display the Visual Basic Editor
  • in the Project window, double-click Microsoft Excel Objects and select the sheet to hide.
  • Press F4 to display the Property box
  • Set the sheet's Visible property to xlSheetVeryHidden.

To unhide the sheet, repeat these step, but set the Visible property to xlSheetVisible.

Protect the VBA project with a password, so even a user with a few VBA skills cannot easily get to it.

Be aware that no Excel password protection is ever totally safe. Worksheet passwords take 2 seconds to disarm. Depending on the Excel version, workbook passwords and VBA passwords can be circumvented with a bit more effort if enough malicious energy is thrown at them.

2

The general approach I try to take is to hide the things people don't need to see, protect the cells I don't want them to change and leave it at that. There's not much that can't be worked around if someone genuinely wants to see what's hidden, so if there are things that you really want to keep secret, then Excel is not the answer.

That said, here are a couple of examples of things you can do to disuade people from getting at the detail:

  1. Set the properties of those cells to Hidden (Format Cells...|Protection|Hidden, which hides the formulas that generated the value in a cell when you protect it; and
  2. Turn off the 'select locked cells' check box when you protect the sheet, which will stop someone from then selecting the cell if they unhide it and try to use it in a formula. It won't prevent someone from typing in the cell reference manually, but it would make it more of a hassle.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .