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.
- 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.
- On the keyboard, press Ctrl + 1 to open the Format Cells dialog box.
- On the Protection tab, remove the check mark from Locked.
- 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
- Go to the worksheet that you’re going to protect.
- On the Excel Ribbon, click the Review tab
- 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.
_________________
How to Protect format.any one edit data but not change format
Hi,
I’m having an issue with protecting a worksheet. Perhaps I just don’t know how it works. I unlock the cells i want the users to be able to edit, then protect the sheets. It works fine for me. When a user edits the “editable” fields it works fine, but when they go to save the file it indicates the sheet is protected and they cannot save it. Anyone know what I’m doing wrong? Thanks for any insight…
Thanks – this easily fixed my problem! Much obliged!
This helped me a lot. Viewers remember before selecting the protection sheet icon you have to click off of the selected cells or it will still lock everything up. Thanks a bunch who posted this.
Sincerely ,
Sonya Harrison
What is the best way to keep ctrl+H functionality enabled in a protected sheet with locked cells?
I want to lock a worksheet but unlock some data entry cells and check box cells. I also want to allow the selection of some cells that contain formulas. This is to identify the cell address. I can get part of it to work but not all of the cells to work at the same time
Thanks
Al