How to protect Excel formulas from being copied
There are many ways to protect Excel formulas in workbook cells, from the heavily flawed original Microsoft Excel formula protection to the ultimate formula protection that DoneEx XCell Compiler provides. In fact, every version of DoneEx XCell Compiler provides the best formula protection by default.
How The Original Microsoft Excel Formula Protection Works
TL;DR: The original Microsoft Excel formula protection uses the “Lock and Hide Excel workbook formula protection” approach, which is weak and inefficient.
The Original Microsoft Excel formula protection is activated as such:
- Select all of the cells that contain the formulas you wish to protect, much like in the screenshot shown below.
- While those Cells are selected, go to the “Home” tab on the Excel toolbar, click on the “Format” drop down menu and then click on the “Format Cells” button.
- The previous steps should have led to the “Format Cells” menu coming up on your screen. Here you can turn on the “Hidden” check box under the “Protection” tab. After you have done that, you can press “OK” to confirm your actions so far.
- Go to the “Review” tab on the Excel toolbar and click on the “Protect Sheet” button.
This should bring up the “Protect Sheet” menu, which allows you to set a password for your worksheet. After setting a password by typing it in the field labeled as “Password to protect worksheet” (which you can even leave blank) and pressing “OK”, the formulas (which you have previously selected in step 1) that are located on your worksheet should be protected with the original Microsoft Excel worksheet formula protection.
This process also prevents the formulas in the cells from being edited. Now whenever any user attempts to edit any cell you have protected in this manner the alert window that is displayed in this screenshot should come up:
The original Excel formula protection is weak and inefficient.
Any method that just involves hiding Excel formulas does not provide sufficient formula protection, since any hidden formulas of an Excel workbook can easily be made visible or be fetched through some other means.
The main goal of Excel spreadsheet copy protection is to remove formulas from cells to make them unavailable to anyone. If the formulas are not secured then anyone who has permission to open your Excel workbook (even if it was protected with a password, license key and/or a hardware dongle) may copy all of the data and formulas (manually or by using some software) into an unprotected workbook and use your intellectual property without any restrictions.
DoneEx XCell Compiler formula protection
The DoneEx XCell Compiler provides the strongest Excel formula protection approach you could possibly have! The best part is that every version of DoneEx XCell Compiler provides the absolute best Excel formula protection and does so by default with every compilation you conduct! The Excel compiler compiles all Excel formulas from xls into binary code and removes them from the cells of xls (xlsx, xlsm, xlsb) file.
To reach this goal, a calculation engine was created to substitute the original Excel’s calculation in compiled EXE whereas the Excel itself is used for formatting and reflection of calculation results.
This is the only way to really protect Excel formulas!
Even encoding your workbook with a strong encryption algorithm is not really all that secure, because then you may need to provide the decryption key for a user and once you do that your data is decrypted and potentially available for anybody.
- To check if there are any available formulas in any compiled EXE you can switch Excel into “Formula Auditing Mode”.
To do this go to “Main Excel menu” >> “Tools” >> “Formula Auditing” >> “Formula Auditing Mode”
- To check that formulas are not available from VBA code you can use the following equation in VBA:
Sheets(“sheet name from your workbook”).Range(“address of the cell with the formula”).formula
Secure formula protection is available for any and all of the DoneEx XCell Compiler licenses . To protect your formulas, you just need to compile your xls file with DoneEx XCell Compiler.
See also: How to compile your workbook.