Did you know you can set different passwords to protect specific ranges of your Excel worksheets?
It’s called Allow Users to Edit Ranges and you’ll find it on the Review tab in the Changes group:
Let’s say you have several different users of a workbook and you want to restrict each user’s editing rights to a specific range (knowing this would have been handy when I used to collate budget and forecast data from department managers), you can give them their own password which only unprotects the section they’re allowed to work on.
Or, if you work on a network drive you can simply grant users permission to a specific range so they don’t have to enter the password.
Applying Different Passwords
It’s easy to set up. I’ll demonstrate using these 4 regions where each region will have a different user enter the data:
- Go to the Review tab in the Changes group > Click on Allow Users to Edit Ranges button.
- Click on ‘New’ to set up your first range for North, which will be cells C3:C14 and enter your password:
- Rinse & Repeat for regions South, East and West using different passwords for each (assuming a different user will input each region).
- Now you should have all 4 ranges listed in the Allow Users to Edit Ranges dialog box and you’re ready to protect the sheet. Click on the Protect Sheet button and enter a unique password (this is your master password so it should be different from the passwords for the users):
- Now when anyone attempts to enter data into the protected cells they’ll be prompted for their password:
Tip: You can set non-contiguous ranges, just select the first range > hold down CTRL and select the next range and so on. This will insert a comma between each range.
Things to note:
- Upon the user entering their password, that range becomes unlocked while the other ranges remain locked.
- You can unlock multiple ranges and doing so will not lock any previously unlocked ranges.
- Once a range has been unlocked by a user, it will remain unlocked until the workbook is closed.
- Saving a workbook doesn't lock any ranges.
- No protection is actually applied until you password protect the workbook. Don’t forget this step 🙂
Changing Passwords
If you need to change a password simply:
- Go to the Review tab > Click Unprotect Sheet > enter your password
- On the Review tab > Allow Users to Edit Ranges
- Click on the range you want to edit > Modify > Password > enter your new password:
- Don’t forget to protect the sheet again.
Protection for Groups and Users
If your workbook is on a shared network drive then you can assign individual users or groups of users. Any permitted users can then edit the protected ranges without having to enter the password.
Note: Other users can still edit the ranges by typing in the password.
To set up access for users and groups of users:
- After setting up your ranges open the Allow Users to Edit Ranges dialog box.
- Click on the range you want to set permissions for and click the Permissions button:
- In the next dialog box click Add. This will open the dialog box where you can select the users or groups (note: I don’t have a network drive so my dialog box in the image below doesn’t show a list of users or groups, but yours should if your file is saved to a network drive):
- Now you’re all set. Don’t forget to password protect the workbook again 🙂
Caveat
Please be aware that Excel doesn’t encrypt data in a password protected workbook and anyone can figure out a way to crack the password in an Excel file by doing enough searches on Google.
Excel password protection is designed to prevent users from accidentally editing data they shouldn't, and to avoid confusion by allowing you to hide formulas and other information from view. It’s by no means Fort Knox.
Greeting
thank you for the great information .i want to clarify if it is possible to lock each column by different user with different password (daily check list with different user )
regards
Yes, Sana. You can protect different columns for different users.
Is there a way I can do this to FormControls or ActiveX controls? For examlpe, I have 4 check boxes (form control or activeX) and I need to individually password protect them so that Person 1 can only check/uncheck checkbox1 and Person 2 can only check/uncheck checkbox2 and so on.
Hi Mufz,
In the Protect Sheet dialog you check the ‘Edit Ojects’ box to allow interaction with form controls and other objects.
Mynda
Hello and thank you!
Is it possible to do this within a VBA-Code?
Thanks
john
Hi John,
Sure, if you know how to write the VBA code, almost anything is possible.
Mynda
Thank you! Is there any way to relock the ranges after a user has inputted their password and no longer needs to edit their ranges? Other then closing the document, as you noted in your “Things to note”?
Things to note:
•Upon the user entering their password, that range becomes unlocked while the other ranges remain locked.
•You can unlock multiple ranges and doing so will not lock any previously unlocked ranges.
•Once a range has been unlocked by a user, it will remain unlocked until the workbook is closed.
•Saving a workbook doesn’t lock any ranges.
Hi Jamie,
That cannot be done without a macro. I suggest uploading a sample file to our forum, if you need help on writing the code for this.
Regards,
Catalin
Hello and thanks for the info. Is there a way to create permissions with passwords within the “allow users to edit ranges” feature using vba. Thank you!
Hi David,
I used the macro recorder while doing the operation manually, and here is what the recorder produced:
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range( _
"A1:C20"), Password:="1111"
Is that what you wanted?
Catalin
This blows me away. It’s great, but… I spaghetti coded my own administrator login layer for a workbook I made for my work, because I wanted people to have access to the data but not be able to change (screw up) all of my hard work. It even locks people out if they try X number of failed attempts, etc.
And now, you go and show me THIS, and I realize that all of what I did was for naught….
Aaaaaaaaaahg!
But, thank you!
🙂 glad you’ll find this useful, Gordon.
Thank you very much ma’am for this another brilliant knowledge you had shared. Maam please teach me how to make a default header and footer so that everytime i open a new excel workbook and print my output there is automatically a header and footer in it without typing again and again header and footer. Thank you very much and GOD BLESS.
Hi Elinor,
Glad you liked it.
To have a default header and footer already set up you need to modify your default workbook. To do this:
1. Open a blank Excel workbook.
2. Format the blank workbook with the header and footer that you want.
3. Choose Save As.
4. From the “Files of type” drop-down list, select “Excel Template (*.xltx)” and change the file name to “Book”
5. Set the “Save in” location to the XLSTART folder*.
6. Click Save.
7. Close Excel and re-open. The blank workbook should contain the header and footer you previously set.
*To find your XLSTART folder :
Windows XP : C:\Documents and Settings\User_name\Application Data\Microsoft\Excel\XLSTART
Windows Vista : C:\Users\User_Name\AppData\Local\Microsoft\Excel\XLSTART
Windows 7 /8 : C:\Users\User_Name\AppData\Roaming\Microsoft\Excel\XLSTART
Replace User_Name with your username on your computer.
Mynda
And of course, if you want any new *sheets* you insert to have the same formatting, you should save the file as “Sheet.xltx” in the XLSTART folder. 🙂
Indeed. Good tip, Khushnood.
Thank you very much ma’am.
thanks a lot
iran,isfahan
You’re welcome, Shapour 🙂
You are excellent
Thanks, Ivan. Glad you found it helpful.
Wonderful Article.Thanks for sharing to us.
You’re welcome, Kalaiselvan 🙂
Great article Mynda… Thanks
Thanks, RGS 🙂
really it is most helpful tips, thanks
And which 1 next???
🙂 thanks, Naeem.
Next is a surprise!
Mynda
Just another great tips!!!
Thanks for sharing.
Thanks, MF 🙂
very nice examples..
thank u
Thanks, Jerald.
Glad you liked them 🙂
Mynda
A useful tip and very handy. I would definitely recommend this method. Thanks for sharing.
Cheers, Jef 🙂