How to Add Data Validation to Excel After a CSV Import

To add data validation after a CSV import, select the column, go to Data > Data Validation, and set the rule: a dropdown list for categories, a number range for amounts, or a date range for dates. A CSV import gives you values with no guardrails — typos, out-of-range numbers, and free-text chaos in columns that should be controlled. Validation turns the imported sheet from a passive record into an actively defended dataset. (New to the conversion itself? The free CSV to Excel converter builds the workbook first.)

Validation does not clean existing data — it governs what gets entered or changed next. Pair it with a cleanup pass (see the duplicates guide and column-mapping guide) for the full treatment.

The Core Patterns

Dropdown lists (categories, statuses, regions)

1. Put the allowed values in a small list somewhere (a separate "Lists" sheet is cleanest): e.g., Pending, Approved, Rejected.
2. Select the imported column (say, Status).
3. Data > Data Validation > Allow: List > Source: select your list range (or type Pending,Approved,Rejected directly for short lists).
4. Now every cell in the column gets a dropdown arrow — no more "pendign" typos.

For a growing list, make the source a Table column or a dynamic range so new items appear in the dropdown automatically.

Number ranges (amounts, quantities, scores)

Select the Amount column > Data Validation > Allow: Decimal (or Whole number) > set Minimum and Maximum. A quantity column gets between 1 and 10000; a discount column gets between 0 and 1. Anything outside the range triggers the error alert.

Date ranges

Select the Date column > Allow: Date > between start and end. Invoice dates get "between 2026-01-01 and 2026-12-31" — a 2025 straggler or a 3026 typo gets caught at entry.

Text length

Select a code column > Allow: Text length > equal to 8. SKU and ID columns with fixed lengths stay fixed.

Custom Formulas (Advanced Rules)

Allow: Custom accepts any formula that returns TRUE/FALSE, evaluated relative to the active cell. Examples for a cell A2:

• Email shape: =AND(ISNUMBER(SEARCH("@",A2)),ISNUMBER(SEARCH(".",A2)))
• No duplicates in the column: =COUNTIF($A$2:$A$1000,A2)=1
• Must start with "ORD-": =LEFT(A2,4)="ORD-"
• End date after start date (selecting the End column): =B2>A2

Custom formulas are the escape hatch for every rule the presets cannot express. Keep them readable — a validation rule nobody understands gets deleted by the next person.

Input Messages and Error Alerts

Two tabs most people skip, both worth setting:

• Input Message: the hint shown when the cell is selected ("Choose from the list — do not type freely."). This is documentation at the point of use.
• Error Alert: what happens on invalid entry. Stop blocks it outright (use for hard rules); Warning lets the user override with confirmation (use for "usually wrong but occasionally legitimate"); Information just notes it. Match the strictness to the column: amounts get Stop, notes get Information or nothing.

Validating Existing Imported Data

Validation only fires on new entries and edits — it does not flag the 200 bad rows already sitting there. To audit the import:

1. Set up the validation rules.
2. Select the column, then Data > Data Validation > Circle Invalid Data. Excel draws red circles around every existing cell that violates the rule.
3. Fix or remove the circled cells, then clear the circles (same menu > Clear Validation Circles).

This two-step — validate, then circle-and-fix — is the correct order. Circling first with no rules defined finds nothing.

Maintenance

• Copying validation: validation travels with copy-paste of cells. To apply a rule down a growing Table, set it on the Table column — new rows inherit it automatically.
• Paste can bypass validation. A user pasting values over validated cells replaces both value and rule. Protect the sheet (Review > Protect Sheet, allowing "select unlocked cells") if this matters, or re-apply rules periodically.
• Document the rules. A "README" sheet listing each column's rule saves the next editor from guessing why their entry was rejected.

Date and Time Validation

Dates deserve their own rules because bad dates hide inside plausible ones:

• Sensible range. Invoice dates: between the fiscal year start and today (=AND(A2>=DATE(2026,1,1), A2<=TODAY()) as a Custom formula). A 1926 or 2126 date is a typo wearing a disguise.
• No future dates for completed events: Custom =A2<=TODAY().
• Workdays only for scheduled items: Custom =WEEKDAY(A2,2)<6 blocks weekends.
• Times within business hours: for a time column, Custom =AND(A2>=TIME(9,0,0), A2<=TIME(17,0,0)).

Cross-column date logic is where Custom formulas earn their keep: end-after-start (=$B2>=$A2), delivery within 30 days of order (=$B2-$A2<=30), no overlapping bookings for the same resource (COUNTIFS with date overlap conditions). Select the dependent column and write the formula relative to the active row.

One caution: date validation cannot fix dates that imported as text — <=TODAY() on the text "2026-10-20" misbehaves. Fix date types at import first, then validate. Validation assumes the values are what they claim to be; typing is what makes that true.

Validation order of operations. For each new source file: (1) import with correct types, (2) Circle Invalid Data against your standard rules to audit what arrived, (3) fix violations, (4) leave the rules in place for ongoing edits. Skipping step 2 is how bad data gets grandfathered in — the rules guard the future while the past stays dirty. The circle-and-fix pass is a one-time cost per source; the rules pay off forever.

FAQ

Does data validation fix bad data already in the sheet?

No — it only governs new entries and edits. Use Data > Data Validation > Circle Invalid Data to find existing violations, then fix them by hand.

Why does my dropdown list not show new items I added?

The Source range is fixed. Point it at a Table column or a dynamic named range so it grows automatically.

Can validation stop paste from overwriting rules?

Not by itself — pasting replaces validation. Protect the sheet or train the workflow; for critical columns, re-check rules with Circle Invalid Data after bulk pastes.

What's the difference between Warning and Stop alerts?

Stop blocks the entry entirely. Warning asks "continue anyway?" and allows override. Use Stop for impossible values, Warning for improbable-but-possible ones.

Will validation slow down a large sheet?

Negligibly for lists and ranges. Complex custom formulas (COUNTIF over huge ranges) can add recalculation cost — keep the evaluated ranges as tight as real data needs.

Final Checklist

• Every controlled column has a rule: List for categories, ranges for numbers/dates, length for codes.
• Input messages explain the rule; error alerts match the strictness (Stop vs Warning).
• Existing data audited with Circle Invalid Data; violations fixed.
• Rules applied to Table columns so new rows inherit them.
• Rules documented on a README sheet for the next editor.

If all five checks pass, your imported data now defends itself — bad entries get stopped at the door, not discovered in next month's report.