How to apply different Passwords or Permissions to separate Ranges in Worksheets in Excel

Qsn: I need to share a spreadsheet with different group of people and I want only specified group access specified ranges, like sales group members should be able to edit only sales data range and human resources group members to edit personal data range. Please help me how to do it.

Ans: Apply different Passwords or Permissions to separate Ranges in a Worksheet

To apply Individual Range-Level Protection in a worksheet, follow these steps:

1. Review Menu > Changes group > Click on Allow Users to Edit Ranges

2. In the Allow Users to Edit Ranges dialog box, click New.

3. In the New Range dialog box,
   • Title : Specify Range Title Eg; Range1
   • Refers to Cells: Select required range e.g; =E3:E6
   • Range Password: Specify password

4. Click on Permissions, and then click Add.

5. In the “Select Users, Computers, or Groups” dialog box, type Everyone. Click OK

6. Check Deny > Click on Apply then on OK in the Permissions for Range1 dialog box.

7. Repeat steps 3 – 6, by selecting the a another range. You can create as many as password protected ranges as you need.

8. In the Allow Users to Edit Ranges dialog box, click Protect Sheet, type the Password

9. Click OK twice. When prompted, retype the password.

10. When you try to edit the protected ranges, you will prompted to enter password

When you apply different passwords to separate ranges in this way, a range that has been unlocked remains unlocked until the workbook is closed.

You can use existing range names to identify cells that are to be protected with passwords, but if you do, Excel converts any relative references in the existing name definitions to absolute references. Because this may not give you the results you intended, it is preferable to select the cells/range, as described earlier in this article.

Share this

SupeR_GameR