Lock and protect cells in Excel – (Easy step by step tutorial)

In Excel, it's common to protect all cells from being modified by others. Sometimes, you may only need to protect specific cells or formula cells that shouldn't be changed, while allowing other cells to be edited or modified, as shown in the screenshot below. In this tutorial, we'll introduce quick methods for tackling these tasks in Excel.

Lock and protect all cells in a worksheet

Lock and protect specific cells in a worksheet

• By using Format Cells and Protect Sheet features
• By uisng Kutools for Excel - with only several clicks

Lock and protect formula cells

Unlock all cells / Unprotect sheet

Unlock specific cells to be edited on a protected sheet

• By uisng Format Cells and Protect Sheet features
• By uisng Kutools for Excel - with only several clicks
• By uisng Allow Users to Edit Ranges features

Kutools for Excel to highlight the unlocked cells for editing

Video: Lock and protect cells / specific cells / formula cells

Lock and protect all cells in a worksheet

To lock and protect all cells in a worksheet, since all cells are locked by default, you just need to protect the worksheet. Follow these steps:

Step 1: Enable the Protect Sheet feature

Activate the worksheet that you want to protect, and then click "Review" > "Protect Sheet", see screenshot:

Step 2: Set password for protecting all cells

In the pop-up "Protect Sheet" dialog box, type a password into the textbox, and then click OK. In the following dialog box, reenter the password to confirm it, and click OK to close the dialogs. See screenshots:

Result:

Now, all cells in the current worksheet are protected. When you try to edit or change any of the locked cells, a warning message will appear.

Lock and protect specific cells in a worksheet

Sometimes, you just want to protect particular cells in a worksheet. In this case, the following 3 ways can do you a favor.

Lock and protect certain cells with Format Cells and Protect Sheet features

In Excel, to lock and protect specific cells, you should unlock all cells, then lock specific cells, finally, protect the sheet. Follow these step-by-step instructions:

Step 1: Select all cells and unlock them

1. Select all cells in the worksheet by pressing Ctrl + A. (Or clicking the Select All  button, the gray triangle in the top left corner of the worksheet)
2. Then, right click and choose "Format Cells" from the context menu (or press Ctr + 1) to open the "Format Cells" dialog box.
3. In the "Format Cells" dialog box, under the "Protection" tab, uncheck the "Locked" option, and then, click OK button. See screenshot:
   

Step 2: Select the specific cells and lock them

1. Now, select the cells that you want to lock and protect. (If multiple ranges need to be locked, please press Ctrl key to select the ranges.)
2. Then, press Ctr + 1 to open the "Format Cells" dialog box.
3. In the "Format Cells" dialog box, check the "Locked" option, and click OK. See screenshot:
   

Step 3: Protect the worksheet

1. Go to the "Review" tab, and click "Protect Sheet". See screenshot:
   
2. In the pop-up "Protect Sheet" dialog box, type a password and confirm it in the following dialog box, and click OK to close the dialogs. See screenshots:
   

Result:

Now, only the selected cells are protected. If you attempt to edit these specific cells, a warning box will appear as below screenshot shown:

Lock and protect certain cells with only few clicks

If you have Kutools for Excel, you can complete this task by only a few clicks in the Kutools Design ribbon.

Please click "Kutools Plus" > "Design View" to show the "Kutools Design" tab. Then follow the steps below:

Lock and protect formula cells in a worksheet

Suppose most of the time, you need to share your worksheet with other people, and wouldn’t want anyone to change or mess up the formulas but leave other cells to be editable. Fortunately, Excel makes it simple to lock and protect the formula cells. Please follow these step-by-step instructions:

Step 1: Unlock all cells in the worksheet

1. Select all cells in the worksheet by pressing Ctrl +A. (Or clicking the Select All button , the gray triangle in the top left corner of the worksheet)
2. Then, press Ctr + 1 to open the "Format Cells" dialog box.
3. In the "Format Cells" dialog box, under the "Protection" tab, uncheck the "Locked" option, and then, click OK button. See screenshot:
   

Step 2: Select all formula cells

Now that all cells have been unlocked. In this step, we should select all cells with a formula.

1.  Under the "Home" tab, click "Find & Select" > "Go To Special". See screenshot:
   
2. In the "Go To Special" dialog box, check the "Formulas" radio button and all formula types will be selected. See screenshot:
   
3. Then, click OK button, and all
4. the cells with formula are selected at once.

Step 3: Lock the formula cells

