Excel Formula Protection

Microsoft Excel developers have made it easier for users to understand and work with formulas. When you select a cell with a formula in Excel, the formula bar becomes active and displays the formula. Excel formula protection can alleviate this problem, but first we must understand how Excel works without it.

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. 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 the copying process, 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 the copying process 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:

1. Standard Excel formula protection approach to lock and hide spreadsheet formulas.
2. 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. Standard Excel just hides and locks formulas, which provides protection that is insufficient in cases where you need to keep your formulas confidential. This is where XCell Compiler’s advanced protection mechanism shines.

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 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 cells in the resulting file. By eliminating the original Excel formulas, it becomes impossible to restore them from the binary code, providing the most effective method to create strong Excel formula protection.

For maximum protection, XCell Compiler has created a new calculation engine that replaces the original Excel calculation engine in the compiled workbook. Our calculation engine manipulates binary code algorithms created from the original Excel workbook formulas, using Excel only as a tool for formatting and displaying the calculation results.
This process ensures that the original formulas are entirely absent 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:

1.  Open the workbook you want to compile.
2.  Select the ‘DoneEx’ item in the Excel main menu and then select the ‘Compile’ item
3. 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.

However, in a workbook compiled with XCell Compiler these arguments are valid and the INFO() function returns the following 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”) – returns 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.

How to Lock the Excel Formulas to Protect Them

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 (which the article will explain in further detail below):

1. Unlock all cells.
2. Select cells containing formulas using Go To Special.
3. Lock the selected cells.
4. Protect the worksheet.

Now let’s go through all of the details on 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, remember 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 by 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 become 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 secure them

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 with Excel formula protection

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.
• Make sure to select the “Protect worksheet and contents of the locked cells” option in the Protect Sheet dialog box.
  
• 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! You have locked and protected the Excel formulas! However, they are 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. A formula being visible is what happens when you click on a cell and it displays 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 by either using the keyboard shortcut [Ctrl+1] or by right-clicking on the selected cell(s) and select “Format Cells” from the context menu. You can also navigate to the “Home” Excel ribbon, 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 that 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 the customer cannot see or change your worksheet formulas.