Data Validation in Downloaded Excel Sheet

1. Immediate fixes
Step 1: Unlock the file
Right-click the downloaded Excel file → Properties → check “Unlock”
Re-open the file and check if the data verification is restored
 Step 2: Convert the file format
Click File → Save As
Select .xlsx format (not CSV/TXT) to save
Close and reopen the new file
2. Quickly rebuild data validation
Method A: Copy validation rules
Open the original file containing the validation rules
Select validation cells → press Ctrl+C to copy
Right-click in the downloaded file → Selective Paste → Validation
Method B: Manual reset
Select the target cell
Data → Data Validation → Re-enter Allowed Conditions and Sources
3. Prevent recurrence
Download Notes:
Always edit directly via OneDrive/SharePoint to avoid downloading
To download, select “Save As” instead of editing the email attachment directly.
Batch Repair Tool:
Use Power Query:
Data → Get Data → Import from File
Right click on the columns after loading → Replace values Repair abnormal data
4. Special scenarios
CSV file missing validation:
New Excel file → Data → Import from Text/CSV.
Set the data type of each column in the import wizard.
Manually add validation rules after import
5. Validation checklist
If still invalid:
Check if cells are protected/locked
Verify that compatibility mode is not enabled (File → Information → Convert)
Test if it opens correctly on another computer

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.