How to lock cells in Excel to protect them

Sometimes you may want to lock all or specific cells in Excel to protect the contents of those cells from being modified or deleted. Below are the steps on how to lock cells in Excel.

Lock cells in Excel

The easiest way to lock cells in Excel is to protect the entire worksheet and this prevents others from deleting or editing the content in any of its cells.

However, locking all cells in Excel by protecting the entire worksheet prevents others from working on it.

Therefore, this method is not suitable if you share the worksheet with others or want others to be able to work on the worksheet.

Fortunately, it is also possible to lock specific cells in Excel and this will prevent others from making changes only in those selected cells.

1. Lock all cells in Excel worksheet

As mentioned above, you can lock all cells in Excel by protecting the worksheet. By default, all cells in a protected worksheet are locked.

1. Click on that review tab and then click protective sheet Option in the Changes group.

Protect Sheet in Excel

2. In the box that appears, select Protect Sheet Protect the worksheet and the contents of locked cells Option > Type a password (optional) > Select actions you want to allow (select locked cells, select unlocked cells, etc.) and click OK.

Protect sheet dialog box in Excel

3. If you entered a password, you will be asked to confirm the password again.

Once the worksheet is protected, all cells in the worksheet will be locked and Excel will display the message “The cell or chart you are trying to open is on a protected sheet” when someone tries to modify the worksheet.

Protected worksheet message in Excel

In case you want to edit the cells, you need to unlock the worksheet first by clicking review tab > Remove sheet protection.

Unprotect worksheet in Excel

Note: You must enter a password if the worksheet you want to unlock was password protected.

2. Lock specific cells in Excel

Follow the steps below to lock only specific selected or specific cells in Excel worksheet.

1. Choose whole leaf and click the Dialog launcher icon is in the Orientation group of the Home tab.

Launch dialog box in Excel

2. In the Format Cells dialog box, click Protection tab and disable the Locked Crate.

Unlock cells in Excel

3. Click on OK to save the changes.

4. Now just select the specific cells you want to block and click on the Dialog box launcher Icon in the Alignment group of the Home tab.

Select cells to lock in Excel

In this case, we selected cells D2 through D5 to prevent others from making changes to the rates in the worksheet.

5. In the Format Cells dialog box, click the Protection tab and select it Locked Possibility.

Lock cells in Excel

6. Then click on the review tab and click protective sheet Option in the Changes group.

Protect Sheet in Excel

7. Select “Protect Sheet” from the screen Protect the worksheet and the contents of locked cells Option > Select the actions you want to allow on this worksheet (select cells, insert rows, format cells, etc.) and click OK to save the settings.

Protect sheet dialog box in Excel

After that, only the selected cells in the worksheet will be locked and all other cells will be unlocked.

Also, users can work on the worksheet and perform any allowed actions that you have selected in the Protect Sheet window.

3. Hide formula in locked cells

Even if a cell is locked in Excel, if someone clicks the locked cell, the formula in the locked cell will be visible.

Follow the steps below to lock cells and also hide the formula in them.

1. Choose cells that you want to lock and hide the formula, and click the Dialog box launcher Icon in the Orientation section of the Home tab.

Select cells to lock in Excel

2. In the Format Cells window, enable both Locked and Hidden options.

Lock and hide formula in Excel

3. Click on OK to save this setting.

This will lock the selected cells and also hide the formula when someone selects or clicks the locked cells.

Protect sheet option grayed out in Excel

If you find that the Protect Sheet option is greyed out, you are probably dealing with a shared worksheet. To fix this problem, you need to turn off sharing for the workbook.

1. Click on that review tab and click share workbook Option in the Changes group.

Split workbook option in Excel

2. In the Share Workbook window, uncheck the box Use the old shared workbooks feature… or Allow changes by more than one user… option and click OK.

Share worksheet window in Excel

Excel Protect Sheet option may also be grayed out because multiple worksheets are selected.

To fix this, right click on any one Worksheet tab and click Ungroup sheets option in the displayed menu.

Ungroup worksheets in Excel

I hope this gave you a good understanding of how to lock cells in Excel and protect your work.

Related

Leave a Reply

Your email address will not be published.