How to use passwords to grant users access to different Microsoft Excel workbook ranges

If multiple users work in the same Microsoft Excel file, you can limit their access to only the ranges where they need to work.

You can password protect a Microsoft Excel file, and you can also apply protection to specific areas. The result of the latter means the user can change only unprotected cells: One keeps people out of the file altogether, and the other allows users to do their jobs without inadvertently changing formulas. What you might not realize is that you can also limit their access to different ranges in the same Excel workbook.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/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 earlier versions of Excel. Excel for the web supports this feature.

How to apply Allow Edit Ranges in Excel

Now let’s suppose that you have an Excel workbook that five users update but you want to limit each user to the range they edit. Thanks to the Allow Edit Ranges feature, you can easily accomplish this.

This feature allows you to specify a range rather than the entire sheet or workbook, as password protected. Then, you give the password to the user(s) so they can edit only their range. Using this simple process, you can allow multiple users access to the workbook while limiting what they can edit.

Figure A shows a simple Excel sheet. Five employees need to enter their regional sales values. You want each employee to be able to edit their column without having access to the other employees’ columns. We have five employees, so we will protect five ranges with five different passwords. If you want more than one person to have access to the same range, simply send them the appropriate password.

Figure A

Now, let’s set up Emily’s range as follows:

1. Select C3:C6, which is Emily’s column.
2. Click the Review tab.
3. In the Protect group, click Allow Edit Ranges.
4. In the resulting dialog, click New. Enter the title “Emily”  and when assigning passwords, keep in mind that they are case-sensitive.
5. Excel has already filled the Range setting with $C$3:$C$6 (Figure B).
6. In the Password field, enter “Emily” as well.
7. Click OK.
8. When prompted, enter the password again.
9. Click Protect Sheet.
10. In the resulting dialog, enter a password for the sheet, such as “pw” (Figure C). At this point, you can allow permission for specific tasks that Excel doesn’t allow in a protected sheet, such as sorting. For now, don’t check anything. Simply click OK.
11. Enter “Emily” when prompted to enter the range password.
12. Click OK.

Figure B

Figure C

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 the passwords. The sheet password will always be “pw.”

When you’re done, you will have six passwords: Emily, John, Susan, Kevin, Mary and pw. Only you should know the sheet password. You’ll need this 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. Applying these passwords keeps accidents from occurring. For instance, without the range passwords, Kevin might accidentally enter his data in Mary’s column without realizing it. Range passwords are for preventing accidents.

The same is true with regards to the sheet password. Use one that’s easy to remember but not exceedingly obvious.

How to use range passwords to enter data in the protected cells in Excel

With all five employee ranges password protected, let’s try to enter something in Emily’s column and see what happens:

1. Select C3 and enter anything. As soon as you type the first character, Excel displays the sheet password prompt shown in Figure D.
2. Enter “Emily” and click OK.
3. Try again. This time, Excel will allow you to edit C3:C6.

Figure D

Select a cell outside the data range and try to enter a value. This time, Excel displays the message shown in Figure E. We’ve not 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.

Figure E

This feature is flexible enough to allow multiple ranges, users, and passwords. It’s also smart enough to protect the entire sheet.