Allow specific users to edit ranges in a protected worksheet

Excel offers you an ability to assign user-level permissions to different areas on a protected worksheet. You can specify which users can edit a particular range while the worksheet is protected. As an option, you can require a password to make changes.

    1.    On the Review tab. in the Changes group, click Allow Users to Edit Ranges:

Changes group in Excel 2016

Note: This command is available only when the worksheet is not protected.

    2.    Do one of the following:

Allow Users to Edit Ranges in Excel 2016
  • To modify an existing editable range, select it in the Ranges unlocked by a password when sheet is protected box, and then click Modify.
  • To delete an editable range, select it in the Ranges unlocked by a password when sheet is protected box, and then click Delete.
  • To add a new editable range, follow next steps:

       1.    Click New to open dialog box:

New Range in Excel 2016

       2.    In the Title box, type the name for the range that you want to unlock.

       3.    In the Refers to cells box, type an equal sign (=), and then type the reference of the range that you want to unlock. You can also click the Collapse Dialog button, select the range in the worksheet, and then click the Collapse Dialog button again to return to the dialog box.

       4.    In the Range password box, type a password that allows access to the range. The password is optional. If you don't supply a password, then any user can edit the cells.

       5.    Click Permissions to open dialog box:

Permissions in Excel 2016

       6.    Add users that you want to be able to edit the ranges or remove them, and then click OK three times.

    3.    After all changes, in the Allow Users to Edit Ranges dialog box, click the Protect Sheet... button to open the Protect Sheet dialog box:

Protect Sheet in Excel 2016

    4.    In the Allow all users of this worksheet to list, select the elements that you want users to be able to change (see Protect sheet options).

    5.    In the Password to unprotect sheet box, type a password for the sheet, click OK, and then retype the password to confirm it.

Note: The password is optional. If you don't supply a password, then any user can unprotect the sheet and change the protected elements. Make sure that you choose a password that is easy to remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.