How to lock formulas in Excel

When sharing an Excel file, you don't want other people to edit the formulas, see the formulas, then lock the formulas again.

There are many ways to protect the Exce file, depending on the needs of each person such as setting a password for the entire Excel file, setting the password for the sheet on Excel so that others cannot edit it, or just setting the security key for the recipe. in Excel file only.

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

  1. Forgot password protected Excel file, what should you do?
  2. How to break VBA password in Excel
  3. Password setting tools for PDF files

1. Guide to protecting formulas on Excel

Step 1:

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

How to lock formulas in Excel Picture 1How to lock formulas in Excel Picture 1

Step 2:

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

How to lock formulas in Excel Picture 2How to lock formulas in Excel Picture 2

Step 3:

Go back to the worksheet to mouse in any box, click on the Home tab, then select Find & Select in the Editing group. Click on Go to Special . in the list that appears.

How to lock formulas in Excel Picture 3How to lock formulas in Excel Picture 3

Step 4:

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

How to lock formulas in Excel Picture 4How to lock formulas in Excel Picture 4

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

How to lock formulas in Excel Picture 5How to lock formulas in Excel Picture 5

Step 5:

Keep the cells with the selected formula intact. Then click on the Home tab and then choose Format to choose Format Cells . In Format Cells, select Locked and then click OK.

How to lock formulas in Excel Picture 6How to lock formulas in Excel Picture 6

Step 6:

Click the Review tab, then click Protect Sheet .

How to lock formulas in Excel Picture 7How to lock formulas in Excel Picture 7

In the Protect Sheet dialog box, we do the following:

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

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

How to lock formulas in Excel Picture 8How to lock formulas in Excel Picture 8

When you return to the table, you will not see the formula icon in the formula cell as before. When clicking on the box does not show the formula used. Others will not be able to know and edit that recipe. Areas outside the formula can still be customized as usual.

How to lock formulas in Excel Picture 9How to lock formulas in Excel Picture 9

2. How to unblock Excel formulas

Step 1:

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

How to lock formulas in Excel Picture 10How to lock formulas in Excel Picture 10

Step 2:

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

How to lock formulas in Excel Picture 11How to lock formulas in Excel Picture 11

Finally, the data box uses the formula that appears again and when you click on that box, the formula will appear.

How to lock formulas in Excel Picture 12How to lock formulas in Excel Picture 12

A great way to protect certain content on Excel files, but other content can still be edited as usual. When placing an Excel formula lock, viewers cannot click on the formula box and edit. This will limit the status of editing and deleting formulas on Excel.

See more:

  1. How to prevent others from copying and editing Word files
  2. How to set a password for a PDF file?
  3. 10 ways to recover corrupted Excel files

I wish you all success!

5 ★ | 1 Vote