Lock and Unlock Cells Formulas

Want to lock cells in Excel 2016 and make them protected from any type of modification OR unable to edit/modify cells because they are locked? Read this blog till its conclusion, and know the simple tricks to lock & unlock cells in Microsoft Excel 2016.

Preventing spreadsheet cells from any types of unwanted modifications become necessary as no one want inadvertently change in cells that should not be modified.

Luckily, Microsoft Excel 2016 and other of its earlier versions allow you to lock cells and prevent them from being modified.

In case, you want to lock all the cells in a worksheet or protect a specific cell and allow some cells of the excel file to be changeable then you can also do this.

One can easily lock some specific cells and ranges before you prevent the worksheet and, optionally, allow users to modify only exact ranges of a locked sheet.

Now, coming to the point, “how to Lock and Unlock the Cells in a Worksheet?


How to Lock the Cells in a Spreadsheet?

Step 1: Open the Excel file and then click on the Review tab.

Step 2: Then, click on the Protect Sheet. A Protect Sheet window will appear; here you have to enter a password to protect the excel sheet and any of the other actions.

Step 3: Click on the OK to enable the protection.

Now, your entire excel sheet is protected from any types of modifications. Whenever anyone tries to edit any of the locked cells then the following message will appear:

The cells can only be unlocked when the sheet is unprotected (by going to the Review tab again, choosing “Unprotect Sheet,” and entering the password, if required).


How to Lock Specific Cells in a Worksheet

With the help of above-discussed methods, all cells will get locked by default as it protects the entire sheet. But, if you want to lock or unlock a specific cell then you have to go through the below-given steps:

For example: If you are handling an inventory list and you might want that unit rate and stock quantities to be editable, but prevent the item IDs, names, or descriptions from any type of modification then you should follow this method.

Step 1: Here, first you have to choose all the cells that you don’t want to be locked. All the cells selected by you can be edited even after the spreadsheet is protected.

Step 2: Now, right-click on your selection, choose Format Cells, and then go to the Protection tab. Or you can also navigate through, Home tab -> click on the expansion icon -> Format Cells window -> Protection tab.

Step 3: Unmark the “Locked” and then click OK.

Step 4: Then, go to the Review > Protect Sheet and press the OK

All the cells that you have didn’t unlock using the above steps will be locked, but the unlocked cells will be changeable.


Some other tips that you may like:

6 Ways to Use Pivot Table to Analyze Quarterly, Monthly & Yearly Trends

8 Easy Excel Filters To Save Time, Money and Get Accurate Data

7 Underrated Excel Functions That I Wish Knew Before


How to Unlock Specific Cells in a Worksheet?

Step 1: First you have to select the locked cells that you want to unlock or modify.

Step 2: Click on the “Home” tab and in the “Cells” area, choose “Format” > “Format Cells“.

Step 3: Go to the “Protection” tab.

Step 4: At this step, you have to unmark the box present along with the “Locked” to unlock the cells.

Then click on the “OK“.

A dialog box will appear saying that “locking cells or hiding formulas has no effect until you protect the worksheet.”

In order to do so, go to the “Review” tab and choose “Protect Sheet“.


How to Lock Formulas in Microsoft Excel?

Follow the below given steps to lock the formulas in Microsoft Excel:

Step 1: First you have to unlock all the cells.

Step 2: Now, select all the cells that contain formulas. To do this you can use Go To Special option.

Step 3: Then you have to Lock these selected cells.

Step 4: Then protect the worksheet.

Once you have locked the formulas in Excel then the user can’t make any modifications to the cells having formulas.


How to Hide Formulas in Excel?

To do this, follow the below given steps:

Step 1: Press the Control + A from the keyboard to select all the cells present in the worksheet.

Step 2: Then, go to the Home and click on the Find & Select.

Step 3: Now, from the drop-down menu you have to choose Go to Special.

Step 4: Select Formulas from the Go to Special option.

Step 5: Then, click on the OK. All the cells containing formulas get selected.

Step 6: Now, you have to press the Control + 1 to open the format cells dialog box.

At last, Format the Cells dialog box, and then go to the Protection tab. Mark the Hidden option and then click on the OK.



All the above-given methods are effective and simple to apply, so it is hoped that now you are the master in protecting and un-protecting your excel sheet/cells from unauthorized access.

If you have any other query or issues related to the excel file then you can contact us at http://ask.repairmsexcel.com


How to Lock and Unlock Cells, Formulas in Microsoft Excel 2016?
How to Lock and Unlock Cells, Formulas in Microsoft Excel 2016?
Article Name
How to Lock and Unlock Cells, Formulas in Microsoft Excel 2016?
Want to learn about how to Lock and Unlock Cells, hide Formulas, lock formulas and more tricks in Microsoft Excel 2016 then go with this blog and learn them
Publisher Name
Repair MS Excel Blogs
Publisher Logo