Technology

How to Lock Cells in Microsoft Excel to Prevent Editing

If you want to restrict editing in a Microsoft Excel worksheet to certain areas, you can lock cells to do so. You can block edits to individual cells, larger cell ranges, or entire worksheets, depending on your requirements. Here’s how.

Enabling or Disabling Cell Lock Protection in Excel

There are two stages to preventing changes to cells in an Excel worksheet.

First, you’ll need to choose the cells that you want to allow edits to and disable the “Locked” setting. You’ll then need to enable worksheet protection in Excel to block changes to any other cells.

RELATED: How to Protect Workbooks, Worksheets, and Cells From Editing in Microsoft Excel

By default, Excel will assume that, when you “protect” a worksheet from editing, you want to prevent any changes to all of its cells. If this is the case, you can skip to the next section.

How to Disable Cell Lock Protection in Excel

To allow or block changes to cells in Excel, open your Excel workbook to the sheet you’re looking to edit.

Once you’ve selected the worksheet, you’ll need to identify the cells you want to allow users to modify once your worksheet is locked down.

You can select individual cells or select a larger range of cells. Right-click the selected cells and select “Format Cells” from the pop-up menu to proceed.

To enable or disable lock protection to Excel cells, select the cells you wish to allow changes to, right-click and select the "Format Cells" option.

In the “Format Cells” menu, select the “Protection” tab. Uncheck the “Locked” checkbox to allow changes to those cells once you’ve protected your worksheet, then press “OK” to save your choice.

In the "Protection" tab, uncheck the "Locked" checkbox to disable lock protection for that cell, then press "OK" to save.

With the “Locked” setting removed, the cells you’ve selected will accept changes when you’ve locked your worksheet. Any other cells will, by default, block any changes once worksheet protection is activated.

Enabling Worksheet Protection in Excel

Only cells with the “Locked” setting removed will accept changes once worksheet protection is activated. Excel will block any attempt to make changes to other cells in your worksheet with this protection enabled, which you can activate by following the steps below.

How to Enable Worksheet Protection in Excel

To enable worksheet protection, open your Excel workbook and select the worksheet you want to restrict. From the ribbon bar, select Review > Protect Sheet.

Select Review > Protect Sheet to enable lock protection for your active worksheet.

In the pop-up menu, you can provide a password to restrict changes to the sheet you’re locking, although this is optional. Type a password into the text boxes provided if you want to do this.

By default, Excel will allow users to select locked cells, but no other changes to the cells (including formatting changes) are permitted. If you want to change this, select one of the checkboxes in the section below. For example, if you want to allow a user to delete a row containing locked cells, enable the “Delete Rows” checkbox.

When you’re ready, make sure that the “Protect Worksheet and Contents of Locked Cells” checkbox is enabled, then press “OK” to save your changes and lock the worksheet.

In the "Protect Sheet" box, provide a password (if required), enable the "Protect worksheet and contents of locked cells" checkbox, confirm the changes you want to allow, then press "OK" to save.

If you decided to use a password to protect your sheet, you’ll need to confirm your changes using it. Type the password you provided into the “Confirm Password” box and press “OK” to confirm.

If you're locking an Excel worksheet with a password, confirm the password in the "Confirm Password" box and press "OK" to save.

Once you’ve locked your worksheet, any attempts to make changes to locked cells will result in an error message.

An example of an Excel error message following an attempt to edit a locked cell.

You’ll need to remove the worksheet protection if you wish to make any changes to locked cells afterwards.

How to Remove Worksheet Protection in Excel

Once you’ve saved your changes, only the cells you’ve unlocked (if you’ve unlocked any) will allow changes. If you want to unlock other cells, you’ll need to select Review > Unprotect Sheet from the ribbon bar and provide the password (if used) to remove worksheet protection.

To remove lock protection from an Excel worksheet, press Review > Unprotect Sheet.

If your worksheet is protected with a password, confirm the password by typing it into the “Unprotect Sheet” text box, then press “OK” to confirm.

Type your password into the "Unprotect Sheet" box, then press "OK" to confirm.

This will remove any restrictions to your worksheet, allowing you to make changes to previously locked cells. If you’re a Google Docs user, you can protect Google Sheets cells from edits in a similar way.

RELATED: How to Protect Cells From Editing in Google Sheets



File source

Tags
Show More

Related Articles

Back to top button
Close