Excel Formula Protection
Microsoft Excel developers have made it easier for users to understand and work with formulas. Whenever a cell with a formula is selected, the formula is displayed automatically in the Excel formula bar.
To see the formula directly in the cell, simply press the F2 button on your keyboard.
If you need more clarification on a formula, you can evaluate individual parts of it by accessing the Formulas tab, then the Formula Auditing group, and clicking on the Evaluate Formulas button which will allow you to trace through the formula via the step-by-step formula calculation algorithm.
Besides the Excel formula bar, there exist many other ways to reach the cell formulas in the spreadsheet.
However, there may be situations where it is necessary to keep formulas private and shield them from being visible in the formula bar as well as other areas of Excel where the formulas may be exposed. In such cases, protection of Excel formulas becomes crucial in order to prevent other users from tampering with them or erasing them.
Why the Excel formula protection is important.
Protecting Excel formulas is crucial for workbook authors to maintain the accuracy as well as the integrity of the data and the formulas inside their spreadsheets.
After investing a significant amount of time and effort in crafting complex formulas that perform specific calculations or manipulate data, authors can ensure that they are not unintentionally or deliberately altered by others, which could lead to inaccurate analysis or miscalculations.
Moreover, protecting formulas can also safeguard the confidentiality of the author’s intellectual property, particularly if the workbook aims to monetize proprietary algorithms. This protection can prevent unauthorized access and usage of the workbook without the author’s permission.
By protecting the formulas from being copied, you can ensure that others cannot easily replicate the calculations and use them for their own purposes without your consent. This can be important if the formulas contain proprietary calculation algorithms. Additionally, formula protection from being copied can help prevent deliberate attempts to steal or misuse the formulas, which can be particularly important in a business or financial context where the confidentiality of the calculations is crucial.
Here we consider the following main—and very different—approaches to protect spreadsheet formulas:
- Standard Excel formula protection approach to lock and hide spreadsheet formulas.
- DoneEx XCell Compiler approach – formula protection with conversion of the workbook formulas into binary format (aka formula compilation).
You need to use the standard Excel formula protection approach in the case when you need to provide an integral workbook and avoid accidental formulas changes or complete removal by a customer. In this case you need just hide and lock workbook formulas with Excel. For a complete, beginner friendly tutorial on how to do that please read the tutorial How to hide and lock formulas in Excel.
Excel Formula protection with XCell Compiler
The DoneEx XCell Compiler approach is the use of an indispensable tool for securing your workbook’s formula calculation algorithms. The standard Excel, which just hides and locks formula, provides protection that is insufficient in cases where you need to keep your formulas confidential. This is where XCell Compiler’s advanced protection mechanism comes in handy.
It is not enough to simply protect your spreadsheet with a password or hardware locking mechanism. If the formula protection is weak, anyone with access to your workbook can—manually or with software—copy all of the data and formulas to an unprotected workbook and use it all as they please. This makes it essential to use a powerful protection tool such as the XCell Compiler.
The XCell Compiler is designed to compile all formulas in your workbook (xls, xlsm, xlsx, xlsb file) into binary code and remove them from the xls cells in the resulting file. By eliminating the original Excel formulas, it becomes impossible to restore them from the binary code, providing the absolutely most effective method to create strong Excel formula protection.
To ensure maximum protection, XCell Compiler has created a new calculation engine that replaces the original Excel calculation engine in the compiled workbook. Although Excel is still used for formatting and displaying calculation results, our calculation engine manipulates binary code algorithms created from the text of the original Excel workbook formulas, ensuring that the original formulas are entirely removed from the compiled workbook.
It is worth noting that other methods of formula protection, such as locking cell formulas or hiding them with standard Excel worksheet password protection, are not secure. These approaches can be quickly bypassed with software that is readily available on the internet. XCell Compiler is the only viable solution to secure your Excel workbook formulas, guaranteeing that your intellectual property remains safe from unauthorized access.
How to protect workbook formulas as securely as possible
The Excel workbook formula protection is the main feature of the XCell Compiler and is present in all product licenses. To securely protect cell formulas in your workbook, you just need to compile your workbook with XCell Compiler.
If you downloaded and installed our product then just follow these simple steps:
- Open the workbook you want to compile.
- Select the ‘DoneEx’ item in the Excel main menu and then select the ‘Compile’ item
- Set your compilation options and press ‘Compile’ button on the compilation form.
The XCell Compiler provides the real Excel formula compilation into binary format
To prove how real and effective the compilation of the formulas by XCell Compiler is, you can make the following simple test.
The XCell Compiler augments the logic of the Excel INFO() function by adding some arguments which are not available in the original Excel’s version of the function.
The original Excel function logic change is possible to do only in the case of a real compilation of the workbook and its formulas.
The INFO() function with augmented argument values will return the #VALUE! error in the original Excel workbook, which means that argument values are not valid.
But in a workbook compiled with XCell Compiler all these arguments are valid and INFO() function returns the following run-time values:
=INFO(“isexe”) – returns true when the compiled EXE file started
=INFO( “compid”) – provides the computer id of the computer where the compiled workbook is started
=INFO( “appname”) – gives the application name of the compiled workbook.
=INFO( “appver”) – returns the application version of the compiled workbook.
=INFO( “dateto”) – shows the expiration date of the compiled workbook registration period if the workbook was compiled with the “Workbook Copy protection” option turned on, along with a registration key and the date limitation present.
=INFO( “rname”) – shows the registered customer name if the workbook is compiled with the hardware locking feature and the customer registration key is present
=INFO( “rmail”) – shows the registered customer email if the workbook is compiled with the hardware locking option toggled on and the customer registration key is present.
If you create a simple workbook with an INFO() excel function which uses the additional arguments mentioned above, then after the compilation of that workbook you may verify the results which return the previously mentioned information with the INFO() function.
The following part of article describes the standard Excel approach to protect workbook formulas.
How to Lock the Excel Formulas
Before locking the formulas in Excel, it’s important to understand that—by default—all cells are actually locked in Excel. However, to restrict user access and prevent changes, the locked cells must be protected by using the explicit worksheet protection. Conversely, if a cell is unprotected, the user can make changes to it even when the worksheet is protected.
Here the four steps to lock formulas listed in brief; all steps will be explained in details below:
- Unlock all cells.
- Select cells containing formulas using Go To Special.
- Lock the selected cells.
- Protect the worksheet.
Now, after you have observed the necessary steps, let’s go through all the details of how to lock formulas in Excel.
Step 1 – Unlock all of the cells in the worksheet
To begin with, you should unlock all of the cells in your worksheet, even if you haven’t locked any yet. By default, every cell in Excel is set to “Locked,” whether it’s a new or an existing worksheet. However, this doesn’t prevent you from editing them until you protect the worksheet.
If you only want to lock cells with formulas, it’s important to first unlock all cells on the worksheet before proceeding.
To unlock all cells you need to go through the following steps:
- select all cells on the worksheet by pressing [Ctrl + A] or clicking on the gray triangle in the top left corner of the Excel grid.
- then open the Format Cells dialog by pressing [Ctrl + 1] or right-clicking on the mouse and choosing Format Cells.
- Once there, go to the Protection tab in the Format Cell dialog and uncheck the Locked option before clicking OK.
When you click on the OK button all of your cells will be unlocked.
Step 2 – Select All of the Worksheet Cells that Have Formulas
We unlocked all the cells, but now we have to lock the cells that contain formulas.
To achieve this, we must follow these steps:
- Choose all the cells on the worksheet by pressing the [Ctrl + A] key combination.
- Click on “Find & Select” in the “Editing” group, which is located under “Home” in the main Excel menu bar.
- In the drop-down menu, select the “Go To Special…” item
- To select all types of formulas, open the “Go To Special” dialog box and select the “Formulas” radio button. Then, click OK to choose all the checkboxes with formula types.
Step 3 – Lock the Cells with Formulas
To lock the selected cells that contain formulas, follow these steps:
- Open the Format Cells dialog box again by pressing [Ctrl + 1] or by clicking on the right mouse button and choosing Format Cells..
- Go to the Protection tab.
- Check the Locked checkbox.
- Click on the OK button
By selecting the Locked option, the cells’ contents are protected from being deleted, overwritten or modified by the user.
Step 4 – Protect the Worksheet
Enabling the “Locked” property for cells with formulas and not for others means that protecting the worksheet will only restrict access to cells that contain formulas. To protect the worksheet, follow these steps:
- Go to the Review tab.
- Click on Protect Sheet.
- In the Protect Sheet dialog box, make sure that the option “Protect worksheet and contents of the locked cells” is selected.
- Optionally, you can set a password.
- Click OK.
After following these steps, all cells with formulas will be locked, and users will not be able to modify them.
Congratulations! Your Excel formulas are now locked and protected, but still visible in the formula bar. If you want to hide formulas in your Excel sheet as well, please refer to the next section.
How to hide formulas in Excel
- Hiding an Excel formula involves concealing it from being visible in the formula bar, which is what happens when you click on a cell as it is displaying the formula’s result.
To hide formulas in Excel, follow these steps:
- Select the cell(s) or range of cells containing the formulas you want to hide. You can also choose non-adjacent cells or the entire sheet by pressing [Ctrl+A] or using the Go To Special > Formulas feature to select all cells with formulas.
- Open the Format Cells dialog box:
- To open the “Format Cells” dialog, you have various options to choose from. One way is to use the keyboard shortcut [Ctrl+1]. Alternatively, you can right-click on the selected cell(s) and select “Format Cells” from the context menu. Another way is to navigate to the “Home” Excel ribbon bar tab, locate the “Cells” group, and select “Format” followed by “Format Cells.”
- In the Format Cells dialog box, go to the Protection tab and select the “Hidden” checkbox. This option ensures that the Excel formula remains hidden in the formula bar.Note: The Locked attribute, which prevents cell contents from being edited, is selected by default. Generally, you would want to leave it as is.
- Click the OK button.
- Protect your Excel worksheet by performing these steps.
Now your worksheet formulas are locked and hidden from the customer’s view.