How to Clean Messy Spreadsheet Data: A 9-Step Checklist

Cleaning data has an order of operations, and most of the frustration comes from ignoring it. Remove duplicates before you trim whitespace and "Acme Corp" and "Acme Corp " both survive, so you get the satisfying feeling of having deduplicated with none of the benefit. Fix dates after you've sorted and the sort meant nothing.

Work through the passes below in sequence and each one makes the next easier. The whole thing takes about twenty minutes on a typical export, and you only have to do it once if you keep the checks at the end.

Step 1: Look before you touch

Ten minutes of looking saves an hour of fixing. For each column you want to know four things: how many rows have a value, how many distinct values there are, what type the values actually are, and what the extremes look like.

• =COUNTA(B:B) against =COUNT(B:B). A big gap in a numeric column means text pretending to be numbers.
• =COUNTA(UNIQUE(B2:B5000)). A country column with 340 distinct values has a problem.
• =MIN() and =MAX(). A date range starting in 1899 means a blank got read as a date.
• Sort ascending, then descending, and read the top ten each time. Rubbish lives at the edges.

Step 2: Fix the shape

Before touching values, sort out the structure. What you want is one header row, one record per row, one value per cell, no blank spacer rows, and no totals sitting in the middle of the data.

1. 1Unmerge every merged cell. They break sorting, filtering and most formulas.
2. 2Collapse multi-row headers into one row of unique names.
3. 3Delete blank spacer rows and columns.
4. 4Move subtotals and totals out of the data range, or they'll double-count everything downstream.
5. 5Where a value appears once and applies to the rows beneath it, fill it down.

Step 3: Strip whitespace and invisible characters

Best value for effort in the whole list, because the problem is invisible. TRIM removes leading, trailing and repeated internal spaces. CLEAN removes non-printable characters. Neither removes the non-breaking space that comes with anything pasted from a web page or a PDF, which is why this catches people out.

Step 4: Fix numbers stored as text

A number stored as text left-aligns, gets ignored by SUM, and won't match in a lookup against a real number. The usual culprits are currency symbols, thousands separators, trailing spaces, a leading apostrophe, or European decimal commas.

Step 5: Repair the dates

Dates break in two directions. Some are text that looks like a date. Some are real dates parsed with the wrong locale, so 03/04/2026 is either the third of April or the fourth of March depending on where the file came from, and the file won't tell you which.

• Real dates right-align. Text left-aligns. That's the two-second check.
• =ISNUMBER(A2) returns TRUE for real dates, since they're just formatted numbers underneath.
• If day and month might be swapped, look for any row where the day is above 12. If there aren't any, the data can't tell you, and you need to ask whoever exported it.
• =DATEVALUE() converts text dates. Say which order the source uses when it's ambiguous.

Step 6: Standardise the categories

With whitespace and case sorted, what's left is real variation. Ltd against Limited, NY against New York, enterprise against Enterprise Plan. There are two ways to handle it and you want both.

Step 7: Decide what blanks mean

A blank means one of three quite different things, and treating them all as zero will quietly distort every average you calculate afterwards.

Step 8: Deduplicate, last

Now that whitespace is gone, case is normalised and categories are consistent, deduplication will actually find the duplicates you have.

Decide what makes two rows duplicates before you delete anything. Identical across every column is easy. "Same customer, different email" is a business rule and somebody has to state it. Build the key in its own column, count with COUNTIF, look at everything above one, then remove. In that order. Don't start with the Remove Duplicates button on a whole sheet, because it deletes without ever showing you what it took.

Step 9: Prove it worked

Last pass is a set of questions the clean data should answer correctly. Write them as formulas so they run again next month.

1. 1Row count before and after. Does the difference match the duplicates you meant to remove?
2. 2Column totals before and after. For anything you didn't intend to change, they must be identical.
3. 3Range checks. Any negative quantities, dates in the future, percentages over 100?
4. 4Referential checks: every customer ID in the orders tab exists in the customers tab?
5. 5Distinct counts. Is each category column now inside the vocabulary you defined?

Where AI actually helps here

Steps 1, 6 and 9 are where an assistant earns its keep. Profiling forty columns at once, proposing mappings for a long tail of category variants, and drafting the validation checks you'd otherwise skip. Steps 4, 5 and 8 are rule-based, so it can write the formulas, but the rules should be yours. Only you know whether a blank means zero.