Excel Worksheet protection

Question

Hi all,

basically I have a worksheet that I want to lock so that all users can add data only but not format in any way, but that myself and a colleague can change formatting without having to unprotect and re-protect the sheet constantly.

so all users can add information but only set people can for example change the font.

Answer 1

Hi @Ben Foubister,

Thank you for posting your question in the Microsoft Q&A forum.According to your concern, you can achieve that by using the Allow edit ranges built-in feature in Excel. I suggest you can try following this guideline:

1- In Excel desktop version

• For example, I want to allow my users to add data into range A1: H15 only, I will go to Review > Allow edit ranges > New > Add the range A1:H15 into the pane > set no password and permission then press OK

• For my specified colleague, I will add a different range with permissions for him to edit ranges without password. I repeat the same step Review > Allow edit ranges > New > Add the range 1:1048576 for the whole worksheet then click Permissions > Add > Add my colleague account > check the box for Allow edit ranges without password > OK

• In the end, double check if there are two ranges in the Allow Edit ranges dialog box and press OK to close it
• Now I will choose Protect sheet > check only these options "Protect worksheet and contents...", "Select locked cells" and "Select unlocked cells" > input the password and press OK.

• As a result, only the colleague who was added in the permissions can freely edit the worksheet. Other users can only add the details into range A1: H15 only, cannot perform any other editing or modifying action.

2- For Excel web version

Excel web version can perform the same feature with the same configuration settings by going to Review > Manage Protection and configure the details in the Manage Protection tab on the right side.

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution.

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out in the comments of this post. I'll be happy to support.

I look forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To achieve the desired protection settings for your Excel worksheet, you can use the Protect Sheet feature in conjunction with the Allow Users to Edit Ranges option. Here’s how you can set it up:

1. Protect the Worksheet: Go to the Review tab and click on Protect Sheet. In the dialog box, you can set a password and specify what actions users are allowed to perform. Make sure to uncheck options that allow formatting (like formatting cells).
2. Allow Specific Users to Edit Ranges: After protecting the sheet, you can allow specific users (like yourself and your colleague) to edit certain ranges. In the Review tab, click on Allow Users to Edit Ranges. Here, you can define which ranges are editable and set a password for those ranges if needed.

This way, all users can add data to the worksheet without changing the formatting, while you and your colleague can still modify the formatting as needed without having to unprotect and re-protect the sheet constantly.

---

References:

• Programmatically protect worksheets
• WorksheetBase.Protect Method