Allow Changes on a Protected Worksheet

image It’s easy to protect a worksheet in Excel, but it’s not so obvious how you leave some of the cells unprotected, so users can make changes to those cells. 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.

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.

ProtectSheet03

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

ProtectSheet01

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.

ProtectSheet02

Watch the Video

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

Allow Users to Change Cells in Protected Sheet


_________________

 

 

You may also like...

14 Responses

  1. Bob Ryan says:

    Debra – I see a place for the video, but I don’t see the actual video. I do see the video for the article on Monday.

  2. Thanks Bob — it’s fixed now. Everything looked okay in Firefox, but IE8 didn’t seem to like the video embed code.

  3. Keggi says:

    Hi,

    I don’t like protecting sheets by unlocking cells, because users can mess with formulas by cutting and pasting.

    I like it better to use “Allow Users to Edit Ranges” (<–name in Excel 2007) functionality, bacause in this approach cells cannot be cut.

    If someone knows how to handle these User Edit Ranges in VBA (unlock and lock, define new areas etc.) please share or send a link.

  4. […] Allow Changes on a Protected Worksheet, Debra Dalgleish shows us how to manually protect a worksheet while allowing changes to specific […]

  5. Carol says:

    I have been able to lock/unlock cells and protect worksheet, but my issue is that when worksheet is protected, I am unable to add/edit comments into unlocked cells.

    Does anyone have suggestions for getting around and allowing comments to be added.

    • Szymon says:

      Carol,
      one solution would be enabling ‘objects editing’ under ‘protect sheet’, another, just pasting in a cell carrying a comment. Rgs

  6. Alok Kumar jena says:

    How to Protect format.any one edit data but not change format

  7. mary says:

    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…

  8. Mitch says:

    Thanks – this easily fixed my problem! Much obliged!

  9. Sonya says:

    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

  10. Naya says:

    What is the best way to keep ctrl+H functionality enabled in a protected sheet with locked cells?

  11. Al Ostendorf says:

    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

  12. Lori says:

    I understand the protection process and how to unlock cells I want to allow users to access / change. However, I also want certain unlocked areas to not only allow data entry but, let the users change formatting of those unlocked cells/area too. Is this possible?

Leave a Reply to Lori Cancel reply

Your email address will not be published. Required fields are marked *