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.
Video tutorial on locking formula cells 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 and click Home tabselect Format and click Format Cells…
Step 2:
The Format Cells dialog box appears, click to select it Protection tab Already Uncheck the Locked option under. Click OK to save.
Step 3:
Return to the spreadsheet, hover over any cell, and click Home tab then select Find & Select at the Editing group. Click next Go to Special… in the list that appears.
Step 4:
In the dialog box Go To Specialplease choose Formulas then press OK to agree.
Now the cells with formulas on the Excel file will be marked as shown below.
Step 5:
Keep cells with selected formulas intact in the content. Next click Home tab then select Format choose next Format Cells…. In Format Cells you do select Locked then press OK.
Step 6:
Let's click next Review tab then press select Protect Sheet.
In the Protect Sheet dialog box, we perform the following operations:
- 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.
- Select locked cells: Uncheck this option.
You will then be asked to re-enter the password you set. Finally click OK to save.
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.
2. How to unlock Excel formulas
Step 1:
To remove the formula locking feature in Excel, click Review tab then select next Unprotect Sheet.
Step 2:
Enter correctly formula password that the user has previously set up. Click OK.
Finally, the data cell using the formula is displayed again and when you click on that cell, the formula will appear above.
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.
See more:
Wishing you success!