How to lock formulas in Excel - Protect excel formulas

There are many ways to protect Excel files, depending on each person's needs, such as setting a password for the entire Excel file, setting a password for Excel sheets so others cannot edit them, or just setting a security key for the formula. in Excel file only.

When you set an Excel formula lock, others cannot edit the formula, copy or delete the formula, but other content can still be edited at will. The article below will guide you how to lock formulas in Excel.

1. Instructions for protecting formulas in Excel

Step 1:

Open the Excel file with the formula to lock the formula. Select the entire spreadsheet then click the Home tab , select Format and click Format Cells…

How to lock formulas in Excel - Protect excel formulas Picture 1

Step 2:

The Format Cells dialog box appears, click on the Protection tab and then uncheck the Locked option below. Click OK to save.

How to lock formulas in Excel - Protect excel formulas Picture 2

Step 3:

Return to the spreadsheet, hover over any cell, click the Home tab and select Find & Select in the Editing group. Click on Go to Special. in the list that appears.

How to lock formulas in Excel - Protect excel formulas Picture 3

Step 4:

In the Go To Special dialog box , select Formulas and then click OK to agree.

How to lock formulas in Excel - Protect excel formulas Picture 4

Now the cells with formulas on the Excel file will be marked as shown below.

How to lock formulas in Excel - Protect excel formulas Picture 5

Step 5:

Keep cells with selected formulas intact in the content. Next click on the Home tab then select Format and select Format Cells. . In Format Cells, select Locked and then click OK.

How to lock formulas in Excel - Protect excel formulas Picture 6

Step 6:

We click on the Review tab and then click Protect Sheet .

How to lock formulas in Excel - Protect excel formulas Picture 7

In the Protect Sheet dialog box, we perform the following operations:

1. Password to unprotect sheet: Set a password for the formula. This password will require entry if you want to remove the formula protection feature in Excel.
2. Select locked cells: Uncheck this option.

You will then be asked to re-enter the password you set. Finally click OK to save.

How to lock formulas in Excel - Protect excel formulas Picture 8

When you return to the table, you will see that the formula icon does not appear in the cell using the formula like before. When clicking on the cell, the used formula is not displayed. Other people will not be able to know and edit that formula. Areas outside the formula can still be customized as usual.

How to lock formulas in Excel - Protect excel formulas Picture 9

2. How to unlock Excel formulas

Step 1:

To remove the formula locking feature in Excel, click the Review tab and then select Unprotect Sheet .

How to lock formulas in Excel - Protect excel formulas Picture 10

Step 2:

Enter the correct formula password that the user previously set up. Click OK.

How to lock formulas in Excel - Protect excel formulas Picture 11

Finally, the data cell using the formula is displayed again and when you click on that cell, the formula will appear above.

How to lock formulas in Excel - Protect excel formulas Picture 12

A great way to protect certain content on an Excel file, but other content can still be edited as usual. When an Excel formula is locked, viewers cannot click on the formula cell and edit it. This will limit the situation of editing and deleting formulas in Excel.

Wishing you success!

Lesley Montoya

Update 10 July 2024