How to Protect Specific Cells in Excel​ (5 Suitable Ways)

Protecting specific Excel cells from editing prevents unwanted changes, deletions, and data loss. By default, all cells in Excel are set as Locked. However, locking has no effect until you lock the sheet using the Protect Sheet feature.

Therefore, to protect specific cells, first, you must unlock the cells you want to remain editable. After that, apply sheet protection so that the remaining cells stay locked.

In our sample project task dataset, we have columns for employee names, tasks, assigned dates, deadlines, and status. We want to lock the first 4 columns (A1:D10) while keeping the last column (Column E) editable.

To protect specific cells that you select, follow the steps given below:

➤ First, we need to unlock all cells. For this, select all cells in your worksheet by clicking the triangle at the intersection of the row numbers and column letters (or press CTRL + A).
➤ Right-click on your selection and choose the Format Cells option from the menu.

➤ As Excel opens the Format Cells dialog box, click on the Protection tab, uncheck the Locked box, and press Ok.

➤ Now, select the cells you want to lock. We’re choosing the range A1:D10. Click and drag to select adjacent cells. For non-adjacent cells, press and hold the  Ctrl  key while you click on individual cells or drag to select ranges of cells you want to include.
➤ Go to the Home tab and click on the Format drop-down in the Cells group.

➤ Choose Lock Cell from the menu (located under the protection group).

➤ Click on the Format drop-down again (or go to the Review tab) and choose Protect Sheet this time.

➤ In the Protect Sheet pop-up, you can type a password or leave the password field empty. Check or uncheck the boxes to give special permissions such as formatting, deleting, or adding columns and rows. Press Ok when you’re done.

➤ When prompted, retype your password and click Ok.

➤ Excel will now lock the cells you’ve selected, displaying the following warning for any edit attempts.

➤ However, the cells you haven’t locked remain editable like the range E1:E10 in our dataset.

In this method, we’ll keep certain cells editable while others stay protected. Here’s how:

➤ Select the range of cells you want to keep editable for the users (E2:E10). Right-click on your selection and choose Format Cells.

➤ From the new window, go to the Protection tab >> uncheck Locked >> press Ok.

➤ Click on the Review tab >> Protect Sheet. Set a password (optional) and click Ok. Reenter your password and press Ok.

➤ Excel will now protect the entire sheet except for the cells you selected.

If multiple people are working on the same sheet and you want to give different users access to certain cells, follow the steps given below:

➤ Go to the Review tab and select Allow Edit Ranges from the Protect group.

➤ In the new window, click on the New button and Excel will open the New Range dialog box.

➤ Give your range a name (e.g., Range1, MyRange) in the Title box. Proceed to the Refers to Cells field and specify the range of cells you want to make editable (E2:E10).
➤ Now, you can apply two optional settings. Use the Password button to set a password to edit this specific range. You can also click on Permissions to give edit access to specific persons (for User-Level Security). When you’re done, click Ok.

➤ As the Allow Users to Edit Range window reappears, click on Protect Sheet.

➤ Set a password if needed and press Ok. Retype your password when prompted. Click Ok.
➤ From now on, users can only edit the selected cells in Column E while others remain protected.

With Excel’s Go To Special feature, you can highlight specific cells containing formulas, blanks, comments, etc. Once highlighted, we can use the Protect Sheet feature to lock those cells. Here’s how:

➤ Select your entire dataset, right-click, and click Format Cells. From the Format Cells window, go to the Protection tab >> uncheck Locked >> click Ok.
➤ Highlight your data range, press  Ctrl  +  G  to open the Go To dialog box, and click on Special.

➤ Depending on the type of cells you want to lock click on any of the given options. We chose Formulas. Click Ok.

➤ As Excel highlights the cells containing formulas (D2:D10), click on the Home tab >> Format drop-down >> Lock Cell.

➤ Go to the Review tab >> Protect Sheet >> set a password (optional) >> Ok. Reenter if prompted and press Ok.

➤ Excel will now lock the cells with formulas and keep the remaining cells editable.

With a VBA macro, you can specify the cells or ranges you want to protect with or without a password. Here are the details:

➤ Right-click on the sheet name and choose View Code from the menu.

➤ In the blank field, copy and paste the following code:

Sub LockSelectedCells()
    Dim rng As Range
    Dim pwd As String
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ' Ask user to select cells
    On Error Resume Next
    Set rng = Application.InputBox("Select the cells or ranges you want to lock:", _
                                   "Lock Cells", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then
        MsgBox "No range selected. Operation canceled.", vbExclamation
        Exit Sub
    End If
    ' Ask for optional password
    pwd = InputBox("Enter a password to protect the sheet (leave empty for none):", _
                   "Password Protection")
    ' Unlock all cells first
    ws.Cells.Locked = False
    ' Lock the chosen range
    rng.Locked = True
    ' Protect worksheet with or without password
    If pwd <> "" Then
        ws.Protect Password:=pwd, UserInterfaceOnly:=True
        MsgBox "Cells locked and sheet protected with a password.", vbInformation
    Else
        ws.Protect UserInterfaceOnly:=True
        MsgBox "Cells locked and sheet protected without a password.", vbInformation
    End If
End Sub

➤ Press  F5  or click on the Run tab >> Run Sub/UserForm.

➤ As Excel opens the Lock Cells dialog box, go back to the Excel tab and click on the upside arrow to select the cells or ranges you want to lock. You can press and hold the  Ctrl  key to select non-adjacent cells or ranges. Press Ok.

➤ Enter a password (optional) or leave the field empty. Click Ok.

➤ All the cells you selected are now locked.

Frequently Asked Questions

How to unlock the locked cells in Excel?

If you want to make locked cells editable again, go to the Review tab >> Unprotect Sheet. Enter the correct password if needed. Select the cells you want to unlock, right-click on them, and choose Format Cells from the menu. Now, open the Protection tab, uncheck Locked, and click Ok.

How to lock cells in Excel but allow formatting?

Select your entire data range right-click, and choose Format Cells. In the Format Cells window, click on the Protection tab >> uncheck Locked >> click Ok. Now, choose the cells you want to lock, click on the Home tab >> Format drop-down >> Lock Cell. Click on the Format drop-down again and select Protect Sheet. In the Protect Sheet dialog box, check the Format Cells box and press Ok.

How to prevent deleting any sheets in Excel?

To prevent Excel sheets from deleting, you can use the Protect Workbook feature. For this, go to the Review tab and choose Protect Workbook. This prevents adding, deleting, hiding, or moving sheets, but does not protect cell contents inside sheets.

Concluding Words

With Excel’s protection and security features, you can protect or unprotect specific cells anytime in just a few clicks. To allow edit access to any user, you need to enter the correct password.

Keep in mind that Excel’s basic security features can be overwritten. So, avoid publicly sharing Excel files with sensitive information even if you’ve locked them from editing.