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

If a number of customers work in the identical Microsoft Excel file, you’ll be able to restrict their entry to solely the ranges the place they should work.

Picture: Renan/Adobe Inventory

You may password shield a Microsoft Excel file, and you may as well apply safety to particular areas. The results of the latter means the consumer can change solely unprotected cells: One retains individuals out of the file altogether, and the opposite permits customers to do their jobs with out inadvertently altering formulation. What you may not understand is you could additionally restrict their entry to totally different ranges in the identical Excel workbook.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

Should-read Home windows protection

On this tutorial, I’ll present you tips on how to use the Permit Edit Ranges function to specify who can entry particular information. On this means, you’ll be able to enable a number of customers to entry information whereas limiting the information every can edit.

I’m utilizing Microsoft 365, however you should utilize earlier variations of Excel. Excel for the net helps this function.

Learn how to apply Permit Edit Ranges in Excel

Now let’s suppose that you’ve got an Excel workbook that 5 customers replace however you need to restrict every consumer to the vary they edit. Due to the Permit Edit Ranges function, you’ll be able to simply accomplish this.

This function permits you to specify a spread fairly than the whole sheet or workbook, as password protected. Then, you give the password to the consumer(s) to allow them to edit solely their vary. Utilizing this easy course of, you’ll be able to enable a number of customers entry to the workbook whereas limiting what they’ll edit.

Determine A reveals a easy Excel sheet. 5 workers must enter their regional gross sales values. You need every worker to have the ability to edit their column with out getting access to the opposite workers’ columns. We’ve got 5 workers, so we are going to shield 5 ranges with 5 totally different passwords. If you’d like multiple particular person to have entry to the identical vary, merely ship them the suitable password.

Determine A

Restrict entry to every vary by password defending a spread.

Now, let’s arrange Emily’s vary as follows:

1. Choose C3:C6, which is Emily’s column.
2. Click on the Assessment tab.
3. Within the Defend group, click on Permit Edit Ranges.
4. Within the ensuing dialog, click on New. Enter the title “Emily”  and when assigning passwords, remember the fact that they’re case-sensitive.
5. Excel has already stuffed the Vary setting with $C$3:$C$6 (Determine B).
6. Within the Password subject, enter “Emily” as nicely.
7. Click on OK.
8. When prompted, enter the password once more.
9. Click on Defend Sheet.
10. Within the ensuing dialog, enter a password for the sheet, reminiscent of “pw” (Determine C). At this level, you’ll be able to enable permission for particular duties that Excel doesn’t enable in a protected sheet, reminiscent of sorting. For now, don’t examine something. Merely click on OK.
11. Enter “Emily” when prompted to enter the vary password.
12. Click on OK.

Determine B

Excel has already stuffed the Vary.

Determine C

Enter a password for the sheet. There are two passwords now: One for Emily and one for the sheet.

At this level, solely the consumer who is aware of the vary password for Emily’s column can edit these cells. There are 4 extra ranges to guard.

Utilizing the directions above, create a spread password for the opposite workers — maybe John, Susan, Kevin and Mary — utilizing their names because the passwords. The sheet password will at all times be “pw.”

Whenever you’re carried out, you should have six passwords: Emily, John, Susan, Kevin, Mary and pw. Solely you need to know the sheet password. You’ll want this to make adjustments to the sheet.

These passwords are easy to maintain the instance easy. When making use of this to your personal work, don’t use names as passwords. Different customers can simply crack that type of apparent password. Nonetheless, don’t use vary passwords as a critical safety measure. Making use of these passwords retains accidents from occurring. As an example, with out the vary passwords, Kevin may by chance enter his information in Mary’s column with out realizing it. Vary passwords are for stopping accidents.

The identical is true on the subject of the sheet password. Use one which’s straightforward to recollect however not exceedingly apparent.

Learn how to use vary passwords to enter information within the protected cells in Excel

With all 5 worker ranges password protected, let’s attempt to enter one thing in Emily’s column and see what occurs:

1. Choose C3 and enter something. As quickly as you kind the primary character, Excel shows the sheet password immediate proven in Determine D.
2. Enter “Emily” and click on OK.
3. Attempt once more. This time, Excel will help you edit C3:C6.

Determine D

You should know Emily’s password to edit her column.

Choose a cell exterior the information vary and attempt to enter a price. This time, Excel shows the message proven in Determine E. We’ve not utilized a spread password to any cells or ranges exterior the information vary, however Excel nonetheless protects these cells. You should know the sheet password to edit different cells.

Determine E

You should know the sheet password to edit something exterior the password ranges.

This function is versatile sufficient to permit a number of ranges, customers, and passwords. It’s additionally good sufficient to guard the whole sheet.

The post How to use passwords to grant users access to different Microsoft Excel workbook ranges appeared first on NO INDEX.