How to Lock Formulas in Excel?

How to Lock Formulas in Excel? Confused? Here is the blog to know how to lock formulas.

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.

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

We are going to lock only those formulas

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!

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.