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

When multiple users work in the same Microsoft Excel file, you can limit their access to the areas they need to work in.

You can protect a Microsoft Excel file with a password, and you can also apply protection to specific areas. The result of the latter means the user can only change unprotected cells: one keeps people out of the file entirely, and the other allows users to get their work done without accidentally changing formulas. What you may not know is that you can also restrict their access to different areas in the same Excel workbook.

SEE: Google Workspace vs. Microsoft 365: A Parallel Analysis with Checklist (TechRepublic Premium)

In this tutorial, I’ll show you how to use the Allow Ranges To Be Edited feature to control who can access specific data. This allows you to allow multiple users to access data while limiting the data that anyone 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 Editing Ranges” in Excel

Now let’s say you have an Excel workbook that five users are updating, but you want to limit each user to the range they are editing. You can easily achieve this thanks to the Allow Edit Ranges feature.

This feature allows you to specify a range as password protected instead of the entire sheet or workbook. Then give the users the password so they can only edit their space. With this simple procedure, you can grant multiple users access to the workbook while restricting what they can edit.

Figure A shows a simple Excel spreadsheet. Five employees must enter their regional sales values. You want each collaborator to be able to edit their column without having access to other collaborators’ columns. We have five employees, so we will protect five areas with five different passwords. If you want more than one person to have access to the same area, just send them the appropriate password.

Figure A

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

1. Select C3:C6, Emily’s column.
2. Click the Review tab.
3. In the Protect group, click Allow Worksets.
4. In the dialog box that appears, click New. Enter “Emily” as the title and pay attention to upper and lower case letters when entering the password.
5. Excel already has the range setting with $C$3:$C$6 (Figure B).
6. Also enter “Emily” in the password field.
7. click OK.
8. Re-enter the password when prompted.
9. Click Protect Sheet.
10. In the dialog box that appears, enter a password for the sheet, e.g. “pw” (Figure C). At this point, you can grant permission for certain tasks that Excel does not allow in a protected sheet, such as: B. sorting. Don’t check anything for now. Just click OK.
11. Enter “Emily” when prompted for the realm password.
12. click OK.

Figure B

Figure C

At this point, only the user who knows the space password for Emily’s column can edit those cells. There are four other areas that need protection.

Using the instructions above, create a space password for the other employees—perhaps John, Susan, Kevin, and Mary—and use their names as passwords. The sheet password is always “pw”.

When you’re done, you’ll have six passwords: Emily, John, Susan, Kevin, Mary, and pw. Only you should know the sheet password. You need this to make changes to the sheet.

These passwords are simple to keep the example simple. If you’re applying this to your own work, don’t use names as passwords. Other users can easily crack this kind of obvious password. However, don’t use realm passwords as a serious security measure. Using these passwords prevents accidents. For example, without the realm passwords, Kevin could accidentally enter his data in Mary’s column without realizing it. Area passwords are used to prevent accidents.

The same goes for the sheet password. Use one that’s easy to remember but not overly obvious.

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

Since all five staff areas are password protected, let’s try typing in Emily’s column and see what happens:

1. Select C3 and type everything. As soon as you type the first character, Excel will display the sheet password prompt shown in Figure D.
2. Type Emily and click OK.
3. Try again. This time Excel allows you to edit C3:C6.

Figure D

Select a cell outside the data range and try entering a value. This time, Excel displays the message shown in Figure E. We didn’t apply a range password to cells or ranges outside the data range, but Excel still protects those cells. You need to know sheet password to edit other cells.

Figure E

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