Allow Only Certain People to Edit Specific Cells in Excel

Add to Favorites

How to allow only certain people to edit certain cells or ranges in Excel.

This is a security feature that you can setup so that a user has to have a specific password in order to edit parts of a worksheet.  This allows you to have more robust Excel files and to reduce the chance that someone messes-up something in the worksheet.

Steps to Allow Only Certain People to Edit Specific Cells in Excel

  1. Select the cell or range of cells that you want only a certain person to be able to edit.
  2. Go to the Review tab and click Allow Users to Edit Ranges.
  3. A window will open; in that window, click New...

  4. In the window that opens, enter a title for the range, this is what you will see when you manage ranges that can be edited; make sure the cells that you want to be edited by the user are entered correctly, this should have happened already if you selected the desired cells before you got here; then, enter a password that the user must have in order to edit this range of cells.

    When you are done, hit the OK button.
  5. In the next window, you will have to re-enter the password you used in the previous window and then hit OK.
  6. Now, you will see the password protected range of cells and the title for that range in a list.
  7. Hit New to add another range if you want.  (Otherwise hit OK and skip to step 11)
  8. Here, you will repeat step 4.  The difference now is that you will need to select the new range that you want to be password protected.  To do that, click the little box to the right of the input box for the Refers to cells: section.

    Then, select the desired cells:

    Then, hit Enter or click the little box on the right side of the tiny window again.

    Now, your new range has been selected and is ready to go.  Finish filling out the Title and the Password like we did above and then hit OK and re-enter the password.
  9. Now, we have two password protected ranges.
  10. Repeat Steps 7 to 9 as much as you need to add as many password protected ranges as you need.  When you are done, hit OK on the window that lists the ranges, the one shown in Step 9.
  11. We are not done yet.  In order to make this work, we need to protect the worksheet.  Make sure that you are still viewing the worksheet where you added the password protected ranges and then go to the Review tab and click Protect Sheet.
  12. In the window that opens, type a password that will be used to lock the entire worksheet.  Once the worksheet is locked, only those that have a password to edit specific ranges will be able to edit anything, unless, of course they have the password that was used to lock this sheet.  After entering the password, hit OK.
  13. Re-enter the password on the next window and hit OK.
  14. That's it!

Now let's test it out.

When you try to edit a cell that was password protected, you must have the correct password in order to edit that cell.

Once you enter the password, you can freely edit that range of cells.

Remove Password Protection from Cells

To remove password protection from the sheet or a specific range you must first remove the worksheet protection and then remove that specific range's protection.

Unprotect the Worksheet: Review tab > Unprotect Sheet > then enter the correct password and hit OK.

Unprotect Specific Ranges: Review tab > Allow Users to Edit Ranges > select the desired range from the list > hit Delete > hit OK.

Notes

Make sure to use different passwords for each range so that a user cannot unlock and edit a range that they are not supposed to be able to access.

Any ranges or cells that are not allowed to be edited by specific users will not be editable by anyone unless they have the password used to unlock or "unprotect" the worksheet.  This is standard behavior for protected worksheets.

You can get to the Protect Sheet window from the Allow Users to Edit Ranges window by clicking the Protect Sheet... button in the lower left corner of the window (you can see this button in Step 9 above).

Passwords:

The passwords below are to unlock the ranges and the sheet protection for this worksheet.  Never EVER use these passwords for anything!  They are kept simple only for this example.  Make sure to download the sample workbook so you can try this out.

Store 1: 123456

Store 2: qwerty

Protect Sheet: 123456

Like this tutorial? Follow us on Google +

Excel Version: Excel 2007, Excel 2010, Excel 2013
Downloadable Files: Excel File