Validating Excel Data with Python
Every reporting job eventually receives a file where a date is 31/02/2026, a quantity is n/a, and a region that used to be North is now north . Validation is the layer that turns those into a clear message instead of a wrong number in a board pack. There are two halves to it, and they solve different problems: pandas checks reject bad data that has already been entered, while openpyxl rules stop it being entered next time. This guide, part of Advanced Data Transformation and Cleaning, builds both.
Install the dependencies
pip install pandas openpyxl
Create a workbook with realistic problems
Every example below runs against this file, which contains the failure modes real submissions have:
import pandas as pd
rows = [
{"Order_ID": 2001, "Region": "North", "Order_Date": "2026-01-14", "Quantity": 4, "Unit_Price": 19.99},
{"Order_ID": 2002, "Region": "north ", "Order_Date": "2026-01-15", "Quantity": "2", "Unit_Price": 49.5},
{"Order_ID": 2003, "Region": "Souht", "Order_Date": "15/01/2026", "Quantity": -7, "Unit_Price": 12.25},
{"Order_ID": 2004, "Region": "West", "Order_Date": "2026-02-30", "Quantity": 5, "Unit_Price": None},
{"Order_ID": 2001, "Region": "West", "Order_Date": "2026-01-19", "Quantity": 3, "Unit_Price": 8.75},
{"Order_ID": None, "Region": "", "Order_Date": "", "Quantity": "n/a", "Unit_Price": 15.0},
]
pd.DataFrame(rows).to_excel("submitted.xlsx", index=False, sheet_name="Orders")
print("wrote submitted.xlsx")
Six rows, seven distinct problems: a trailing space, a misspelt region, a negative quantity, an impossible date, a duplicate order number, a missing price and a text value in a numeric column. A validation layer should describe all seven without stopping at the first.
Layer 1: structural checks
Before looking at any value, confirm the file has the shape your code expects. Structural failures are worth stopping the job for, because nothing downstream can be trusted:
import pandas as pd
REQUIRED = ["Order_ID", "Region", "Order_Date", "Quantity", "Unit_Price"]
def load_and_check_structure(path, sheet="Orders"):
sheets = pd.ExcelFile(path).sheet_names
if sheet not in sheets:
raise ValueError(f"sheet {sheet!r} not found — file has {sheets}")
df = pd.read_excel(path, sheet_name=sheet, dtype=object)
df.columns = [str(c).strip() for c in df.columns]
missing = [c for c in REQUIRED if c not in df.columns]
if missing:
raise ValueError(f"missing column(s): {missing}; found {list(df.columns)}")
if df.empty:
raise ValueError("the sheet has headers but no data rows")
return df[REQUIRED]
df = load_and_check_structure("submitted.xlsx")
print(df.shape)
Reading with dtype=object is deliberate. Letting pandas guess types hides exactly the problems you are looking for: a column with one "n/a" silently becomes text, and a date column with one European date becomes a mix of timestamps and strings. Read everything raw, then coerce with your eyes open.
Layer 2: type coercion that records its failures
Coercion and validation are the same operation if you keep the failures. errors="coerce" turns anything unparsable into NaT/NaN, and comparing before and after tells you which rows broke:
import pandas as pd
def coerce_types(df):
issues = []
out = df.copy()
out["Order_ID"] = pd.to_numeric(out["Order_ID"], errors="coerce")
out["Quantity"] = pd.to_numeric(out["Quantity"], errors="coerce")
out["Unit_Price"] = pd.to_numeric(out["Unit_Price"], errors="coerce")
out["Order_Date"] = pd.to_datetime(out["Order_Date"], errors="coerce", format="mixed")
for column in ("Order_ID", "Quantity", "Unit_Price", "Order_Date"):
broke = out[column].isna() & df[column].notna() & (df[column].astype(str).str.strip() != "")
for idx in out.index[broke]:
issues.append({
"row": int(idx) + 2, # +2: header row and zero-based index
"column": column,
"value": df.loc[idx, column],
"problem": f"could not be read as {'a date' if column == 'Order_Date' else 'a number'}",
})
return out, issues
typed, issues = coerce_types(df)
for issue in issues:
print(issue)
The +2 on the row number is the small courtesy that makes a validation report usable: it converts a pandas index into the row number the reader will see in Excel, so they can go straight to the cell.
The bottom row is the subtlety worth building in from the start: a value that was blank to begin with is a missing data question, not a bad data question. Conflating them produces validation reports full of noise, and readers stop opening them.
Layer 3: business rules
Type checks say a value is a number; business rules say it is a plausible one. Express each rule as a boolean mask with a message, so adding a rule is one entry rather than a new branch:
import pandas as pd
VALID_REGIONS = {"North", "South", "West", "Central"}
def apply_rules(df):
normalised = df.copy()
normalised["Region"] = normalised["Region"].astype(str).str.strip().str.title()
rules = [
("Order_ID", normalised["Order_ID"].isna(), "order id is missing"),
("Order_ID", normalised["Order_ID"].duplicated(keep=False) & normalised["Order_ID"].notna(),
"order id appears more than once"),
("Region", ~normalised["Region"].isin(VALID_REGIONS), "region is not on the approved list"),
("Quantity", normalised["Quantity"] <= 0, "quantity must be greater than zero"),
("Unit_Price", normalised["Unit_Price"].isna(), "unit price is missing"),
("Order_Date", normalised["Order_Date"] > pd.Timestamp("today"), "order date is in the future"),
]
issues = []
for column, mask, message in rules:
for idx in normalised.index[mask.fillna(False)]:
issues.append({
"row": int(idx) + 2,
"column": column,
"value": df.loc[idx, column],
"problem": message,
})
return normalised, issues
checked, rule_issues = apply_rules(typed)
print(f"{len(rule_issues)} rule violation(s)")
Normalising Region before testing it is what makes the rule fair: "north " is a formatting problem, not a wrong region, and correcting it silently while rejecting "Souht" is exactly the behaviour a submitter expects. Where cleaning ends and validation begins is a judgement call — the rule of thumb is to auto-fix anything unambiguous and report anything that requires a decision. The cleaning Excel data with pandas guide covers the fixing side in depth.
Layer 4: quarantine instead of crashing
With the issues collected, split the frame. Clean rows go on to the report; problem rows are held back with their reasons attached:
import pandas as pd
def split_clean_and_quarantine(df, issues):
bad_rows = {issue["row"] - 2 for issue in issues}
clean = df.drop(index=bad_rows, errors="ignore")
quarantined = df.loc[sorted(bad_rows)].copy()
reasons = {}
for issue in issues:
reasons.setdefault(issue["row"] - 2, []).append(f"{issue['column']}: {issue['problem']}")
quarantined["Why_rejected"] = [
"; ".join(reasons.get(idx, [])) for idx in quarantined.index
]
return clean, quarantined
clean, quarantined = split_clean_and_quarantine(checked, issues + rule_issues)
print(f"{len(clean)} clean row(s), {len(quarantined)} quarantined")
Stopping the whole job on one bad row is rarely the right behaviour for a recurring report: the business still needs Monday's numbers, and one malformed line should not hold them up. Stopping on a structural problem is different — if a column is missing, every row is suspect.
Layer 5: report the failures back in Excel
Submitters live in Excel, so the error report belongs in the workbook they receive:
import pandas as pd
with pd.ExcelWriter("validated.xlsx", engine="openpyxl") as writer:
clean.to_excel(writer, sheet_name="Clean", index=False)
quarantined.to_excel(writer, sheet_name="Rejected", index=False)
pd.DataFrame(issues + rule_issues).to_excel(writer, sheet_name="Issues", index=False)
print("wrote validated.xlsx with Clean, Rejected and Issues sheets")
Three sheets, one file, no email thread. Making the Issues sheet the active sheet so it opens first is a small touch that gets problems fixed faster, and highlighting the offending cells on the original data turns the report into something a person can act on without cross-referencing row numbers.
Layer 6: stop the next round of bad data
Everything so far reacts to problems. openpyxl's DataValidation prevents them, by writing Excel's own validation rules into the file you send back:
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = load_workbook("validated.xlsx")
ws = wb["Clean"]
last = ws.max_row + 200 # leave room for new rows
region = DataValidation(
type="list",
formula1='"North,South,West,Central"',
allow_blank=False,
showErrorMessage=True,
)
region.errorTitle = "Invalid region"
region.error = "Pick a region from the dropdown."
ws.add_data_validation(region)
region.add(f"B2:B{last}")
quantity = DataValidation(type="whole", operator="greaterThan", formula1=0, showErrorMessage=True)
quantity.errorTitle = "Invalid quantity"
quantity.error = "Quantity must be a whole number greater than zero."
ws.add_data_validation(quantity)
quantity.add(f"D2:D{last}")
wb.save("validated_with_rules.xlsx")
Excel now refuses the values your pandas layer would have rejected, at the moment someone types them. Two limits are worth knowing: these rules constrain typing, not pasting, in many Excel versions, and openpyxl itself does not enforce them when writing — so the Python checks stay necessary. Adding dropdown validation covers the list-based rules in more detail, including lists that read from a named range.
Choosing where each check belongs
| Check | pandas, on read | openpyxl, on write | Both |
|---|---|---|---|
| Required column present | Yes | — | — |
| Value parses as a number or date | Yes | — | — |
| Value is on an approved list | Yes | Dropdown | Yes |
| Number within a range | Yes | Numeric rule | Yes |
| Cross-row rules (duplicates, totals) | Yes | — | — |
| Cross-column rules (end date after start) | Yes | Formula rule | Sometimes |
| Cosmetic normalisation (trimming, casing) | Yes | — | — |
Anything involving more than one row can only be done in pandas — Excel's validation looks at one cell at a time. Anything meant to guide a human typing belongs in the workbook. The middle rows of that table are the ones worth implementing twice: the dropdown makes the right value easy to pick, and the pandas check still catches the value that arrived by paste, by import, or from a copy of the file that predates the rules. Duplicating a rule in two places is only a maintenance problem if the two definitions can drift, so keep the approved list in one place — a lookup sheet or a constants module — and generate both the Excel rule and the Python check from it.
Validate against reference data, not a hardcoded list
VALID_REGIONS as a Python set works until someone opens a new region and nobody tells you. Reference lists belong in data, and the most convenient place for them is usually a lookup sheet in the same workbook or a small master file that the business owns:
import pandas as pd
def load_reference(path, sheet="Lookups", column="Region"):
ref = pd.read_excel(path, sheet_name=sheet, usecols=[column])
values = (
ref[column].dropna().astype(str).str.strip().str.title().unique().tolist()
)
if not values:
raise ValueError(f"reference list {sheet}!{column} is empty — refusing to validate")
return set(values)
# A missing lookup sheet should fail loudly rather than silently approving everything
try:
valid_regions = load_reference("master_data.xlsx")
except (FileNotFoundError, ValueError) as exc:
raise SystemExit(f"cannot validate regions: {exc}")
unknown = sorted(set(checked["Region"].dropna()) - valid_regions)
print("regions not in the master list:", unknown)
The empty-list guard matters more than it looks. A reference list that fails to load and quietly becomes an empty set turns every row into a violation; one that becomes None and is skipped turns validation off entirely. Both have shipped in production somewhere, and the second is much harder to notice.
Where the reference data is genuinely large — a product catalogue with 40,000 SKUs — checking membership with a Python set is still the fastest approach, and df["SKU"].isin(catalogue) is a single vectorised pass. Reserve database round trips for the cases where the list changes during the run.
Rules that span columns, rows or files
The checks that catch real errors are usually relational. A quantity is fine and a price is fine, but their product does not match the total the submitter typed. Express these the same way as any other rule:
import pandas as pd
def relational_checks(df, previous_month=None):
issues = []
if {"Start_Date", "End_Date"} <= set(df.columns):
bad = df["End_Date"] < df["Start_Date"]
for idx in df.index[bad.fillna(False)]:
issues.append({"row": int(idx) + 2, "column": "End_Date",
"value": df.loc[idx, "End_Date"], "problem": "ends before it starts"})
if {"Quantity", "Unit_Price", "Line_Total"} <= set(df.columns):
expected = df["Quantity"] * df["Unit_Price"]
mismatch = (df["Line_Total"] - expected).abs() > 0.005
for idx in df.index[mismatch.fillna(False)]:
issues.append({"row": int(idx) + 2, "column": "Line_Total",
"value": df.loc[idx, "Line_Total"],
"problem": f"does not equal quantity × price ({expected[idx]:.2f})"})
if previous_month is not None:
shrunk = len(df) < len(previous_month) * 0.5
if shrunk:
issues.append({"row": 0, "column": "(file)", "value": len(df),
"problem": f"row count halved versus last month ({len(previous_month)})"})
return issues
The tolerance of half a cent in the arithmetic check is not laziness — floating-point money almost never matches exactly, and an exact comparison produces a validation report where every row is wrong. The row-count check is a different species again: it says nothing about any single row, and it is the one that catches a truncated export or a filter left on before the file was saved.
Wire validation into a scheduled job
A validation layer earns its keep when it runs unattended. Give it an exit code, a log line per failure class and a way to alert a human:
import logging
import sys
logging.basicConfig(
level=logging.INFO,
format="%(asctime)s %(levelname)s %(message)s",
handlers=[logging.FileHandler("validation.log"), logging.StreamHandler()],
)
log = logging.getLogger("validate")
def run(path):
try:
raw = load_and_check_structure(path)
except ValueError as exc:
log.error("structural failure in %s: %s", path, exc)
return 2 # nothing usable — page someone
typed, type_issues = coerce_types(raw)
checked, rule_issues = apply_rules(typed)
all_issues = type_issues + rule_issues
clean, quarantined = split_clean_and_quarantine(checked, all_issues)
for issue in all_issues[:50]:
log.warning("row %s %s: %s (value=%r)",
issue["row"], issue["column"], issue["problem"], issue["value"])
log.info("%s: %d clean, %d quarantined, %d issue(s)",
path, len(clean), len(quarantined), len(all_issues))
if len(quarantined) > len(checked) * 0.2: # more than a fifth rejected
log.error("rejection rate above threshold — treating as a failed submission")
return 1
return 0
if __name__ == "__main__":
sys.exit(run("submitted.xlsx"))
Three exit codes carry the whole story: 0 for a good run, 1 for a submission bad enough to need a human, and 2 for a file that could not be read at all. A scheduler can act on each differently, and the scheduled reporting and error alerting guides show how to turn those codes into retries and emails.
Test the rules, not just the data
Validation code is the part of a pipeline nobody exercises until something goes wrong, which is precisely why it deserves tests. Each rule gets a row that should pass and a row that should fail:
import pandas as pd
import pytest
def issues_for(row):
df = pd.DataFrame([row])
typed, type_issues = coerce_types(df)
_, rule_issues = apply_rules(typed)
return [i["problem"] for i in type_issues + rule_issues]
GOOD = {"Order_ID": 1, "Region": "North", "Order_Date": "2026-01-14",
"Quantity": 3, "Unit_Price": 10.0}
def test_clean_row_has_no_issues():
assert issues_for(GOOD) == []
@pytest.mark.parametrize("field,value,expected", [
("Quantity", -1, "quantity must be greater than zero"),
("Region", "Atlantis", "region is not on the approved list"),
("Unit_Price", None, "unit price is missing"),
("Order_Date", "2026-02-30", "could not be read as a date"),
])
def test_each_rule_fires(field, value, expected):
assert expected in issues_for({**GOOD, field: value})
The test_clean_row_has_no_issues case is the one that pays for itself. Rules tend to accumulate, and a new rule that is slightly too strict shows up here immediately rather than in a Monday morning report where 80% of rows have been quarantined.
Deciding the severity of each rule up front is what stops a validation layer from being either an alarm nobody trusts or a filter that silently drops half the data.
Common errors and fixes
| Symptom | Cause | Fix |
|---|---|---|
| Every row flagged as an invalid region | Reference list failed to load and became empty | Raise when the reference list is empty |
| Dates silently wrong by month/day | Mixed European and ISO dates in one column | Read raw, then pd.to_datetime(..., format="mixed") and check the failures |
| Valid rows rejected for whitespace | Compared before normalising | Strip and case-normalise, then compare |
| Numbers read as text | The column contained one "n/a" | Read with dtype=object, coerce explicitly |
| Arithmetic check fails on every row | Exact float comparison | Compare with a tolerance |
| Report full of blank-cell issues | Missing data conflated with bad data | Only flag values that were present and unparsable |
| Duplicate check misses cases | Key column has trailing spaces or mixed case | Normalise the key before duplicated() |
Key takeaways
- Read submissions with
dtype=objectso type problems stay visible instead of being guessed away. - Coerce with
errors="coerce"and record which values failed — coercion and validation are one step. - Separate "was blank" from "was unreadable", or your reports fill with noise.
- Express business rules as mask-and-message pairs so the list is easy to extend and easy to audit.
- Quarantine bad rows and deliver the good ones; reserve hard failures for structural problems.
- Return the workbook with
DataValidationrules so the next submission has fewer problems to find.
Frequently asked questions
What is the difference between validating in pandas and in openpyxl?
pandas validates data that already exists, so your job can reject a bad file. openpyxl DataValidation writes rules into the workbook so the next person typing into it is constrained at entry time. Production workflows usually need both.
Where should validation run in a reporting pipeline? Immediately after reading the source and before any transformation. Failing at the door keeps bad values out of joins and aggregations, where they are far harder to trace.
Should a validation failure stop the whole job? Stop on structural failures such as a missing column or an unparsable date column. For row-level problems it is usually better to quarantine the bad rows, process the rest, and report what was dropped.
Does openpyxl enforce its own validation rules when writing?
No. DataValidation is metadata for Excel's user interface. openpyxl will happily write a value that breaks the rule, so check values in Python as well.
How do I tell users which rows failed? Write the failures to their own sheet in the delivered workbook, with the source row number, the column, the offending value and a plain-language reason.
Related
Up to the parent guide:
- Advanced Data Transformation and Cleaning — the wider ingest, clean, transform and export pipeline.
Go deeper here:
- Add Dropdown Data Validation to Excel with openpyxl — list rules, named ranges and error messages.
- Validate Excel Columns Before Import with pandas — the structural gate in detail.
- Check Excel Data Types with pandas — dtypes, coercion and mixed columns.
- Highlight Invalid Cells in Excel with Python — mark the exact cells that failed.
- Find Duplicate Rows in Excel with Python — the cross-row check Excel cannot do.
- Compare Two Excel Files for Differences with Python — validate one version against another.
Related areas:
- Cleaning Excel Data with pandas — the fixing counterpart to these checks.
- Handling Missing Data in Excel Reports — deciding what a blank means.
- Applying Conditional Formatting with openpyxl — making problems visible in the delivered file.