Cleaning & transforming data
Data cleaning
Whitespace, text-as-numbers, blank rows, duplicates and columns that need splitting.
Start with the full guides
The long-form treatment of data cleaning, with the reasoning the shorter pages assume.
- OverviewAdvanced Data Transformation and Cleaning for Excel AutomationBuild production-grade Python pipelines that clean, validate, merge, and format Excel data with pandas and openpyxl — staged, testable, and reproducible.
- GuideCleaning Excel Data With Pandas: A Production WorkflowTurn messy Excel exports into reliable reporting inputs with pandas: load as text, normalize headers and types, drop noise, deduplicate, validate, and export.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toClean Mixed-Currency Amounts into Amount and Currency ColumnsTurn an Excel column of amounts typed with their currency — "€1.234,50", "USD 1,200", "1200 GBP", "£950", "CHF 1'250.00", "(45.00) EUR" — into a numeric amount column and an ISO currency code column with pandas: map symbols and codes, resolve the ambiguous dollar sign, detect decimal commas, keep negative signs and parentheses, convert to one reporting currency, and list what could not be read.
- How-toCoalesce Several Excel Columns into One with pandasTake the first non-empty value from several columns — Mobile, else Work phone, else Home phone — the way SQL's COALESCE or a chain of nested IFs in Excel does: bfill across columns, combine_first for two columns, treating blanks and placeholder text as missing, recording which column supplied each value, and flagging rows where the sources disagree.
- How-toConvert Currency Strings Like "$1,234.56" and "(500)" to Numbers with pandasAmount columns exported as text — "$1,234.56", "(1,200.00)", "-€50", "1.234,56 EUR", "750.00-" or "100.00 CR" — cannot be summed. Parse every common currency format into a signed number and a separate currency code, report the values that do not parse, and write real numbers back to Excel.
- How-toConvert Excel Text Columns to Numbers with pandasNumbers stored as text will not sum. Convert them in pandas — currency symbols, thousands separators, European decimals, parentheses negatives, and reporting what failed.
- How-toConvert Percentage Strings Like "12.5%" to Numbers with pandasA rate column that mixes typed text like "12.5%" with real percentage cells that pandas reads as 0.125 cannot be averaged. Convert every form — "12.5%", "12,5 %", "-3%", "25 bp", fractions and bare numbers — to one consistent fraction, flag the ambiguous values, and write them back with a percent format.
- How-toConvert Trailing Minus and CR/DR Amounts with pandasTurn amounts exported by SAP, Oracle and bank systems into signed numbers: trailing minus signs like 1,234.56-, accounting brackets, CR and DR suffixes, a separate debit/credit indicator column, European 1.234,56 formats, and a check that nothing was left as text.
- How-toExtract Numbers and Codes from Text with pandasPull order numbers, SKUs, quantities and amounts out of free-text cells like "Re: PO-10442, 3 x SKU AB-1200 @ $12.50". Use str.extract with named groups for one match per cell, extractall for many, fullmatch to validate codes, and a report of the rows where nothing matched.
- How-toFilter Excel Rows by Condition with pandasKeep only the rows you need from a sheet: boolean masks, combining conditions with & | ~ and the brackets they need, isin, between and date ranges, text matching that does not crash on blanks, query strings, filters defined in a config file, and deleting rows from a formatted workbook in place with openpyxl.
- 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-toKeep the Latest Record per Key When Deduplicating with pandasA customer export lists the same customer several times as their details changed. Keep only the most recent row per customer with sort_values and drop_duplicates, break ties on equal timestamps, handle dates stored as text and missing dates, and avoid groupby().last(), which quietly mixes values from different rows.
- How-toMap Free-Text Job Titles to Standard CategoriesTurn hundreds of free-text job titles from an HR export or a contact list into a handful of standard job families and seniority levels with pandas: normalise the text, expand abbreviations, apply an ordered keyword rule table kept in Excel, fall back to fuzzy matching against known titles, separate seniority from function, and report every title that still needs a human decision.
- How-toNormalise Postcodes and ZIP Codes with pandasClean the postcode column of an international address list from Excel: restore the leading zeros Excel stripped from US ZIP codes and other numeric postcodes, format UK postcodes with the single space in the right place, handle ZIP+4, Dutch and Canadian formats, validate each value against its country's pattern, and list the ones that need a human.
- How-toPandas: Drop Duplicates From an Excel ColumnRemove duplicate rows by a single Excel column with pandas drop_duplicates and the subset parameter — including keep options, NaN behavior, and pre-cleaning.
- How-toRemove Accents and Normalise Unicode in Excel TextMake text from different sources compare equal when it looks equal: Unicode normal forms NFC and NFKC for composed and decomposed accents, full-width characters, ligatures and non-breaking spaces, removing zero-width characters, casefold instead of lower, stripping accents for matching keys while keeping the original for display, and converting smart quotes and dashes.
- How-toRemove Blank Rows From Excel With PandasStrip blank rows from an Excel file with pandas: dropna(how='all'), subset and thresh, whitespace-only cells, index resets, and writing the cleaned file back.
- How-toRemove Duplicate Words and Extra Punctuation from TextTidy free-text columns from Excel with pandas: remove accidentally repeated words such as "the the" while keeping genuine repeats like "Bora Bora", collapse runs of punctuation, fix spaces before commas and full stops, normalise curly quotes and dashes, strip leading and trailing junk, and keep a log of exactly which cells changed and how.
- How-toRemove Non-Printable Characters from Excel Data with PythonFind and remove the characters you cannot see in Excel text — carriage returns, tabs, bell and other control codes, soft hyphens, zero-width marks and leftover _x000D_ escapes — using Unicode categories, without damaging line breaks or emoji you want to keep.
- How-toReorder Columns to Match a Template Layout with pandasMake every incoming file match the column layout a template, upload form or downstream system expects: read the required order from the template itself, map renamed headers, reindex so columns land in the right place, decide what happens to missing and extra columns, check the result, and write the data under the template's own header row without disturbing its formatting.
- How-toReplace Values in an Excel Column with pandasRecode values in an Excel column with pandas — replace versus map, mapping tables kept in a workbook, regex replacements, per-column rules, numeric bands with case_when, and a report of values that no rule covered.
- How-toSplit a Column into Multiple Columns with pandasThe pandas version of Excel's Text to Columns: split names, codes and addresses on a delimiter, cap the number of pieces, extract parts with a regex, and turn multi-value cells into rows.
- How-toSplit an Address Column into Street, City and PostcodeBreak single-cell addresses from an Excel export into street, city, postcode and country columns with pandas: handle line breaks typed with Alt+Enter and comma-separated addresses, find the postcode with a pattern for each country, take the city from around it, keep extra lines such as building names, and send addresses the rules cannot split to a review list instead of guessing.
- How-toSplit Delimited Cell Values into Rows with pandasTurn cells like "Red; Blue; Green" or "INV-1, INV-2" into one row per value with str.split and explode: clean up spaces and empty items, split two parallel columns together (and handle the "matching element counts" error when they disagree), keep a link to the original row, count and pivot the results, and join them back into one cell when needed.
- How-toSplit Full Names into First and Last Names with pandasA single "Name" column holds "Ada Lovelace", "Lovelace, Ada", "Dr. Grace Hopper", "Ludwig van Beethoven" and "Smith Jr., John". Split it into first and last names with rules for commas, titles, suffixes and surname particles, keep the original, and flag the names no rule should guess about.
- How-toStandardise Category Values with a Mapping Table in pandasTurn "N. America", "north america ", "NA" and "USA" into one region name using a mapping table kept in Excel, where the people who own the categories can maintain it. Normalise keys, check the table for conflicts, report unmapped values with counts and suggestions, and append new ones for review.
- How-toStandardise Country Names and Codes with PythonMap a free-typed country column — "Germany", "DE", "deutschland", "UK", "U.S.A.", "Holland", "Turkey" — to ISO 3166 codes and one official name with pycountry: exact lookups by name and code, an override table for nicknames, native names and renamed countries, cautious use of fuzzy search, a report of ambiguous and unmatched values, and summaries that finally add up per country.
- How-toStandardise Excel Column Names with pandasTurn messy Excel headers — stray spaces, line breaks, symbols, duplicates, blanks and dates as headers — into consistent snake_case names, map synonyms from different sources to one canonical schema, and check required columns before processing.
- How-toStandardise Phone Numbers in Excel Data with PythonTurn a column of phone numbers typed every possible way — spaces, brackets, (0), 00 prefixes, missing country codes, numbers mangled into floats by Excel — into one consistent format with pandas and the phonenumbers library: E.164 for systems, international or national format for people, validity and line-type checks, and a review list of numbers that cannot be trusted.
- How-toStrip Whitespace and Normalise Text Columns with pandasTrailing spaces break joins and group-bys silently. Clean Excel text columns in pandas — strip, collapse inner whitespace, non-breaking spaces, case folding and accents.
- 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-toHighlight Duplicate Values in Excel with openpyxlFlag repeated values in a worksheet: the built-in duplicateValues rule, formula rules for duplicates across columns, whole-row highlighting, and a pandas alternative.
- 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-toEXACT and Case-Sensitive Comparisons in pandasExcel compares text without regard to case — "AB-100"="ab-100" is TRUE, and COUNTIF, MATCH, XLOOKUP and UNIQUE all ignore case — while pandas compares exactly. Reproduce both behaviours: EXACT as plain equality, Excel-style matching with casefold, COUNTIF with wildcards, first-match lookups and distinct counts, and see why a migrated report counts six products where the spreadsheet counted three.
- How-toFIND, SEARCH and SUBSTITUTE Equivalents in pandasTranslate Excel's FIND, SEARCH, SUBSTITUTE and REPLACE to pandas string methods: positions that start at 0 instead of 1, -1 instead of #VALUE!, case-insensitive search, Excel wildcards, replacing only the second occurrence, and the MID(FIND()) pattern for text between delimiters.
- How-toREGEXTEST, REGEXEXTRACT and REGEXREPLACE Equivalents in pandasExcel's new regular-expression functions map neatly onto pandas' string methods: REGEXTEST to str.contains, REGEXEXTRACT's three return modes to str.extract, str.findall and capture groups, and REGEXREPLACE to str.replace — with the differences that bite: $1 versus \1 in replacements, the case-sensitivity argument, replacing only the nth match, #N/A versus NaN, and patterns that behave differently in Python.
- How-toUNIQUE, SORT and FILTER Equivalents in pandasReproduce Excel's dynamic array functions in pandas: UNIQUE with drop_duplicates, "values that appear exactly once", SORT and SORTBY with sort_values, FILTER with boolean masks and an if_empty fallback, and the chained SORT(UNIQUE(FILTER())) pattern — plus the case-sensitive sort that puts "Banana" before "apple".
- How-toParse Numbers with Units in Excel CellsTurn cells like "12 kg", "1,200 g", "2.5t", "500 ml", "3.5k" and "1,5 kg" into numbers in one unit with pandas: a regular expression that splits value from unit, a conversion table for the units you expect, suffixes such as k and M, decimal commas, the pint library for broader unit support, and a report of every value that could not be parsed.
- How-toParse Records That Span Multiple Rows in Excel with pandasBank statements wrap long descriptions onto extra rows, and contact exports stack one field per row. Number each record from the row that starts it, join the continuation lines, pivot stacked fields into columns, and catch orphan lines and repeated fields instead of silently mixing records.
- How-toRemove Subtotal and Total Rows from an Excel ExportSubtotal and grand-total rows double every sum you compute. Find them by label, by missing detail fields or by SUBTOTAL formulas, remove them, and use the totals to check your own numbers.
- How-toReplace Placeholder Text with NaN in pandasExcel sheets mark missing data with "n/a", "-", "TBC", -999 or 31/12/9999. Turn those placeholders into real missing values with read_excel's na_values, per-column rules and sentinel masks — without losing genuine values such as the country code NA.
- How-toFuzzy Match Names Between Two Excel Files with PythonJoin two spreadsheets when the names do not quite match — Acme Ltd vs ACME Limited — with rapidfuzz: normalise names, choose a scorer, set a cut-off, score every pair at once, and send doubtful matches for review.
- How-toMerge Excel Data on Multiple Key Columns with pandasMatch rows on region and product, or store and date, rather than on one ID. Merge on several columns at once, find why some rows do not match — a key typed as 7 in one file and "007" in another, a trailing space — guard against duplicate keys multiplying rows, and fall back to a less specific key when the exact combination is missing.
- How-toMerge on Keys That Differ in Case and Spacing with pandasA merge that should match every row matches half of them because one file says "SKU-001 " and the other "sku-001", or an ID has lost its leading zeros, or an invisible non-breaking space hides in a cell. Build a normalised join key on both sides, merge on it while keeping the original values, detect keys that collapse into collisions, and report what still does not match.
- How-toClean and Aggregate Excel Data with Polars ExpressionsClean a messy Excel export with Polars expressions — trim and normalise text, parse amounts typed with currency symbols and thousands separators, find the values that failed, add conditional columns, then aggregate with group_by, window shares and a pivot, and write the summary back to Excel.
- How-toClean Wide Excel Sheets with Polars Column SelectorsWide Excel sheets with a column per month, region or measure are tedious to clean column by column. Polars column selectors pick columns by type, name pattern or position and combine with set operations, so one expression can trim every text column, fill every numeric gap, total every monthly column, rename a whole block and unpivot it — and keep working when next month adds a column.
- 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-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-toConvert Dates Like 20240131, 31.01.24 and 2024031 with pandasERP and mainframe exports write dates as 20240131, 31.01.24, 31-Jan-24 or day-of-year codes, and read_excel returns them as integers or text. Convert each layout with an explicit format, handle placeholders like 00000000, invalid dates such as 20240231 and two-digit years, and report what did not convert.
- How-toConvert Text Months and Weekdays to DatesTurn the period labels people type into Excel — "Jan 2026", "September", "Sept-26", "Q3 2026", "Week 14", "FY27 P03", "Mon" — into real dates and sortable periods with pandas: explicit formats for month names, a lookup for abbreviations pandas does not know, ISO week numbers, quarters, fiscal periods with a different year start, and weekday names in order, with every unparsed label reported.
- How-toRemove Duplicate Rows Across Many Large Excel FilesDe-duplicate records spread over dozens of overlapping Excel exports without loading them all at once: stream rows file by file, fingerprint each record on normalised key fields, keep only a set of hashes in memory, write unique rows straight to an output workbook, log every duplicate with its source file and row, and choose between keeping the first or the latest copy.
- How-toTest Excel Cleaning Code with HypothesisLet Hypothesis find the inputs your Excel code breaks on: property-based tests for a text-cleaning function, a round-trip property for writing and reading workbooks that uncovers vanishing empty strings, control characters returned as _x001F_ and floats cut to 15 significant digits, DataFrame strategies for cleaning steps, and settings for CI.
- How-toRead European CSV Exports with Semicolons and Decimal CommasCSV files saved by European Excel and ERP systems use semicolons between fields, commas as decimal points, dots as thousands separators and a Windows code page. Read them into pandas correctly — and avoid the thousands option silently turning dates like 31.01.2026 into the number 31012026.
- How-toKeep Leading Zeros When Reading Excel with pandasCustomer IDs, ZIP codes and phone numbers lose their leading zeros in read_excel. Fix it for text cells with dtype, and for numbers that only look padded because of a 00000 format, by reading the format with openpyxl.
- How-toRead Yes/No and TRUE/FALSE Columns as Booleans with pandasExcel sheets store flags as TRUE/FALSE cells, "Yes"/"No" text, Y/N codes, 1/0 or a tick mark. See what read_excel does with each, why true_values quietly skips columns with blanks or odd spellings, and how to convert every flag column into a reliable nullable boolean.
- How-toUse Converters When Reading Excel with pandasClean values as they are read — account codes with leading zeros, currency text, Y/N flags and mixed date cells — with read_excel converters, and know how they interact with dtype and empty cells.
- How-toDelete Empty Rows and Columns with openpyxlRemove blank rows and columns from a worksheet in place while keeping its formatting: detect empty rows without creating cells, delete them in runs from the bottom up, handle empty columns, repair row heights, merged cells and formulas that delete_rows leaves behind, and switch to a copy-based rebuild when the sheet is large.
- How-toClean Data with pandas in Python in ExcelUse pandas inside Excel's =PY() cells to clean a messy table — trim text, fix types, drop duplicates, standardise categories — and return the cleaned table to the grid, with the calculation-order and output-mode rules that make it work.
Compare the options
Which tool or approach to pick, and why.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix Garbled Characters Like "Café" and "’" in Excel DataText that shows "Müller", "€100" or "’" was UTF-8 read as Windows-1252 somewhere upstream. Detect the affected cells, repair them with a one-line re-encode or with ftfy, avoid the repairs that damage correct text, recognise what cannot be recovered, and stop it happening again.
- FixFix the Capitalisation of Names with Exceptions in pandasTurn SHOUTED and lower-case names in an Excel list into proper names without the mistakes str.title makes: O'Brien instead of O'brien, McDonald, hyphenated surnames, particles such as van and de, generational suffixes like III, company suffixes and acronyms such as LLC and UK, leaving already mixed-case names alone, and a review list of everything that changed.
- FixFix Mixed Day-First and Month-First Dates in Excel DataA date column that combines 03/04/2026 from a UK office, 04/13/2026 from a US one, ISO dates from a system and real Excel dates cannot be parsed with one setting — and pandas' format="mixed" with dayfirst=True silently swaps both unambiguous US dates and ISO dates. Classify each value, infer each source's convention from its unambiguous rows, parse with explicit formats, and flag what stays ambiguous.
- FixFix "Could Not Convert String to Float" in Excel DataFix ValueError: could not convert string to float when converting an Excel column with pandas: find exactly which cells fail and why, then clean thousands separators, currency symbols, percentages, accounting negatives in parentheses, non-breaking spaces, Unicode minus signs, European decimal commas and placeholder text — and report what could not be converted instead of hiding it.
- FixFix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel OutputSums such as 0.1 + 0.2 print as 0.30000000000000004, prices turn into 1998 cents instead of 1999, and threshold checks fail by a hair. See where binary floating point leaks into Excel reports, what the xlsx writers already hide, and how to round, compare and convert money safely.
Follow it through a track
Prefer an ordered route? These tracks cover data cleaning 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.
Data cleaning is part of Cleaning & transforming data.