Clean a spreadsheet without breaking it

Every business has the spreadsheet. Three thousand rows, six people have edited it, the same company appears four ways, half the dates are text, and everyone works around it because cleaning it is a day nobody has.

It is also exactly the kind of work that is mechanical right up until it is not.

Work on a copy, always

Not a precaution, a requirement. The first thing to do is duplicate the sheet and work on the duplicate.

Cleaning is destructive by nature, and the mistakes are the kind you only notice a week later when a number has quietly changed.

Ask what is wrong before fixing anything

Look at this sheet and tell me what is wrong with it, without changing
anything.

  which columns have inconsistent formats, and what the variants are
  which rows look like duplicates, and what makes you think so
  which values are impossible or out of range
  which columns are mostly empty

Give me counts. Show three examples of each problem rather than listing
every instance.

This is the step people skip and it is the one that saves the day. You will almost always find that what you thought was one problem is three, and that one of them is not safe to fix automatically.

Fix in passes, checking between

On the copy, do only this: normalise the date column to ISO format.

Report how many cells changed, and list any you could not interpret rather
than guessing at them. Do not touch anything else.

One transformation at a time, each reported. It is slower to describe and much faster than untangling a combined pass that went wrong somewhere in the middle.

Deduplication is the pass to do last and to review by hand. Every other fix is reversible in principle because the original values are recoverable from the untouched sheet. A merged row is a decision that two records were the same thing, and if that was wrong you have lost information rather than reformatted it. Ask for candidates, decide yourself.

Keep the report

The list of what changed is worth more than it looks. When somebody asks in three weeks why a figure moved, the alternative to that log is an afternoon.

Then fix the source

A cleaned sheet degrades immediately if the thing filling it has not changed. If the same column keeps arriving in three formats, the fix is upstream — a form, a validation rule, a conversation with whoever pastes it in.

What good looks like

A clean copy, a log of every change, and a short list of things a person had to decide.

The failure to watch for is a sheet that is now consistent and subtly wrong: a date interpreted as US format that was European, a duplicate merged that was two real customers. That is why the passes are separate and the dedupe is manual.