Cleaning Messy Spreadsheet Data

The most insidious flaw is invisible: leading and trailing spaces. " Alice" and "Alice" look identical on screen but are different strings to a computer, which quietly breaks grouping, lookups and joins. A value with a trailing space will fail to match its twin, inflating your distinct counts and scattering what should be one category into two. Trimming whitespace from every cell is almost always safe and should be the first step in any cleanup.

Not every space is the ordinary one. Text pasted from a web page often carries a non-breaking space (U+00A0), which looks identical on screen but is a different character entirely; copying out of a PDF or a chat window can leave zero-width characters that are invisible even in an editor; and a tab inside a value causes the same silent mismatches. That is why a value that looks the same can still refuse to match after a plain trim — those characters have to be normalised to an ordinary space or removed on their own.

Duplicates creep in whenever data is merged or a process runs twice. Identical rows double-count revenue, distort averages and inflate totals. Removing exact duplicate rows keeps the first occurrence of each and drops the rest — but do it after trimming, so that rows differing only by a stray space are correctly recognised as the same. Empty rows are the other common noise: blank lines left by an export or a manual delete. They add nothing and confuse tools that treat every line as a record, so clearing them makes the file both smaller and safer to process.

Decide what a duplicate is before you delete anything. Rows where every field matches are safe to collapse. Rows that repeat only the key — the same customer id twice with two different phone numbers — are a different problem: one of them is probably a correction, and dropping whichever comes second loses information. When later rows are updates, keep the last occurrence rather than the first. Whichever rule you choose, note the row count before and after: a deletion you cannot explain is a deletion you should not trust.

Problems in the header poison everything downstream, and they are easy to miss because you read past them. The usual set: a report title or an export timestamp sitting on line one above the real header; a BOM glued to the first column name; trailing spaces in names; the same name used twice; names that differ only in case, such as Email and email; and a header repeated in the middle of a paginated export. Any one of them makes a column impossible to address reliably by name.

Sort the header out once, before the values. Cut the rows above the real header, strip whitespace from the names, settle on one case convention, and make every name unique. Do it before you join or compare files, because both of those operations match on column names, and to every tool in existence a column called "Email " and a column called "email" are two unrelated columns that happen to look alike.

Empty is not always empty. Exports write N/A, NULL, null, none, a single dash or #N/A to mean the same thing, and the reader treats them inconsistently: pandas counts several of those strings as missing by default while leaving a bare dash as ordinary text. The result is a column where half the gaps are real nulls and half are four-character strings, and every count you take of it is wrong. Choose one representation — usually a genuinely empty field — and convert the sentinels to it before you count anything.

Formats hide the same kind of trap. Mixed dates in one column — 01/02/2026 is the first of February or the second of January depending on who exported it — are fixed by converting to ISO 8601, 2026-02-01, which is unambiguous and sorts correctly even while it is still text. Numbers carrying thousands separators, a currency symbol or a trailing percent sign are text until those are stripped. And a column of identifiers that has been through a spreadsheet often arrives as 123.0, the visible trace of a trip through a floating-point number.

Exports often include columns that are entirely blank — placeholders for fields nobody filled in. They widen the file and clutter every downstream view for no benefit. Removing columns that contain no data at all tidies the table without losing anything real. Because this step is more aggressive than the others, keep it optional and review the preview afterwards to confirm you only lost genuinely empty columns.

In the Cleaner, tick the operations you want, run it once, and read the stats: they show how many rows you started with, how many duplicates and empty rows were found, and the final count. When it looks right, download the cleaned CSV and carry on with confidence.