How to Lock Formulas or Cells in Excel?

Sometimes we share our excel sheet with others but we don’t want others to edit it. Or we want to prevent the sheet from getting edited mistakenly. Sometimes the Excel sheet may have formulas in some cells which when edited mistakenly may lead to the wrong result. Excel provides us with the facility to lock formulas or cells in the sheet.

All the cells of an excel sheet ‘by default’ are ‘locked’ that means the lock property of all the cells in an excel sheet is enabled by default. But, if you have to restrict this lock property you have to ‘protect’ the sheet. In this section, we will discuss the step to lock only those cells which contains formulas in it or to lock selected cells only.

Steps to Lock Formulas or Cells in excel 2016:

Step 1: First we have to disable the lock property of all cells in an excel sheet because we have to lock only those cells which have formulas.

So, to disable lock property over all the cells initially, select all the cells on the excel sheet. You can do this by clicking on the ‘Select All’ button as you can see in the image below or you can also do this by pressing ‘Ctrl+A’.

Right-click on the sheet and click on the ‘Format Cells…’.

A Format Cells dialog box will pop up (you can do this by pressing ‘Ctrl+1’). In Format Cell dialog box go to Protection tab and ‘uncheck’ the Locked checkbox and click OK.

Step 2: In this step, we have to select only those cells on the sheet which contains Formulas.

For this go to the ‘Find & Select’ drop-down button under the ‘Editing’ group of ‘Home’ tab. Among the displayed options click on ‘Go To Special…’.

A ‘Go To Special‘ dialog box will open. Here, you have to click on Formulas radio button and then click OK.

Or you can directly click ‘Formulas’ from the options displayed by ‘Find & Select‘ drop-down button.

As a result, all the cells that have formula will automatically get selected as you can see in the image below, the two cells containing the formula are selected.

Step 3: In this step, you have to impose ‘lock’ property on the selected cells containing the formulas.

Right-click on any one of the selected cell containing the formula and among the displayed option click on the ‘Format Cells…’.

In the Protection tab of the Format Cells dialog box, check the Locked checkbox and click on OK. Doing this we have enabled the lock property on the cells containing the formulas.

Step 4: In this step, we have to restrict the user by accessing the locked cells. So, we have to protect our sheet and for this go to Review tab and under Changes group, click on Protect Sheet.

It will lead you to the Protect Sheet dialog box, where you can provide a password to protect the sheet. The same password will be required to unprotect the sheet. After writing the password in ‘Password to Unprotect sheet:’ text box, click OK.

A confirmation dialog box will appear where you have to re-enter the same password and click OK.

Note: If you want that user should not be able even to select the cells containing formula (locked cells) you can uncheck the Select locked cells checkbox of Protect Sheet dialog box.

Now, after you protect the excel worksheet, try to access the cells that have a formula or the cells we have locked and protected. It will display a message:

The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

So, this was all about the steps to Lock cells containing the formulas and it is not that you can only lock cells containing formula you can lock any cell on the sheet of your choice.