How to allow certain users to edit cell range in Excel?
When working with sensitive or shared data in Excel, it's common to protect a worksheet or workbook to prevent unauthorized changes. By default, once a worksheet is protected, only users who have the password can make any modifications. However, there are situations—such as collaborative projects or departmental reporting—where you may want to let specific users edit designated areas of a protected sheet, while still ensuring that the rest of the content remains secure. This tutorial introduces a practical method for allowing certain users to modify cell ranges in Excel, balancing security and flexibility within your team or organization.
Allow certain users to edit a cells range
Allow certain users to edit a cell range
Before configuring permissions, confirm that your workbook or worksheet is currently not protected. If protection is already enabled, you will need to unprotect it before applying user-specific editing permissions. This approach is especially useful in scenarios such as allowing the finance team to modify financial entries, while others can only view data.
1. Open the worksheet where you want to permit specific users to edit certain cell ranges. Go to the Review tab, and select Allow Users to Edit Ranges. This menu is typically found within the "Changes" group on the ribbon. Refer to the screenshot below:
2. The Allow Users to Edit Ranges dialog box will appear. Click the New button to define a new editable range. This feature is designed to set up and manage multiple permission-based ranges as needed for your workflow.
3. In the New Range dialog:
- Give the cell range a descriptive name in the Title field for easier management (for example, “HR Input” or “Q2 Updates”).
- Specify the actual cell range by clicking the Collapse dialog button, selecting the desired cells directly in your worksheet, and expanding the dialog again.
- Enter a Range password to control who can access this range. If you leave the password blank, anyone can edit this range once the sheet is protected, so setting a password is highly recommended for security.
Note: Double-check for typos in passwords—remember that passwords are case-sensitive and should be stored securely. If you forget the password, it cannot be recovered.
4. Click Permissions. In the Permissions for Range1 dialog (where "Range1" is the name you typed in previously), select Add to open the Select Users or Groups dialog. Here, input the names of the users (such as domain usernames or email addresses) whom you wish to grant editing rights for this range.
Tip: If the entered username or group doesn't appear, use the Advanced button in the dialog, then click Find Now in the next window. You can select the appropriate user or user group from the Search Results. This step ensures that permissions are assigned accurately, especially in environments with large user lists or networked Excel installations.
5. After selecting the user(s), click OK to return to the Permissions for Range1 window. Here, you can define whether each user needs the password you set earlier to edit the range, or if editing is permitted without a password based on user credentials. This is particularly useful if you want to streamline access for frequent collaborators. Make sure the correct users are listed to avoid accidental unauthorized access.
6. Click OK in all dialogs to confirm your settings. You will be prompted to re-enter the range password for verification to ensure it matches your original entry. Reconfirming the password helps prevent accidental misconfiguration.
7. Once returned to the Allow Users to Edit Ranges dialog, you can now either click Protect Sheet to activate worksheet protection, or simply close the dialog if you need to configure more ranges. When you protect the worksheet, only the specified users will be able to edit their assigned ranges, and all other users will be blocked unless they have the general worksheet password.
Note: When sheet protection is enabled, regular editing for most cells is blocked except for those ranges where users are specifically allowed. Always verify user access after configuring settings, especially if working with large teams or on shared networks.
- Applicable scenarios: Project management, department-specific data entry, shared summary reports.
- Advantages: Maintains overall worksheet security, enables flexible team collaboration.
- Drawbacks: Windows-based user authentication is required for named users—this may not be available for files used outside organization networks or on personal computers.
If you encounter issues—such as users not appearing in the selector, or editing restrictions not working—double-check your Windows user accounts and network sharing settings. Restarting Excel after permission changes may also help apply the settings.
Alternative: Use Excel formulas & hidden sheets to control editing (manual protection)
In cases where you do not have access to Windows authentication or want a simpler control method, you can apply manual protection by hiding formulas and sheets, and only sharing editing instructions for relevant cells. This method is practical in smaller teams or for standalone Excel files.
1. Select the cell range (such as B2:B10) you want others to edit. Right-click and choose Format Cells > Protection, then uncheck Locked. Click OK.
2. Protect the sheet by navigating to Review > Protect Sheet. Set a password and confirm.
Now, only the cells you unlocked in step 1 will be editable. All other cells will remain protected. You can adjust the unlocked range as needed for different users, but this method does not support user-specific control—anyone with access to the worksheet password can edit the unlocked cells.
- Applicable scenarios: Quick worksheet sharing, non-network Excel files, and individual small projects.
- Pros: Simple to set up, compatible with all Excel versions.
- Cons: Does not distinguish between users—anyone with the password can edit the unlocked range.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in