Why can't I lock a sheet so users can't paste or drag/drop in to a cell. It breaks formulas and validations that should be locked

Currently, Excel does not provide a built-in feature that specifically prevents users from pasting or dragging and dropping data into cells while keeping the formulas and data validations intact. When you protect a worksheet, you can lock cells to prevent editing, but this does not stop users from pasting or dragging data into those cells if they are not properly set to be locked.

To mitigate this issue, you can consider the following approaches:

1. Data Validation: Use data validation to restrict the type of data that can be entered into the cells. This won't prevent pasting but can help ensure that only valid data is entered.
2. Worksheet Protection: Make sure to protect the worksheet and lock the cells that contain formulas and validations. However, this will not prevent users from pasting data into unlocked cells.
3. Instructions for Users: Provide clear instructions to your clients on how to fill out the forms correctly and emphasize the importance of not using drag-and-drop or paste functionalities.
4. Post-Processing: Implement a validation step in your data extraction process to check for any invalid entries or overwrites that may have occurred due to user actions.

Unfortunately, without a specific feature to restrict pasting or dragging, these workarounds may be necessary to maintain the integrity of your forms and data extraction processes.

References:

• How to control and understand settings in the Format Cells dialog box in Excel

Hi @Hammond, Lawrence (MOH),

Thank you for posting your question in the Microsoft Q&A forum.

I'm sincerely sorry for any inconvenience and troubles this situation may have caused you.

Based on your information, you can restrict the behavior of the clients breaking your forms by using Excel sheet protection feature. You can configure the sheet like this:

For Excel desktop app:

• In Excel desktop app, you need to go to Review tab > Protection
• For example, I want my clients can only provide details in range A1:A15, the other cells are my formulas, and I need to lock them so the structure won't be affected. Choose Allow Edit Ranges > New > Add the range A1:A15 > OK

• Now I will choose Protect Sheet > check these options "Protect worksheet and contents of locked cells", "Select locked cells", "Select unlocked cells" (clients only select, cannot change the contents of the cells outside range A1:A15) > input the password and press OK.

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.

We already use the protection feature and it does not stop users from pasting in to cells or dragging in to cells. This over-writes formatting so we get text in cells that are supposed to be numbers, which the database rejects. I know there is a "formatting" protection, but it does not work for users with older versions of Excel, which is pretty common.

Also, when a user drags a cell in to another cell, then any formula pointing to the receiving cell loses the reference in the formula and is replaced with #REF!

We can't stop our users from doing this. I need a protection option to keep them from doing it.