Data Validation in Downloaded Excel Sheet

1. Quick fixes
Enable Content
When opening the downloaded Excel file, click “Enable Content” on the top yellow warning bar
Check if the file is from a trusted source (right-click the file → Properties → check “Unlock”)
2. Re-apply data validation
Select the cells that need to be validated
Click Data → Data Validation → Reset Rules
Save as .xlsx format (not CSV)
3. Frequently Asked Questions
 Problem 1: The drop-down menu disappears
Reason: Loss of validation rules due to saving the file as CSV.
Solution:
Re-import the CSV to the newly created Excel file.
Manually add data validation rules
 Problem 2: The rule does not take effect
Check if the cell is locked:
Right click cell → Set Cell Formatting → Protect → Undo “Locked”
Ensure worksheet is not protected (Review → Unprotect Worksheet)
4. Preventive measures
 Save the file correctly:
Always save files containing validation rules in .xlsx format
Avoid direct editing via email (download locally and modify)
Batch Repair Tool:
Re-import data using Power Query:
Data → Get Data → Import from File
Manually add validation rules after loading
5. Alternatives
Use the drop-down list control:
Developer Tools → Insert → Combo Box (Form Control)
Right-click to format the control → Specify the data source range.