Hide n protect formula

Question

When I circulate my statistical worksheet to users outside my company, I need to protect the underlying confidential formulas but also keep the worksheet easy for users to enter their data. Any ideas?

Answer 1

Implementing the secret formula in a compiled language and calling it from a library will provide a little bit of protection, though not from a determined and knowledgeable reverse engineer.

If you have the resources, your company could set up a web service where users send in their inputs, and your black box processes the input and sends back the output. That could be a hassle for the users, though, particularly the ones who want to keep their inputs confidential.

Answer 2

easiest solution (but not very secure) is to protect the cells. Not only select 'protect' but also 'hidden'. When the worksheet is then protected you can't see the formula that is being used.

as others have noted, if you really want this to be secure I wouldn't use excel.

Answer 3

As others have said, Excel isn't the best way to do this securely.

If you're going to do it with Excel anyhow, here's a way:

If your formulae are in VBA, you can go to the VB Editor and select Tools -> VBAProject Properties. On the Protection tab, tick the box next to Lock project for viewing and enter a password. Users will be able to see the formulas being called in Excel, but won't be able to view/edit the VBA.

(Menu commands are for Office 2003 - not sure about newer versions.)

Answer 4

SpreadsheetGear for .NET will let you embed your Excel workbook in a Windows Forms application or use it in an ASP.NET application.

Ultimately, if a determined user has the workbook, or an application with the workbook, or even the logic of your workbook converted to .NET or native code, they will be able to reverse engineer your logic if they invest enough time in it.

If you build an ASP.NET or other server based application, where the workbook only exists on the server, your IP will be much better protected (assuming your server admin is following sound security practices).

You can use SpreadsheetGear for either of these approaches.

You can see live ASP.NET samples here, learn about the SpreadsheetGear Explorer WinForms samples here, and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 5

Well there is always the near bullet-proof method of pasting the values and formatting to a new worksheet (can be easily automated). But of course you run into the risk of accidentally sending the original/unprotected Workbook to the users.