Allow Changes on a Protected Worksheet

It’s easy to protect a worksheet in Excel, but it’s not so obvious how you leave some of the cells unprotected, to allow changes on a protected worksheet. You can follow this tutorial to learn how to do that, and maybe you’ll even see the weird dialog box heading that I show below.

Unlock Some Cells

For example, on a data entry sheet, you might have some cells with formulas, that you don’t want users to mess up. However, you want the users to be able to enter the date, item name and quantity for an order.

In the screenshot below, the selected cells — A2:C6 — should be unprotected, so users can enter data. The rest of the cells should be protected, so the formulas cannot be changed or deleted.

Step 1: Unlock the Data Entry Cells

By default, all the cells on the worksheet will be locked, if you protect the worksheet. To allow changes to some cells, you have to change the protection setting for those cells.

1. On the worksheet that you’re going to protect, select the cells that users will be able to change — cells A2:C6 in this example.
2. On the keyboard, press Ctrl + 1 to open the Format Cells dialog box.
3. On the Protection tab, remove the check mark from Locked.
4. Click OK, to close the dialog box

Note: In the screenshot above, you can see that the dialog box name shows as “Custom Lists”, even though it’s the Format Cells dialog box.

It seems to appear if you open the Format Cells dialog box, go to the Protection tab, and then close the dialog box. The next time you press Ctrl+1, the Custom Lists heading is on the Format Cells dialog box.

Step 2: Protect the Worksheet

1. Go to the worksheet that you’re going to protect.
2. On the Excel Ribbon, click the Review tab
3. Click Protect Sheet.

Watch the Video

To see the steps for allowing changes in some cells on a protected Excel worksheet, watch this short Excel video tutorial.

_________________