How To Lock And Unlock Cells, Formulas In Microsoft Excel 2016?

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 /Unlock Cells in Excel Worksheet?

How To Lock Cells In Excel Spreadsheet?

In this section you will catch information on how to lock cells in Excel just by making few small changes in the settings.

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:

excel lock cells from editing

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 Individual Cells in Excel 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. These steps are very much essential for those who wants to lock individual cells in Excel.

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.

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 on how to lock a cell in Excel formula:

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 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.

Conclusion

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. Similarly, if you get stuck with Password Protected Excel File and looking for the fixes to unlock it without using any software. Then to know how to fix this issue just check out the following post: Top 3 Methods To Unlock Password Protected Excel File

If you have any other query or issues related to the Excel file then you can ask it in our comment section.



Summary
How to Lock and Unlock Cells, Formulas in Microsoft Excel 2016?
Article Name
How to Lock and Unlock Cells, Formulas in Microsoft Excel 2016?
Description
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
Author
Publisher Name
Repair MS Excel Blogs
Publisher Logo

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.