How to lock and protect formula in excel?

In this article, the user will learn the process of locking and protecting formula cells in Excel. Locking and protecting nonempty cells in a selected range with the "Protect Sheet" feature in spreadsheet applications like Excel or Google Sheets provides an additional layer of security to prevent accidental or unauthorized modifications to important data. By using the "Protect Sheet" feature and locking nonempty cells, the user can add an extra level of control and safeguard to your spreadsheet, promoting data integrity, collaboration, and data security.

Example 1: To Lock and protect all the formula cells in a selected range with Protect Sheet

Step 1

To understand the process of locking and protecting formula-based cells within the selected range, the user first needs to consider a sample table, as shown below. In the below given table, will be using three columns. The first column contains value I, while the second column contains value II, and the third column contains the “sum” column.

Step 2

Select the provided table, by clicking on C1 cell and selecting the table with data up to E5.

Step 3

In the Home tab, click on the “Editing” option, and select the option “Find & Select”. In the option, select the option “Go To Special...”. Consider the below-depicted image for the proper reference.

Step 4

The above step will display a “Go to Special”, dialog box. In the appeared dialog box, click on the option for “formulas”. After that select all the available options that are Numbers, Text, Logicals, and Errors. Finally, click on the “OK” button.

Step 5

The above step will automatically select the cells with the formula cells. Consider the below-given snapshot for the proper reference. Please note that in the taken example E3, E4, and E5 are the cells that contain formula. And in the below-depicted image, all the cells mentioned above are shown as highlighted ones −

Step 6

The above step will select all the cells that contain the formula After that right click on any of the selected cells. This will display a list of available options. Among the displayed list of options, select the option with “Format Cells”.

Step 7

The above step will display a dialog box for “Format Cells”. This option will display multiple tabs, here each tab has multiple options. Consider below given image snapshot for proper reference −

Step 8

Among all the available tabs, choose the “Protection” tab, and untick the “Locked” option. Finally, click on the “OK” button.

Step 9

In the review tab, click on the “Protect” tab, and select the option for “Protect Sheet”. Consider the below-depicted image for reference −

Step 10

The above step will open a new dialog box, with the header “Protect Sheet”. In this dialog box, first, enter the password to make the sheet unprotected. In this example, we will assume that the password is “abc”. Please note that users can use any desirable password. But, make sure to remind the user password, as to make any change or to unprotect the sheet, the user needs this password only. After that select both the first two options “Select locked cells” and “Select unlocked cells”. Finally, press the “OK” button.

Step 11

This will open a “Confirm Password” dialog box. This dialog box will allow the user to enter the password again, and click on the “OK” button.

Step 12

After that, try to click and edit any cell. In this example, the user would try to modify the B9’s content cell. but this will display a dialog box, with the message, as depicted below. To remove the error message, click on the “OK” button.

Conclusion

In this article, users will learn the process of locking and protecting the cells that contain the formula of the Excel sheet. This article briefs the same task with two different approaches, although the output of both examples is the same. In this article, one example is provided which allows a user to lock and protect the nonempty cell, in the selected range, by using the available Excel features, and options. All the provided steps are clear, precise, thorough, and fully relevant to the provided content.