How to get data validation to work when it has a stop symbol and how to unlock the workbook and the worksheet

Question

Mac running Tahoe experienced spreadsheet glitch in Excel 16. User did not turn on protection for worksheet or workbook - and does not and has not used a password.  In existing spreadsheet it is not possible to disengage either worksheet and workbook protection by clicking on either lock.  Requesting:

1.  investigation if worksheet and workbook self-protected somehow on its own. 

2.   How to unlock both and keep document intact?  
3.   Has this been reported by any other Mac users previously?  
4.   Was there a solution(s) - and if yes, please share?   
5.   What are possible causes to Excel self-engaging worksheet and/or workbook protection - and how to prevent it going forward?  

How to remove the stop symbol from Data validation?

Answer 1

Although it does sound like Data Validation just clearing the Alert will not alter or remove the Validation Criteria. The criteria still will be in effect but the user will have no clarification to go on. If you don't want the data entry to be controlled you have to clear the Validation Rule, itself, on the Settings page or modify the other parameters in the Data Validation dialog. https://support.microsoft.com/en-us/excel/get-started/apply-data-validation-to-cells

Answer 2

Hi Tina,

Welcome to the Microsoft Q&A forum.

For Data Validation, if you enter data that does not comply with the configured validation rules, Excel will display a red warning icon.

Please go to Data > Data Validation > Error Alert.

Then, clear the Show error alert after invalid data is entered option.

Regarding the second issue, Excel does not automatically apply Protect Sheet with a password unless the file owner has explicitly set a password for that worksheet.

In this case, it is possible that someone with edit permissions may have accidentally set a password for the sheet.

Please help me check the following:

1. Please check whether you encounter the same issue when using Excel for the web.
2. Please contact anyone who has edit permissions for the file and confirm whether someone may have accidentally set a password for the worksheet.

I hope this information is helpful. Please let me know the results after completing the steps above. If the issue persists, I will be happy to continue assisting you.

---

If the answer is helpful, please click "Yes". 

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.