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.

Create a new blank workbook
Enter dummy data in few cells as shown in the screenshot below
You can select the entire sheet by clicking on the top left section of the sheet as highlighted in the screenshot below
Right-click to get the various options from the right-click menu
Now, select Format Cells option from the drop-down
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 unlocked
Now select the section of cells that you would like to lock as selected in the screenshot below
Right-click and select format cells and lock the cells by checking Locked as shown below
Click OK to close the pop-up
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 sheet

Upon selection of Protect sheet, a pop-up appears where you can enter the password to protect the selected the cells
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
If you try to edit any of the cells, you’ll get the following error
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.

Select unprotect sheet under the Changes group of the Review tab or right click on the sheet name and select Unprotect

A pop-up appears which asks for the password. Enter the password and click OK
The worksheet is now unprotected, you’ll be able to edit the contents of the locked cells again
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.