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