Methods to use passwords to grant customers entry to completely different Microsoft Excel workbook ranges

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

Microsoft Excel logo editorial illustrative
Picture: Renan/Adobe Inventory

You’ll be able to password defend a Microsoft Excel file, and you too can apply safety to particular areas. The results of the latter means the person 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 notice is that you would be able to additionally restrict their entry to completely different ranges in the identical Excel workbook.

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

On this tutorial, I’ll present you find out how to use the Permit Edit Ranges characteristic to specify who can entry particular knowledge. On this manner, you possibly can permit a number of customers to entry knowledge whereas limiting the information every can edit.

I’m utilizing Microsoft 365, however you need to use earlier variations of Excel. Excel for the online helps this characteristic. You’ll be able to obtain the Microsoft Excel demo file for this tutorial.

Methods to apply Permit Edit Ranges in Excel

Now let’s suppose that you’ve an Excel workbook that 5 customers replace however you wish to restrict every person to the vary they edit. Due to the Permit Edit Ranges characteristic, you possibly can simply accomplish this.

See also  Are moodables the subsequent smartest thing in IoT?

This characteristic lets you specify a spread quite than all the sheet or workbook, as password protected. Then, you give the password to the person(s) to allow them to edit solely their vary. Utilizing this straightforward course of, you possibly can permit a number of customers entry to the workbook whereas limiting what they will edit.

Determine A reveals a easy Excel sheet. 5 staff must enter their regional gross sales values. You need every worker to have the ability to edit their column with out accessing the opposite staff’ columns. Now we have 5 staff, so we are going to defend 5 ranges with 5 completely different passwords. If you’d like a couple of 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 Overview tab.
  3. Within the Shield group, click on Permit Edit Ranges.
  4. Within the ensuing dialog, click on New. Enter the title “Emily”  and when assigning passwords, understand that they’re case-sensitive.
  5. Excel has already stuffed the Vary setting with $C$3:$C$6 (Determine B).
  6. Within the Password discipline, enter “Emily” as nicely.
  7. Click on OK.
  8. When prompted, enter the password once more.
  9. Click on Shield Sheet.
  10. Within the ensuing dialog, enter a password for the sheet, reminiscent of “pw” (Determine C). At this level, you possibly can permit permission for particular duties that Excel doesn’t permit in a protected sheet, reminiscent of sorting. For now, don’t test something. Merely click on OK.
  11. Enter “Emily” when prompted to enter the vary password.
  12. Click on OK.
See also  SaaS startup goals to eradicate digital friction in distant transactions and scale back software overload

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 person 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 staff — maybe John, Susan, Kevin and Mary — utilizing their names because the passwords. The sheet password will all the time be “pw.”

If you’re accomplished, 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 modifications to the sheet.

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

See also  An exec’s information to attaining mission-critical availability

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

Methods to use vary passwords to enter knowledge 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 need to 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 need to know the sheet password to edit different cells.

Determine E

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

This characteristic is versatile sufficient to permit a number of ranges, customers, and passwords. It’s additionally sensible sufficient to guard all the sheet.