Cleaning a spreadsheet somebody else built

The part of data work nobody puts on a slide

Before any analysis there is a spreadsheet somebody else built, over four years, with the rules in their head. Dates in three formats. "N/A", "n/a", "-" and blank all meaning missing, except where blank means zero. The same supplier spelled six ways. Amounts stored as text because of a stray space. A merged cell in row 1 that breaks every formula below it.

This is most of the job, it is where results are silently won and lost, and it is a genuinely good use of AI — provided you keep one rule.

The rule

Never clean in place. Every fix goes in a new column beside the original, and the original is not touched until you have checked the new column.

This single habit is the difference between a recoverable afternoon and an unrecoverable one. It also makes checking possible: the two columns sit side by side and you can scroll them, which is faster than any verification you could design.

What to ask for, and how

Describe your actual layout — the sheet name, the column letters, what is in them, and a few real example values including the awkward ones. Then ask for a formula or a script, not for cleaned data.

Column C has dates entered as 3/4/24, 03-Apr-2024, 2024/04/03 and sometimes blank. In Google Sheets, give me a formula for column D that converts C to a proper date value and returns blank if C is blank or unparseable. Explain what it does with 3/4/24.

That last clause is the important one. 3/4/24 is 3 April in most of the world and 4 March in the United States, and no tool can resolve that from the data. Making it state its assumption forces the ambiguity into the open, where you can decide it.

The same pattern works for the whole standard list: trimming spaces and non-breaking spaces, stripping currency symbols, splitting one name column into two, normalising phone numbers, standardising categories, flagging impossible dates, and finding rows where the total does not equal the sum of its parts.

Names and near-duplicates, the hard one

"Kumar Traders", "Kumar Traders Pvt Ltd", "KUMAR TRADERS", "Kumar Trading" — the first three are almost certainly one supplier and the fourth may be a different company entirely. Fuzzy matching will merge all four if you let it, and the merge is invisible in every number downstream.

Handle it as a two-stage job. Ask for a proposed mapping in two columns — original name and suggested standard name — and never a direct replacement. Then read the proposals. Sort them so the merges group together and you can scan a few hundred in ten minutes. The ones to look at hardest are the pairs that differ by a single word, because that word is often "Ltd", which is harmless, or a place name, which is not.

OpenRefine is free, open source and better at this than any chatbot. It clusters similar values, shows you each cluster, and lets you accept or reject them one at a time. If you clean data more than occasionally, an hour learning it repays itself immediately.

The checks that catch real errors

Do these every time. They take five minutes and they have saved people from published mistakes.

• Row count before and after. If it changed and you did not intend it to, stop. Deduplication that removes 40% of rows has usually matched on the wrong column.
• Sum a numeric column before and after. Cleaning should not change a total unless you meant it to.
• Count the blanks per column. A column that gained blanks lost data; a column that lost blanks gained assumptions.
• Sort each column and look at the top and bottom twenty rows. Impossible dates, negative quantities, a price of 999999, an "Unknown" that is really 300 rows. This crude look finds more real problems than any clever method.
• Check the awkward cases by hand. The blank, the zero, the duplicate, the row with the apostrophe in the name, the one date sitting exactly on a boundary.

The parts to keep for yourself

Two decisions are yours and no tool should make them.

What missing means. Blank might be zero, or not applicable, or nobody filled it in, and those three lead to different totals and different conclusions. Only somebody who knows how the sheet was filled in can say, and often that means asking a colleague rather than a model.

Which rows to exclude. Dropping outliers, removing test records, excluding a branch that changed its recording method — these are analytical judgments that change the answer. Make them explicitly, write down what you did and why, and keep the excluded rows in a separate tab. A cleaning step nobody recorded is the most common reason two people produce different numbers from the same file.

Keep the recipe

Whatever you did, save it: the formulas, the script, the order of operations, the decisions. Next month the file arrives again, just as messy. The person who kept the recipe spends ten minutes; the person who did not spends the afternoon again and gets slightly different answers.