Now, go to lock the selected formula cells. Please press Ctrl + 1 to open the "Format Cells" dialog again, switch to the "Protection" tab, and check the "Locked" checkbox. At last, click OK button. See screenshot:

Step 4: Protect the worksheet

After locking the formulas, you should protect the sheet:

1. Please go to the "Review" tab, and click "Protect Sheet".
2. In the popped-out dialog box, type a password and confirm it. And then, click OK to close the dialogs. See screenshots:
   

Result:

Now, only the formula cells are protected. If you delete or edit these formulas, you will see a warning box, but, you can edit other cells you need.

Unlock all cells / Unprotect sheet

To unprotect all cells, follow these steps:

Step 1: Enable the Unprotect Sheet feature

Click "Review" > "Unprotect Sheet", see screenshot:

Step 2: Enter the password to unprotect the sheet

In the popped-out "Unprotect Sheet" dialog box, type the password into the textbox to cancel the protection. And then, click OK to close the dialogs. See screenshot:

Unlock specific cells to be edited on a protected sheet

If you want to allow users to change or edit data in certain cells and keep other cells locked, you can unprotect the specific cells or require other users to enter a password before editing these cells, or limit editing to certain users. This section will introduce some useful ways for accomplishing this in Excel.

Unlock specific cells with Format Cells and Protect Sheet features

To do this, you can use the Format Cells and Protect Sheet features. Follow these steps:

Step 1: Select and unlock the specific cells

1. Select the cells that you want to unlock, and then press Ctrl + 1 to open the "Format Cells" dialog box.
2. In the "Format Cells" dialog box, under the "Protection" tab, uncheck the "Locked" option, and then, click OK button. See screenshot:
   

Step 2: Protect the worksheet

After unlocking the specific cells, you should protect the sheet:

1. Please go to the "Review" tab, and click "Protect Sheet".
2. In the popped-out dialog box, type a password and confirm it, and then, click OK to close the dialogs. See screenshots:
   

Now, all other cells are protected, but the cells you specified can be edited.

Unlock specific cells with only few clicks by Kutools for Excel

With Kutools for Excel's "Kutools Design" tab, you can quickly solve this job by only a few clicks.

Please click "Kutools Plus" > "Design View" to show the "Kutools Design" tab. Then apply the below steps:

Unlock specific cells with Allow Users to Edit Ranges feature

In Excel, the "Allow Users to Edit Ranges" feature also can help to unlock specific ranges. With this feature, you can allow only certain people to edit certain cells or ranges with different passwords.

Step 1: Go to enable the Allow Users to Edit Ranges feature

Click "Review" > "Allow Edit Ranges", see screenshot:

Step 2: Specify unlocked ranges and set passwords

1. In the popped out "Allow Users to Edit Ranges" dialog box, click "New" button, see screenshot:
   
2. In the "New Range" dialog box, do the following operations:
   • In the "Title" box, type a name for the range that you want to unlock;
   • In the "Refers to" cells box, click the button to select the data range that you want to unlock;
   • In the "Range password" box, enter a password, or, leave this box empty to allow others to edit the range without password.
   • 
3. Then, click OK button, and another "Confirm Password" dialog will appear. please retype the password again, see screenshot:
   
4. Click OK to go back to the "Allow Users to Edit Ranges" dialog box. The new range is listed into the list box. If you want to add more ranges with different passowrds, please repeat steps 1- 3 of Step 2.
5. Still in the "Allow Users to Edit Ranges" dialog box, click "Protect Sheet" button to protect the whole sheet. See screenshot:
   
6. In the following dialog boxes, type a password and confirm it.(Here, you’d better type a different password than you used to unlock the ranges.) And then, click OK to close the dialogs. See screenshots:
   

Result:

Now, when you need to edit the unlocked cells, you should type the password you supplied for that range. (If you didn't set a password for the range, this dialog will not appear, and you can edit the cells directly.)

 Kutools to highlight the unlocked cells quickly for editing

If a protected worksheet contains both locked and unlocked cells, you can quickly identify which cells can be modified and which are off-limits by highlighting the unlocked cells. Kutools for Excel’s Highlight Unlocked feature can help to highlight all the unlocked (editable) cell with a specific color.

1. In a protected sheet, click "Kutools Plus" > "Design View" to show the "Kutools Design" tab.
2. Under the "Kutools Design" tab, please click "Highlight Unlocked" option, all unlocked cells are covered by a color temporarily, you can modify or edit them freely as you need. (If close this Kutools Design tab, the color will disappear.)

Related Articles: