Lock and Unlock specific cells in Excel

Locking and unlocking cells in Excel is an important skill to learn, especially if you’re sharing files with other people, as it is important to know how to restrict others from making changes to the important cells in an Excel workbook and at the same time give the user flexibility to edit other cells.

In this article, you’ll understand how to deal with such situations by locking and unlocking certain specific cells of a sheet in an Excel file.

Locking specific cells and protecting the sheet

By default, all the cells in a worksheet will be locked, but this doesn’t have any effect until you protect the workbook. Let’s take an example and see how all this works.

  1. Create a new blank workbook
  2. Enter dummy data in few cells as shown in the screenshot belowLocking and Unlocking cells in Excel 1
  3. You can select the entire sheet by clicking on the top left section of the sheet as highlighted in the screenshot belowLocking and Unlocking cells in Excel 2
  4. Right-click to get the various options from the right-click menu
  5. Now, select Format Cells option from the drop-downLocking and Unlocking cells in Excel 3
  6. This opens up a pop-up window, under the Protection tab, you’ll notice that Locked option is ticked. By default, all the cells are locked. Make sure to uncheck the Locked option so that all the cells are unlockedLocking and Unlocking cells in Excel 4
  7. Now select the section of cells that you would like to lock as selected in the screenshot belowLocking and Unlocking cells in Excel 5
  8. Right-click and select format cells and lock the cells by checking Locked as shown belowLocking and Unlocking cells in Excel 6
  9. Click OK to close the pop-up
  10. Then, go to protect sheet under the Changes group of the Review tab or right click on the Sheet name to get the protect sheet option for the sheetLocking and Unlocking cells in Excel 7

 

Locking and Unlocking cells in Excel 8

  1. Upon selection of Protect sheet, a pop-up appears where you can enter the password to protect the selected the cellsLocking and Unlocking cells in Excel 9
  2. Enter a password and click OK, there will be another window which asks for reconfirmation of the password. Once you reconfirm the password, all the selected cells are locked in the worksheet and are now protected and cannot be edited
  3. If you try to edit any of the cells, you’ll get the following errorLocking and Unlocking cells in Excel 10
  4. When you get such error, you’ll have to unprotect the worksheet to edit the locked cells. When you try to perform this task, Excel will prompt for the password and only users who know the password can unprotect the worksheet. However, you can edit other cells on the sheet

Hence, you can restrict the end user from changing the contents of specific cells on the sheet. Since the sheet is now locked, even if you forget the password then you can’t access the worksheet anymore. You need to be careful while choosing the password.

Unprotecting the sheet

Once the sheet is protected and if you know the password the following steps need to be followed to unprotect the sheet.

  1. Select unprotect sheet under the Changes group of the Review tab or right click on the sheet name and select UnprotectLocking and Unlocking cells in Excel 11

 

Locking and Unlocking cells in Excel 12

  1. A pop-up appears which asks for the password. Enter the password and click OKLocking and Unlocking cells in Excel 13
  2. The worksheet is now unprotected, you’ll be able to edit the contents of the locked cells again
  3. The protect and unprotect appear at the same location. So, when a sheet is protected, you’ll have an option to unprotect it and if the sheet is not protected, you’ll get an option to protect it.

We have looked at options and techniques to lock and protect specific cells of a worksheet. As mentioned initially, protection of the sheet applies only to those cells which are locked. After protection of the sheet, the end user without password can only edit those cells which were not Locked.

Use this link below to download the excel file. The password for protection of sheet is 1234.

Download Excel file here

Please share

Did you find this useful?  Why not share the love and tell your friends on the different social media platforms.

Shreyas M

Shreyas M

I'm a Senior Business Analyst by profession. Working with Excel is my passion. I've been working on Excel since 2011, it has been a great learning experience for me so far. I would like to share my knowledge with fellow Excel users!
Shreyas M
By |2018-03-23T01:13:12+00:00March 22nd, 2018|Uncategorized|0 Comments

About the Author:

I'm a Senior Business Analyst by profession. Working with Excel is my passion. I've been working on Excel since 2011, it has been a great learning experience for me so far. I would like to share my knowledge with fellow Excel users!

Leave A Comment

Send this to a friend