Hide, lock and protect Excel formulas from being copied
Microsoft Excel provides an easy and convenient way to trace, research, and reconstruct the worksheet cell formulas and algorithms through the use of their ‘Formula Auditing’ tools. You may trace all dependencies and precedences of the formulas in a workbook using the corresponding tools in “Formula Auditing”.
You may figure out how a formula gets its value with step-by-step formula calculation tracing. You may find this tool by going to the “Main menu” and selecting “Formulas”, then the “Formula Auditing” group and selecting the “Evaluating Formula” item.
But what if the workbook author wants to hide spreadsheet formulas from prying eyes?
There may be different reasons as to why an author wants to hide and lock the formulas in spreadsheet.
- Prevent accidental deletion or changes in cell formulas.
- Keep proprietary cell formulas and algorithms secret.
In this case Microsoft Excel provides easy to use tools to hide and lock formulas in cells.
Formula protection tools provided by Microsoft Excel are good enough to achieve the first goal of “preventing accidental deletion or changes formulas” but these tools absolutely are not fit for the second goal of “keeping the proprietary algorithms a secret” because they only hide the equations from showing in cells and formula bar, but all these equations remain reachable by many other ways.
In contrast to Excel’s approach to hiding and locking formulas, the DoneEx XCell Compiler provides a revolutionary method to protect cell formulas in your workbook.
XCell Compiler doesn’t just hide equations from worksheet cells – it does so much more! The product compiles all formulas into a binary format and moves all equations into an executable (EXE) file as pure algorithms. After this process, not a single formula is left in the spreadsheet’s cells. This means that nobody will be able to see, trace or reconstruct the protected formulas.
Excel formulas protection with DoneEx XCell Compiler
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 formulas protection and does so by default with every compilation you conduct! The XCell compiler secures all workbook formulas by moving them from xls into binary code and removes them from the cells of xls (xlsx, xlsm, xlsb) file.
To reach this goal, DoneEx engineers developed our own calculation engine which works during the compiled formula calculation process by substituting the original Excel’s calculation in the protected workbook, while 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.
How to Hide and Lock Formulas in Microsoft Excel
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 equations (which you have previously selected in step 1) that are located on your worksheet should be protected with the original Microsoft worksheet formula protection.
This process also prevents the equations 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 workbook can easily be made visible or be fetched through some other means.
The main goal of 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 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.
See also: How to compile your workbook.