If multiple users work on the same Microsoft Excel file, you can limit their access to only the ranges they need to work on.
You can password protect a Microsoft Excel file and you can also apply protection to specific areas. The result of the latter means that the user can change only unprotected cells: one keeps people out of the file entirely, and the other allows users to do their jobs without inadvertently changing formulas. What you may not know is that you can also limit your access to different ranges in the same Excel workbook.
SEE: Google Workspace vs. Microsoft 365: A Side-by-Side Analysis with Checklist (TechRepublic Premium)
In this tutorial, I’ll show you how to use the Allow Edit Ranges feature to specify who can access specific data. In this way, you can allow multiple users to access data while limiting the data each can edit.
I’m using Microsoft 365, but you can use older versions of Excel. Excel for the web supports this feature. You can download the Microsoft Excel demo file for this tutorial.
How to Apply Allow Editing Ranges in Excel
Now suppose you have an Excel workbook that is updated by five users, but you want to limit each user to the range they edit. Thanks to the Allow Editing Ranges feature, you can easily achieve this.
This feature allows you to specify a range instead of the entire sheet or workbook, as password protected. Then you give the password to the users so they can edit only their rank. With this simple process, you can allow multiple users to access the workbook and limit what they can edit.
Figure A displays a simple excel sheet. Five employees must enter their regional sales values. You want each employee to be able to edit their column without having access to other employees’ columns. We have five employees, so we will be protecting five ranks with five different passwords. If you want more than one person to have access to the same range, simply send them the appropriate password.
Now, let’s set up Emily’s range as follows:
- Select C3:C6, which is Emily’s column.
- Click the Review tab.
- In the Protect group, click Allow Edit Ranges.
- In the resulting dialog, click New. Enter the title “Emily” and when assigning passwords, keep in mind that they are case sensitive.
- Excel has already filled the Range setting with $C$3:$C$6 (Figure B).
- In the Password field, enter “Emily” as well.
- Click OK.
- When prompted, enter the password again.
- Click Protect Sheet.
- In the resulting dialog, enter a password for the sheet, such as “pw” (Figure C). At this point, you can grant permission for specific tasks that Excel doesn’t allow on a protected sheet, such as sorting. For now, don’t check anything. Just click OK.
- Enter “Emily” when prompted to enter the range password.
- Click OK.
At this point, only the user who knows the range password for Emily’s column can edit those cells. There are four more ranges to protect.
Using the instructions above, create a range password for the other employees, perhaps John, Susan, Kevin, and Mary, using their names as passwords. The sheet password will always be “pw”.
When you’re done, you’ll have six passwords: Emily, John, Susan, Kevin, Mary, and pw. Only you should know the password for the sheet. You will need it to make changes to the sheet.
These passwords are simple to keep the example simple. When applying this to your own work, don’t use names as passwords. Other users can easily crack that kind of obvious password. However, don’t use range passwords as a serious security measure. Enforcing these passwords prevents accidents from happening. For example, without range passwords, Kevin could accidentally enter his data into Mary’s column without realizing it. Range passwords are to prevent accidents.
The same goes for the sheet password. Use one that is easy to remember but not overly obvious.
How to use range passwords to enter data into protected cells in Excel
With all five employee ranks password-protected, let’s try entering something into Emily’s column and see what happens:
- Select C3 and enter anything. As soon as you type the first character, Excel displays the password prompt for the sheet shown in Figure D.
- Enter “Emily” and click OK.
- Try again. This time, Excel will let you edit C3:C6.
Select a cell outside the data range and try to enter a value. This time, Excel displays the message shown in Figure E. We haven’t applied a range password to any cells or ranges outside the data range, but Excel still protects those cells. You must know the sheet password to edit other cells.
This feature is flexible enough to allow multiple ranges, users, and passwords. It’s also smart enough to protect the entire blade.