Cleaning & transforming data
Validation & data quality
Schema checks before import, dropdown rules in the sheet, and flagging bad cells.
Start with the full guides
The long-form treatment of validation & data quality, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toDetect Outliers in Excel Data with pandasFind the invoice for 48,000 in a column of 400s, the negative quantity and the delivery that took 90 days. Compare IQR fences, z-scores and median-based robust scores on the same data, detect outliers within each group, add business rules, and flag rows in the workbook for review instead of deleting them.
- How-toAdd Dropdown Data Validation to Excel with openpyxlWrite Excel dropdown lists from Python with openpyxl DataValidation — inline lists, lists from a lookup sheet or named range, custom error messages, and the 255-character limit.
- How-toAnonymise Personal Data in an Excel File with PythonPrepare a workbook of customer or employee records for sharing: drop direct identifiers, replace IDs with keyed pseudonyms that stay consistent across files, generalise ages and postcodes, scrub emails and phone numbers out of free text, check how many people share each combination of details, and scan the output for anything left behind.
- How-toBuild a Data Quality Scorecard for an Excel FileScore an incoming Excel file on data quality with pandas, column by column: completeness, validity against a type, pattern or range, uniqueness for key columns, consistency with an allowed list and freshness of dates — declared once in a small rules table, combined into a score per column and for the file, compared with last month's file, and written as a colour-coded scorecard with sample failing values.
- How-toCheck Excel Data Types with pandasFind out what pandas actually read from a spreadsheet — object columns hiding mixed types, numbers stored as text, Excel date serials — and coerce them with a record of every failure.
- How-toCheck References Between Excel Sheets with pandasFind orders that point at customers who do not exist, products missing from the price list and lookup sheets with duplicate keys. Check every reference from one sheet to another with pandas, normalise keys so near-misses are caught, report the Excel rows that break a reference, and flag master rows nothing uses.
- How-toCheck That Totals in a Report Add UpAutomatically check a finished Excel report before it is sent: every total row equals the sum of the rows above it, every total column equals the sum across, the grand total cross-foots both ways, the summary sheet agrees with the detail sheet, and differences within a rounding tolerance are accepted — with each failure reported by sheet and cell so it can be fixed.
- How-toCreate Dependent Dropdown Lists in Excel with PythonBuild cascading dropdowns — pick a category, then only its subcategories are offered — from a mapping table in Python: a hidden lookup sheet, one named range per category with safe names, a data validation list for the first column and an INDIRECT-based list for the second, error messages, and a Python check that existing rows still match.
- How-toDetect Possible Duplicate InvoicesFind supplier invoices that may have been entered or paid twice in an Excel payables export: exact duplicates, invoice numbers that differ only in formatting such as "INV-001042" and "1042", the same amount from the same supplier within a few days under a different number, and near-identical supplier names — scored, explained and listed for review before the payment run.
- How-toDetect Schema Drift Between Monthly Excel FilesCatch the silent changes in a recurring Excel export before they break a report: take a schema snapshot of each file — columns, order, types, blanks, categories and ranges — compare it with last month's, flag added, removed and probably renamed columns, type changes, new category values and shifted ranges, and stop the pipeline with a readable drift report when a change matters.
- How-toFind Gaps in a Numbered Sequence with pandasCheck that invoice, cheque or ticket numbers in an Excel export run without gaps: extract the number from IDs such as INV-2026-000123, check each series separately, list missing numbers as compact ranges, catch duplicates and numbers issued out of date order, and write an exceptions report.
- How-toHighlight Invalid Cells in Excel with PythonMark the exact cells that failed validation with openpyxl — a red fill, a comment explaining the rule, a legend and an issues sheet — so a submitter can fix the file in minutes.
- How-toProfile an Excel Dataset with pandasGet to know a workbook before cleaning or loading it: one row per column with its type, blanks, distinct values, most common values, ranges and text lengths; columns that mix numbers and text; value patterns such as "AA-9999"; columns that could be a unique key; and the whole profile written to a workbook people can read.
- How-toReconcile Excel Totals Against Control Totals with PythonProve that an Excel import or report is complete by reconciling it with control totals — record counts, amount totals and hash totals supplied by the source system — overall and per group, at every stage of a pipeline, with money compared in cents and a reconciliation sheet that shows exactly where figures diverge.
- How-toRun a Benford's Law Check on Excel DataTest whether the leading digits of amounts in an Excel ledger follow Benford's law: extract first digits correctly, compare observed and expected frequencies, measure conformity with the mean absolute deviation and a chi-square test, drill into over-represented digits such as amounts just below an approval limit, and chart the result in Excel.
- How-toValidate Check Digits in Excel Data with PythonBarcodes, IBANs and many reference numbers carry a check digit that catches most typing errors. Validate them in a spreadsheet with Python: EAN-13 and other GTIN barcodes, IBAN mod-97 checks, the Luhn algorithm used by payment cards and many IDs, reading the columns as text so no digit is lost, cross-checking against python-stdnum, and highlighting invalid cells for the people who fix them.
- How-toValidate Dates Fall Within an Expected PeriodCheck the dates in a monthly Excel import before loading it: rows outside the reporting month, dates in the future, years with a typo such as 2062 or 1926, day and month swapped, numbers that are really Excel serials, text that is not a date at all, and transactions on days the business was closed — each row tagged with the reason and the likely correct date where one can be inferred.
- How-toValidate Email and Phone Formats in Excel Data with PythonCheck the email addresses and phone numbers in a contact sheet before importing it — a quick regex screen, email-validator for real address syntax, phonenumbers for national and international formats, normalised values for deduplication, and a report of invalid cells with their spreadsheet row numbers.
- How-toValidate Excel Columns Before Import with pandasCheck an incoming workbook's sheet, headers, column order and required fields with pandas before a single row reaches your pipeline — with clear, actionable failure messages.
- How-toValidate Excel Data with pandera SchemasDeclare a schema for a spreadsheet and enforce it: column types, ranges, regex checks, uniqueness, cross-column rules, lazy validation and a readable error report.
- How-toValidate Excel Rows with pydantic ModelsDescribe what a valid row looks like once, as a pydantic model, and check every row of a sheet against it. Map Excel headers to fields with aliases, constrain values, normalise text before checking, add rules that span columns, and turn validation errors into a report that names the Excel row, column and value.
- How-toValidate Parent-Child Codes in a HierarchyCheck a parent-child table maintained in Excel — an account tree, cost-centre structure or product category list — before anything is rolled up through it: duplicate codes, parents that do not exist, more than one top node, cycles and self-references, unreachable branches, depth limits, code-format rules that tie children to parents, and postings to non-leaf nodes, all reported with the Excel row to fix.
- How-toHighlight Cells Containing Specific Text with openpyxlColour every status that mentions "late", every reference that begins with INV, or every note containing any word from a list. Write containsText, beginsWith and formula rules with openpyxl, choose between case-insensitive SEARCH and case-sensitive FIND, match whole words so "late" does not match "calculate", and drive the keyword from a cell.
- How-toFind Near-Duplicate Names Within One Excel ColumnCustomer and supplier lists collect variants of the same name — "ACME Ltd", "Acme Limited", "A.C.M.E. Ltd." — that exact de-duplication misses. Normalise names, compare every pair with rapidfuzz, group matches into clusters, choose a canonical spelling, block by first letter to scale to tens of thousands of names, and write a review sheet so a person confirms each merge.
- How-toRead Dropdown Lists and Validation Rules from an Existing WorkbookFind out what a template's dropdowns allow — the regions, statuses and priority codes behind each data validation rule — and check the data people entered against them. Read validation rules with openpyxl, resolve inline lists, range references and named ranges to real values, and report every cell that breaks its rule.
- How-toFind and Report Missing Values in an Excel FileAudit an Excel file for gaps before you use it — per-column null counts, disguised blanks like n/a and dashes, missing-value patterns, and a formatted report of what is absent.
- How-toFlag Rows with Too Many Missing ValuesFind the incomplete records in an Excel sheet before they distort a report: count missing values per row with pandas, treat placeholders like "n/a", "-" and "TBC" as missing, weight required fields more than optional ones, choose a threshold, group the flagged rows by who entered them, and write a workbook where incomplete rows are highlighted and listed for follow-up.
- How-toValidate a pandas Merge to Avoid Duplicate RowsA duplicate key in a lookup sheet makes a pandas merge silently add rows and inflate totals. Use merge's validate argument, indicator column and before-and-after checks to catch duplicate keys, unmatched rows and missing keys that match each other before a report goes out.
- How-toCompare an Excel File with a Database TableFind every difference between a spreadsheet and the database table it is supposed to match: normalise both sides so formatting noise does not count, outer-join on the key, list rows only in Excel or only in the database, compare fields with tolerances for numbers and dates, write a highlighted difference report, and preview the SQL that would bring the table in line.
- How-toExport Database Lookup Tables as Excel Dropdown ListsBuild an Excel input template whose dropdowns come straight from the database: read the active cost centres, projects and expense types, write them to a hidden lists sheet, give each list a named range, attach data validation to the input columns, show codes with their descriptions while storing a code that maps back cleanly, refresh the lists in an existing template, and check the returned file against the database.
- How-toCompare Two Excel Files for Differences with PythonDiff two versions of a workbook with pandas — added, removed and changed rows keyed on an identifier, a cell-level change list, and a colour-coded difference report in Excel.
- How-toFind Duplicate Rows in Excel with PythonDetect exact and near-duplicate rows in a spreadsheet with pandas — key-based duplicates, whitespace and case traps, duplicate groups with counts, and a report you can send back.
- How-toReconcile a Stock Count Against System InventoryCompare a physical stock count with the inventory system in pandas: combine count sheets from several counters, normalise SKU codes, total items found in more than one place, outer-join to the system quantities with an indicator, value each variance at cost, pick lines to recount by value and percentage thresholds, and produce an adjustment summary and Excel workbook.
- How-toSend Out Budget Templates and Consolidate the ReturnsRun the annual budget round from Python: generate one pre-filled budget workbook per cost centre with locked formulas, validated input cells and a hidden identity sheet, then read the returned files back, check that each is the right template for the right cost centre with sensible numbers, consolidate them into one table and keep a tracker of who has returned what.
- How-toAdd a Data Dictionary Sheet to an Excel ReportDocument every column of a multi-sheet Excel report in a generated data dictionary sheet: descriptions kept next to the code, types, units and number formats, example values, blank counts and ranges profiled from the data, links to each sheet, a check that fails on undocumented columns, and a dictionary for a workbook you did not build.
- How-toAdd a Dropdown Filter That Drives an Excel DashboardGive an Excel dashboard a slicer-like control from Python — a data validation dropdown fed by a hidden list, an "All" option, SUMIFS formulas and a chart that follow the selection, and a second filter combined with the first — because native slicers cannot be written by openpyxl or XlsxWriter.
- How-toValidate an Excel Report Before Sending ItThe last gate before delivery — check the file exists and is a real workbook, the sheets and rows are there, totals are plausible, and this run agrees with the last one.
- How-toImport an Excel File into Django ModelsLoad a spreadsheet of products, customers or prices into a Django database the safe way: read every cell as text, validate each row with the model's own full_clean, report errors by Excel row number, reject duplicate keys in the file, create missing related objects, upsert everything in one transaction with bulk_create, and return an error workbook the uploader can fix.
- How-toValidate an Uploaded Excel File Before Processing It in a Web AppCheck an uploaded workbook in layers, cheapest first — the bytes, the zip container, then the rows — so renamed CSVs, old .xls files, password-protected workbooks, macros, external links and compression bombs are refused in under a millisecond, and data errors come back as a list of exact cells the user can fix.
- How-toAudit Every Formula in a Workbook with PythonBuild an inventory of every formula in a workbook — which functions it uses, which sheets and external files it depends on, which numbers are hard-coded in it — then find the inconsistent formula in a column of identical ones and the value someone typed over a formula. Output the audit as its own sheet.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
Follow it through a track
Prefer an ordered route? These tracks cover validation & data quality along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Validation & data quality is part of Cleaning & transforming data.