How to Validate a Monthly Import File in Excel
The same file arrives every month, and some months it is wrong: a renamed column, a new currency code, a blank where an invoice number should be. You find out downstream, after the import has already failed or — worse — succeeded with bad data.
Last updated: October 2026
| Rule | What it catches | |
|---|---|---|
| Column: Invoice · required | → | Blank invoice numbers, by row |
| Amount · number · 0–10000 | → | Text like "n/a", values out of range |
| Currency · allowed BHD,USD | → | A new code nobody mentioned |
| Date · date | → | Anything that will not parse |
Method 1 — Data Validation rules
Excel's built-in Data Validation guards typing, not pasting or importing — a pasted block bypasses it entirely. It also lives in the cells, so it does not survive a file that arrives fresh each month.
Method 2 — a checking formula column
A column of IF(ISNUMBER(...)) tests works and has to be rebuilt, or carefully copied forward, on every new file — and tells you nothing about a column that is missing entirely.
The 1-click way — Tellsheet
Import Profile Check keeps the rules as an ordinary table in your workbook: one row per expected column, with optional type, required flag, allowed values and numeric range. Point it at this month's file and it reports every problem with its sheet row number, plus the structural ones a formula cannot see — a column the profile expects and the file lacks, or one the file has gained. Because the profile is a normal table it travels with the workbook, a colleague can read it, and editing a rule is editing a cell.
Frequently asked questions
Why not use Data Validation?
It only guards typed entry. Pasted and imported data bypass it, which is exactly how a recurring file arrives.
Where are the rules stored?
In a small table in your workbook. That makes the profile visible, editable, shareable and durable — unlike settings hidden in an app, which are per-machine and lost on a cache clear.
Will it catch a renamed column?
Yes. A column the profile expects but the file lacks is reported, and so is a column the file has gained — usually the first sign a supplier changed their export.
Does it change my data?
No. It writes a problem report to a new sheet; your file is untouched.
Related Excel guides
Check next month's file against last month's rules
Save the profile once; re-run it every month in one click.
Get Tellsheet free See pricing