Cleaning a messy spreadsheet in the order that saves the most work.

A spreadsheet that was ever printed or emailed usually has a title block: a report name, a date range, sometimes a logo, sitting in the first few rows above the grid. Anything reading the file takes the first row as the header unless told otherwise, so the columns come out named after fragments of a title and the data starts three rows late.

The tell is a header made of things that are not column names — a company name in column A and nothing in the rest — or an analysis that comes back nearly empty because every row failed to parse against the wrong header. Delete the rows above the real header, or point the import dialogue at it.

Subtotal rows stranded mid-table are the reason two people get different totals from the same file: every value under them is counted twice, once as itself and once inside its subtotal. Blank spacer rows and a grand-total line at the bottom are the same class of problem — rows that describe the data, sitting inside the data.

Remove them before computing anything, and keep the rule you used. A row whose value equals the sum of the rows immediately above it is a subtotal, not an observation.

Numbers stored as text are silently skipped by most sums, so the total is not wrong so much as incomplete. Currency symbols inside the cell, thousands separators from another locale, and trailing spaces are the usual causes, and left-aligned values in a numeric column are the visual tell.

Dates deserve their own pass, because a column mixing day-first and month-first formats cannot be repaired with one format string. Below the thirteenth of the month the two are indistinguishable, and the unambiguous rows are the evidence for what the rest mean.

Codes are the opposite problem: a postcode or product code read as a number loses its leading zeros, and the damage only shows up later, when a lookup against another list quietly matches nothing.

Run the duplicate check after the types are fixed, not before. "1,240.00" stored as text and 1240 stored as a number are the same value and will never match each other, so a dedupe over unrepaired columns misses real duplicates and flags spurious ones.

Then match on identity rather than on the whole row: an email lowercased and trimmed, or a name together with an address. Real duplicates disagree in at least one field, which is exactly why they survived.

Every one of these steps changes the answer, so each belongs in the write-up: how many rows were dropped as titles or subtotals, how many cells were converted from text, how many dates could not be resolved, how many records merged.

That list is also what makes the cleaning repeatable next month. A file cleaned by hand is clean once; a file cleaned by a recorded sequence of repairs is clean every time it arrives.