Skip to content

Using Excel’s “Allow Users to Edit Ranges” Command

    Excel allows you to set up permissions so that particular ranges can only be edited by specified users.

    The Allow Users to Edit Ranges command is on the Ribbon’s Review tab in the Changes group.  If the command button is greyed out it may be because the worksheet is protected.  Try unprotecting the worksheet before following these instructions.

    allow users to edit ranges 1

    Click on the Allow Users to Edit Ranges button to open the Allow Users to Edit Ranges dialog box. For your first permission setting, click on the New button.

    allow users to edit ranges 2

    Give the range a name, specify which cells make up that range and set a password that the user or users must know to edit the range.

    allow users to edit ranges 3

    The next step is to select which users will have permission to edit this range.  To set permissions click the Permissions button in the New Range dialog box. In the Permissions dialog box click the Add button.

    allow users to edit ranges 4

    In the Select Users or Groups dialog box either enter the name or names of the user/s…

    allow users to edit ranges 5

    or click on Advanced and use the Find Now button to search for users.  Once found you can select users from a list and confirm your choice with the OK button.

    allow users to edit ranges 6

    Click on OK in the Select Users or Groups dialog

    Now specify which users need to know the password to edit the range.  For each user that does, select the user in the Permissions dialog box and then check the Deny check box.  You will need to repeat this for each user you require to know the password.

    allow users to edit ranges 7

    Click on OK to confirm.  A Windows Security dialog explains that deny takes precedence over allow entries, so if a user has been assigned allow through one group and deny through another, deny wins.  Click the Yes button.

    allow users to edit ranges 8

    The New Range dialog appears where you will need to confirm the password you have previously set for the range.  You are next returned to the Allow Users to Edit Ranges dialog box.  You can now repeat these steps to set up permissions for additional ranges if required.

    To protect the worksheet click the Protect Sheet button and enter and confirm a password.

    Your worksheet is now protected with your user permissions.

    You can create a list of permissions by ticking Paste permission information into a new workbook in the Allow Users to Edit Ranges dialog box. The list of permissions is created in a new workbook that you can save and refer to at a later date.

    allow users to edit ranges 9