How to Protect or Lock cells in Excel for Editing

To protect cells from being edited, you need to lock them so that anyone using your spreadsheet will not be able to edit the locked cells.

Locking cells in Excel is very important as it protects your worksheet against things like accidentally deleted formulas, mistakenly scrambled formatting and unintentionally modified data values.

Accidents and mistakes like these can lead to serious problems. That’s why you need to learn how to lock cells in Excel to prevent users from accidentally altering formulas and data on the worksheet.

To learn how to protect cells in Excel, let’s take a look at the following example where we will lock cell G7 which contains a VLOOKUP formula, after that, you’ll also learn how to lock all cells in Excel at once.

How to lock certain Cells in Excel

If you protect a worksheet, the whole worksheet becomes read-only, which means none of the cells can be edited again.

However, you may want to protect your worksheet so that only a few selected cells can be edited (locking only some cells in the worksheet)

To protect individual cells in Excel, you have to unlock all cells first, since all cells are locked by default, then lock the cell or selected cells you wish to lock, and then protect the worksheet. Below will show you how:

• Step #1: Select all the cells in the sheet.

To select all cells, just click on the small triangle at the left top corner of the grid lines, just after column A.

• Step #2: Right-click, and then click Format Cells in the menu.

The Format Cells dialog appears.

• Step #3: Click the protection tab. Then click the “Locked” checkbox to turn it off. Then click OK.

NOTE: This action will unlock all the cells on the sheet. Now you can lock your individual cells.

• Step #4: Select the cell or cells you wish to lock. Then right click and choose Format Cells from the menu.

In our example, cell G7 is the cell we want to clock. So, we’ll right-click cell G7 and click Format Cells from the menu.

If you want to lock more than one cell, just select the range you wish to lock. Or hold down the Control (Ctrl) key and one after the other, click each cell that’s to be locked, then right click and choose Format Cells.

The Format Cells window appears.

• Step #5: Click the protection tab. And then click the “Locked” checkbox to turn it on. Then click OK.

Note: when you also check the “Hidden” checkbox, the cell’s content will not appear in the formula bar. The cell will still appear in the worksheet, but if the cell uses a formula, you can’t see the formula. 

• Step #6: Protect the sheet

Now, if you protect the sheet, Excel will only lock cell G7 instead of the entire cells in the worksheet.

To protect a worksheet:

• right-click on the worksheet tab and choose Protect Sheet.

• The Protect Sheet dialog appears. Enter a password in the “Password to unprotect sheet” field. This field is optional and you can ignore it so that anyone using your spreadsheet can unprotect it without using a password.
• Click the checkbox labeled “Protect worksheet and contents of locked cells”.
• From the “Allow all users of this worksheet to” list, turn on the things you want people to be able to do with the locked cells.
• Then Click OK.
• Repeat the first step to unprotect the sheet again.

Cell G7 which contains the formula is now locked. Meaning the content of the cell can no more be altered. If you try to edit a locked cell, Excel warns you that the cell you’re trying to change is a locked cell on a protected sheet.

To be able to edit the content in a locked and protected cell, you must unprotect the worksheet again.

What users can do with a locked cell

By default, when the Protect Cells windows appears, Excel selects only the first two options whilst restriction every other option that let you define what users can do to your locked cells.

Thus, when you choose Review→Changes→Protect Sheet, Excel will not carry out the command until you tell it what parts of the sheet you want to protect.

Here’s is a detailed breakdown of the choices:

• Select Locked Cells: This option is selected by default. It allows users of your spreadsheet to select locked cells using the keyboard or mouse. If you want to prevent people from moving to locked cells, you’ll have to turn off this checkbox.
• Select Unlocked Cells: This option is also selected by default. It allows users of your spreadsheet to select unlocked cells using the keyboard or mouse. If you want to prevent people from selecting the unlocked cells, you’ll have to turn off this checkbox.
• Format cells: The user can format individual cells if this option is checked.
• Format columns: The user can hide or change the width of columns if this option is checked.
• Format rows: The user can hide or change the height of rows if this option is checked.
• Insert columns: The user can insert a new column if this option is checked.
• Insert Row: The user can insert a new row if this option is checked.
• Insert hyperlinks: The user can insert hyperlinks (even in locked cells) if this option is checked.
• Delete columns: The user can delete columns if this is checked.
• Delete rows: The user can delete rows if this option is checked.
• Sort: If this option is checked, the user can sort data in a range provided the range doesn’t have a locked cell or cells.
• Use AutoFilter: The user can use existing auto filtering if this option is checked.
• Use PivotTable & PivotChart: If this option is checked, the user can change the layout of pivot tables or create new pivot tables.
• Edit objects: If this option is checked, you can make changes to objects (like shapes) and charts, and also insert and delete comments.
• Edit scenarios: The user can use the scenario manager features if this option is checked.

How to lock all cells in Excel

Locking all cells is very straight forward since all cells are already locked by default.

Thus, to lock all cells in Excel, just switch on the worksheet protection without making any changes, and your whole worksheet will become locked (read-only).

However, if some of your cells are locked and others unlocked, you’ll have to follow the steps below to lock all cells:

1. Select all the cells in the sheet.
2. Right-click, and then click Format Cells in the menu.
3. Click the protection tab. Then click the “Locked” checkbox to turn it on if it is not already checked.
4. Then click OK.
5. Protect the sheet

If you protect the sheet now, all the cells in your worksheet will be locked and cannot be edited.