Cleaning messy spreadsheet data: a repeatable order of operations

Data arriving from a form, an export or a colleague's spreadsheet is rarely clean. The defects are predictable, and there is a sensible order for fixing them, because doing it out of order hides problems rather than solving them.

Step 1: whitespace, before anything else

Trailing spaces are invisible and break everything downstream. "Mumbai " and "Mumbai" are different strings, so they group separately, join separately and deduplicate separately.

Trim leading and trailing whitespace on every text field, and collapse internal runs of spaces to one.

Watch for the ones you cannot see: the non-breaking space (U+00A0) is extremely common in data copied from web pages or Word, and it does not match a regular space. A generic "trim whitespace" that only handles ASCII space and tab will leave it behind, and you will spend an hour wondering why two identical-looking values will not match.

Do this first, because every subsequent comparison depends on it.

Step 2: normalise casing and formats

• Case. Decide a rule per column: names in title case, emails in lowercase, country codes in uppercase. Emails in particular should be lowercased, since the domain part is case-insensitive and most providers treat the local part that way too.
• Dates. Convert everything to ISO 8601 (2026-07-25). The trap is ambiguity: 03/04/2026 is 3 April or 4 March depending on origin, and there is no way to tell from the value alone. If the source mixes conventions, you need to know the origin of each row, guessing produces plausible, wrong dates.
• Numbers. Strip thousands separators, currency symbols and percentage signs. Be careful with locales where the decimal separator is a comma: 1.234,56 and 1,234.56 are the same number written two ways.
• Phone numbers. Pick one format. Store the country code.

Step 3: handle empty values consistently

Real exports contain ``, NULL, N/A, -, n/a, #N/A, unknown and TBD, often in the same column. To a person these all mean "missing". To a tool they are six distinct values.

Map them all to one representation before doing anything else with the column.

Step 4: deduplicate, but define "duplicate" first

This is where most cleaning goes wrong. Exact-match deduplication only removes rows that are byte-identical, which will miss:

• john@example.com vs John@Example.com
• Acme Ltd vs Acme Ltd. vs ACME LIMITED
• The same person entered twice with different phone formats

Deduplicating after steps 1–3 catches far more, which is exactly why order matters.

Then decide the key. Duplicate on the whole row? On email alone? On name plus date of birth? These give different answers, and the right one depends on what the data means.

And decide which copy to keep, usually the most recently updated, or the most complete.

Step 5: validate rather than assume

Before using the data, check the assumptions you are about to rely on:

• Do all emails contain exactly one @ with something either side?
• Are all dates within a plausible range? (Birthdates in 1900 or 2087 usually mean a parsing error, not a data-entry error.)
• Do numeric columns contain only numbers?
• Do required fields have values?
• Are there rows with the wrong number of columns, a strong sign of an unescaped delimiter inside a field?

Step 6: check the structure itself

• Merged cells from Excel export as a value in the first cell and blanks in the rest. This silently destroys row alignment.
• Multiple header rows, or a title row above the headers, shift everything by one.
• Totals rows at the bottom get treated as data and pollute every aggregation.
• Blank rows used as visual separators break naive parsers.

Keep the original

Always work on a copy and keep the raw export untouched. When you discover in three weeks that a transformation was wrong, you need the source to redo it from.

If the cleaning is something you will do again, a monthly export, a recurring import, write the steps down. Cleaning that only exists as a sequence of manual actions someone once performed is cleaning that will be done differently next time.

Tooling

For one-off work, a CSV cleaner that handles trimming, casing and delimiter normalisation in a pass covers steps 1–3, duplicate removal covers step 4, and a CSV editor is useful for the structural inspection in step 6. All of it runs in the browser, which is the relevant property when the file is a customer or employee list.