Then the users can sort and filter in this protected sheet. If there are multiple sheets needed to protect and allow users to sort and filter, you can apply Protect Worksheet utility of Kutools for Excel to protect multiple sheets at one time.please go to free try Kutools for Excel first, and then go to apply the operation according below steps. Aug 20, 2020 Start Excel, open your workbook, and then select the range that you want to allow access to. In Microsoft Office Excel 2007, click the Review tab, and then click Allow Users to Edit Ranges in the Changes group. In Microsoft Office Excel 2003 or in Microsoft Excel 2002, on the Tools menu, click Protection, and then click Allow Users to Edit Ranges.
- Named Ranges In Excel
- Allow Users To Edit Ranges In Excel For Mac Pdf
- Allow Users To Edit Ranges In Excel For Mac 2017
- Excel Macro Allow Users To Edit Ranges
- Allow Users To Edit Ranges In Excel For Mac Windows 10
You can use the Allow Users to Edit Ranges command button in the Changes group on the Review tab of the Excel 2013 Ribbon to enable the editing of particular ranges in the protected worksheet by certain users. When you use this feature, you give certain users permission to edit particular cell ranges, provided that they can correctly provide the password you assign to that range.
To give access to particular ranges in a protected worksheet, you follow these steps:
1Click the Allow Users to Edit Ranges command button on the Ribbon’s Review tab or press Alt+RU.
Note that the Allow Users to Edit Ranges command button is grayed out and unavailable if the worksheet is currently protected. In that case, you must remove protection by clicking the Unprotect Sheet command button on the Review tab before you retry Step 1.
Excel opens the Allow Users to Edit Ranges dialog box, where you can add the ranges you want to assign.
2Click the New button.
Doing this opens the New Range dialog box where you give the range a title, define its cell selection, and provide the range password.
3If you wish, type a name for the range in the Title text box; otherwise, Excel assigns a name such as Range1, Range2, and so on.
Next, you designate the cell range or nonadjacent cell selection to which access is restricted.
4Click the Refers to Cells text box and then type in the address of the cell range (without removing the = sign) or select the range or ranges in the worksheet.
Next, you need to enter a password that’s required to get access to the range. Like all other passwords in Excel, this one can be up to 255 characters long, mixing letters, numbers, and spaces. Pay attention to the use of upper- and lowercase letters because the range password is case-sensitive.
5Type in the password for accessing the range in the Range Password dialog box.
You need to use the Permissions button in the New Range dialog box to open the Permissions dialog box for the range you’re setting.
6Click the Permissions button in the Range Password dialog box.
Next, you need to add the users who are to have access to this range.
7Click the Add button in the Permissions dialog box.
Doing this opens the Select Users or Groups dialog box, where you designate the names of the users to have access to the range.
8Click the name of the user in the Enter the Object Names to Select list box at the bottom of the Select Users or Groups dialog box. To select multiple users from this list, hold down the Ctrl key as you click each username.
If this list box is empty, click the Advanced button to expand the Select Users or Groups dialog box and then click the Find Now button to locate all users for your location.
You can then click the name or Ctrl+click the names you want to add from this list, and then when you click OK, Excel returns you to the original form of the Select Users or Groups dialog box and adds these names to its Enter the Object Names to Select list box.
9Click OK in the Select Users or Groups dialog box.
Doing this returns you to the Permissions dialog box where the names you’ve selected are now listed in the Group or User Names list box. Now you need to set the permissions for each user. When you first add users, each one is permitted to edit the range without a password.
To restrict the editing to only those who have the range password, you need to click each name and then select the Deny check box.
10Click the name of the first user who must know the password and then select the Deny check box in the Permissions For list box.
You need to repeat this step for each person in the Group or User Names list box that you want to restrict in this manner.
11Repeat Step 10 for each user who must know the password and then click OK in the Permissions dialog box.
As soon as you click OK, Excel displays a warning alert dialog box, letting you know that you are setting a deny permission that takes precedence over any allowed entries, so that if the person is a member of two groups, one with an Allow entry and the other with a Deny entry, the deny entry permission rules (meaning that the person has to know the range password).
12Click the Yes button in the Security alert dialog box.
Doing this closes this dialog box and returns you to the New Range dialog box.
13Click OK in the New Range dialog box.
Doing this opens the Confirm Password dialog box where you must accurately reproduce the range password.
14Type the range password in the Reenter Password to Proceed text box and then click the OK button.
Doing this returns you to the Allow Users to Edit Ranges dialog box where the title and cell reference of the new range are displayed in the Ranges Unlocked by a Password When Sheet Is Protected list box.
If you need to define other ranges available to other users in the worksheet, you can do so by repeating Steps 2 through 14.
When you finish adding ranges to the Allow Users to Edit Ranges dialog box, you’re ready to protect the worksheet. If you want to retain a record of the ranges you’ve defined, go to Step 15. Otherwise, skip to Step 16.
15(Optional) Select the Paste Permissions Information Into a New Workbook check box if you want to create a new workbook that contains all the permissions information.
When you select this check box, Excel creates a new workbook whose first worksheet lists all the ranges you’ve assigned, along with the users who may gain access by providing the range password. You can then save this workbook for your records. Note that the range password is not listed on this worksheet — if you want to add it, be sure that you password-protect the workbook so that only you can open it.
Now, you’re ready to protect the worksheet. If you want to do this from within the Allow Users to Edit Ranges dialog box, you click the Protect Sheet button to open the Protect Sheet dialog box.
If you want to protect the worksheet later on, you click OK to close the Allow Users to Edit Ranges dialog box and then click the Protect Sheet command button on the Review tab of the Ribbon (or press Alt+RPS) when you’re ready to activate the worksheet protection.
16Click the Protect Sheet button to protect the worksheet; otherwise, click the OK button to close the Allow Users to Edit Ranges dialog box.
If you click the Protect Sheet button, Excel opens the Protect Sheet dialog box, where you can set a password to unprotect the sheet. This dialog box is also where you select the actions that you permit all users to perform in the protected worksheet.
After you turn on protection in the worksheet, only the users you’ve designated are able to edit the cell range or ranges you’ve defined. Of course, you need to supply the range password to all the users allowed to do editing in the range or ranges at the time you distribute the workbook to them.
-->Note
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
Summary
In Microsoft Excel 2002 and in later versions of Excel, you can now use passwords to protect specific ranges in your worksheets. This is a change from earlier versions of Excel, in which one password applies to the entire worksheet, which might have several protected ranges. In addition, if you use Windows 2000, you can apply group-level passwords and user-level passwords to different ranges.
The features in Microsoft Excel that are related to hiding data and protecting worksheets and workbooks with passwords are not intended to be mechanisms for securing data or protecting confidential information in Excel. You can use these features to present information more clearly by hiding data or formulas that might confuse some users. These features also help prevent other users from making accidental changes to data.
Excel does not encrypt data that is hidden or locked in a workbook. With enough time, users can obtain and modify all the data in a workbook, as long as they have access to it. To help prevent modification of data and to help protect confidential information, limit access to any Excel files that contain such information by storing them in locations available only to authorized users.
Note
This article describes how to enable specific collaboration scenarios to function correctly in collaboration environments that do not include users who have malicious intent. You cannot enable strong encryption for a file by using password protection. To protect your document or file from a user who has malicious intent, you can restrict permission by using Information Rights Management (IRM).
More Information
How to apply different passwords
To apply different passwords to two ranges in a worksheet, follow these steps:
![For For](https://www.bluepecantraining.com/wp-content/uploads/2015/06/allow-users-to-edit-ranges-3.jpg)
Named Ranges In Excel
- Start Excel, and then open a blank workbook.
- On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.NoteIn Microsoft Office Excel 2007, click Allow Users to Edit Ranges in the Changes group on the Review tab.
- In the Allow Users to Edit Ranges dialog box, click New.
- In the New Range dialog box, click the Collapse Dialog button. Select the range B2:B6, and then click the Collapse Dialog button again.
- In the Range password box, type rangeone, click OK, then type it again in the Confirm Password dialog box, and then click OK.
- Repeat steps 3 through 5, selecting the range D2:D6 and typing rangetwoas the password for that range.
- In the Allow Users to Edit Ranges dialog box, click Protect sheet. In the Password to unprotect sheet box, type ranger, and then click OK. When prompted, retype the password, and then click OK.
- Select cell B3, and then start to type Dataone.NoteWhen you type D, the Unlock Range dialog box appears.
- Type rangeone in the Enter the password to change this cell box, and then click OK.You can now enter data in cell B3 and in any other cell in the range B2:B6, but you cannot enter data in any of the cells D2:D6 without first providing the correct password for that range.
Allow Users To Edit Ranges In Excel For Mac Pdf
The range that you protect with a password does not have to be made of adjacent cells. If you want the ranges B2:B6 and D2:D6 to share a password, you can select B2:B6 as described in step 4 earlier in this article, type a comma in the New Range dialog box, and then select the range D2:D6 before you assign the password.
When you apply different passwords to separate ranges in this way, a range that has been unlocked remains unlocked until the workbook is closed. When you unlock another range, you do not relock the first range. Likewise, when you save a workbook, you do not relock a range.
You can use existing range names to identify cells that are to be protected with passwords, but if you do, Excel converts any relative references in the existing name definitions to absolute references. Because this may not give you the results you intended, it is better to use the Collapse Dialog button to select the cells, as described earlier in this article.
How to apply group-level passwords and user-level passwords
If you use Windows 2000 (but not other versions of Windows), you can assign different permissions to various individual users or groups of users. When you do this, permitted users can edit the protected ranges without needing to type passwords, and other users can still edit the ranges as long as they can supply the correct password.
To apply group-level protection to a worksheet, follow these steps:
Allow Users To Edit Ranges In Excel For Mac 2017
- Start Excel, and then open a blank worksheet.
- On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.NoteIf you are running Excel 2007, click Allow Users to Edit Ranges in the Changes group on the Review menu.
- In the Allow Users to Edit Ranges dialog box, click New.
- In the New Range dialog box, click Collapse Dialog, select the range B2:B6, and then click Collapse Dialog again.
- In the Range password box, type rangeone, and then click OK twice. When prompted, retype the password.
- Repeat steps 3 through 5, selecting the range D2:D6 and typing rangetwo as the password for that range.
- In the Allow Users to Edit Ranges dialog box, click Permissions, and then click Add in the Permissions for Range2 dialog box.
- In the Select Users or Groups dialog box, type Everyone, and then click OK.
- Click OK in the Permissions for Range2 dialog box.
- In theAllow Users to Edit Ranges dialog box, click Protect sheet, type ranger in the Password to unprotect sheet box, and then click OK twice. When prompted, retype the password.
- Select cell B3, and then start to type Dataone. A password is still required. Click Cancel in the Unlock Range dialog box.
- Select cell D3, and then type Datatwo.No password is required.
Note
You must use Windows 2000 in order to assign permissions to groups or individuals as described earlier in this article, but after you have done so, those permissions are recognized when the worksheets are edited on computers that use Microsoft Windows NT. Windows NT does not enable you to assign or modify the permissions.
If you apply group permissions or user permissions, and then open the workbook in Excel 2002 on a Microsoft Windows Millennium Edition-based computer or Microsoft Windows 98-based computer, the group permissions or user permissions are ignored, but different passwords for different ranges are recognized.
Free ozrosaurus hysterical rar. Aug 08, 2007 Ozrosaurus 0% from 4th Album Hysterical.Directed by Kenzi Sonoda.
How to change passwords
To change the password for a range, follow these steps:
![Named ranges in excel Named ranges in excel](https://i.ytimg.com/vi/vEYPfbzfxa4/hqdefault.jpg)
- Start Excel, and then open the workbook.
- On the Tools menu, point to Protection, and then click Unprotect Sheet.NoteIn Excel 2007, click Unprotect Sheet in the Changes group on the Review tab.
- If prompted type the worksheet password, and then click OK.
- On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.NoteIn Excel 2007, click Allow Users to Edit Ranges in the Changes group on the Review tab.
- Click a range in the list, and then click Modify.
- Click Password.
- Type the new password in the New password box, and then retype the new password in the Confirm new password box.
- Click OK, and then click OK.
- To change the password for another range, repeat steps 3 through 6. Otherwise, click Protect Sheet.
- Type the worksheet password in the Password to unprotect sheet box.
- Click OK, retype the worksheet password to confirm it, and then click OK.
Important
Excel Macro Allow Users To Edit Ranges
Note these aspects of applying passwords and group-level permissions to specific ranges:
- Excel 2003 runs only on Microsoft Windows XP and on Microsoft Windows 2000.
- When a workbook with protected ranges is opened in Excel 2002 on a Windows XP-based computer, on a Windows 2000-based computer, or on a Microsoft Windows NT-based computer, the worksheet range and group protection are the same as they are in Excel 2003.
- When a workbook with protected ranges is opened in Excel 2002 on a Microsoft Windows Millennium Edition-based computer or on a Microsoft Windows 98-based computer, ranges with user-level and group-level permissions require the range password.
Allow Users To Edit Ranges In Excel For Mac Windows 10
More information
For more information about the Microsoft Office features that help enable collaboration, see Description of Office features that are intended to enable collaboration and that are not intended to increase security.