fbpx

How to Lock Formulas in Excel?

Yodalearning>Tutorials>How to Lock Formulas in Excel?

Imagine you have put a lot of effort in creating an awesome worksheet that you need to share with other people, you certainly wouldn’t want anyone to mess up any smart formulas that you worked so hard on!

Imagine Your boss wants you to protect a workbook, but she also wants to be able to change a few cells after you are done. So, before you password protected the workbook (or a worksheet), you unlocked some cells. Now that your boss is done, you can lock the cells.

Think Is it possible for you to protect your particular cells contain formulas?

The answer is Yes!

The most common way of preventing people from tampering with your Excel formulas is to Protect the worksheet.

How to lock formulas in Excel?

Before you start reading on how to lock formula in Excel, you should be aware of Some COOL #EXCEL SHORTCUTS

We are going to lock only those formulas

Let see how to lock formula in Excel by following steps!

Step 1: Create a table same as like above picture. This table is showing students marks of two subjects Math and Geography. In the cells of column H we have used a formula which calculates the total marks of each student in these two subjects. Say, in cell H2 we have used formula given below:

  =B2+C2

We are going to lock only those formulas in column H.

Here we have two objectives to lock only formulas

Step 2: Here we have two objectives to lock only formulas. Firstly, we have to unlock whole cells of worksheet,. Secondly, we will protect only formulas. So now Select whole table as like above picture. Now click Home tab and in the Alignment group, choose the small arrow to open the Format Cells dialog box.

On the Protection tab, Unchecked the Locked

Step 3: On the Protection tab, Unchecked the Locked check box, and then click OK.

Go to the Home tab. From Editing group, click Find & Select button and choose Go To Special

Step 4: Go to the Home tab. From Editing group, click Find & Select button and choose Go To Special.

Go To Special dialog box, check the Formulas radio button. This will select the check boxes with all formula typesStep 5: In the Go To Special dialog box, check the Formulas radio button. This will select the checkboxes for all formula types. Now click OK.

Now locked again only these selected formula from Format Cells dialog box

Step 6: Now it has selected only those cells to contain a formula. Now locked again only this selected formula from Format Cells dialog box following previous steps. Now on the Review tab in the ribbon select Protect Sheet from Changes group.

The Protect Sheet dialog window will appear

Step 7: The Protect Sheet dialog window will appear. Check “Select locked Cell” and “Select Unlocked Cell”. Now click OK.

Tips: In the Protect Sheet dialog window You can use a password in the text box named “Password to unprotect sheet”. In this way when someone will try to unprotect this, it will not be possible for him to unprotect without your given password. Only you can Unprotect or Unlock this formula. So, be careful and remember your given password to unprotect this formula.

Now We have successfully locked our formulas in Excel!


Step 8:
Click on any cell of column H that contain a formula, It will show a message like above picture. But You can edit another cell. This is the simple way you can hide and lock particular cells that contain formulas in Excel.

Tips: When you want to unprotect it again, just simply select Review tab, then click Unprotect Sheet and use your given password. Then it will be unprotected again for you.

Now We have successfully locked our formulas in Excel!