How to automate spreadsheet data cleaning without destroying evidence

Cleaning a spreadsheet by hand is tedious and error-prone, which makes it an obvious thing to automate. The part that is less obvious is that every cleaning rule is a claim about what the data means, and a rule applied automatically applies that claim to rows nobody looked at.

So the design question is not how to normalise a phone number. It is how to do that while keeping the evidence of what was there before, and how to notice when a rule is doing something you did not intend.

What we'll cover

• Cleaning is interpretation
• Keep the original, always
• The rules worth encoding first
• Building it with CodeWords
• Deduplication, which deserves caution
• Reporting what changed
• When the fix belongs upstream
• Cleaning data about people
• Making it survive
• Limits worth knowing about
• What to build first
• Frequently asked questions

Cleaning is interpretation

Trimming whitespace is safe. Almost nothing else is.

"Normalising" a date assumes a format, and the classic failure is reading a day as a month — which produces a plausible wrong date rather than an error, in about a third of cases.

Standardising a company name assumes two spellings are the same company, and occasionally they are two companies.

Filling a blank with a default asserts that absent means that default, and blank usually means "unknown" rather than zero.

Correcting a spelling assumes you know what was intended.

None of these is wrong to do. Each is a decision that should be explicit, reversible, and reported — rather than a step buried in a cleaning script nobody reads.

Keep the original, always

The single practice that makes everything else recoverable.

Write cleaned values to new columns rather than overwriting, at least while the rules are new.

Or keep a copy of the input, timestamped, before anything runs.

Because you will get a rule wrong, and without the original the only recovery is re-exporting from the source — which may not still have it.

It also makes the rules auditable. Somebody questioning a figure can see what the value was and what it became.

Drop the originals later if you must, deliberately, once the rules have been stable for a while — rather than never having had them.

This costs a column and saves a bad afternoon, which is a good trade in every case.

The rules worth encoding first

In rough order of how safe and how valuable they are.

Trim whitespace and normalise case for anything you match or group on. Safe, and it fixes a surprising share of duplicate problems.

Normalise phone numbers to a consistent international format, which makes matching possible and is where most contact deduplication fails.

Parse dates with an explicit expected format, and reject rather than guess when it does not match. This is the rule that most needs an explicit format.

Standardise a fixed vocabulary — country, status, category — against a list, flagging anything not on it rather than mapping it to the nearest.

Strip formatting from numbers — currency symbols, thousands separators — and record the currency separately rather than discarding it.

Flag rather than fix anything you are not certain about, which is most things.

Building it with CodeWords

CodeWords connects to more than 3,000 integrations, so the cleaning can run where the data arrives rather than as a step somebody remembers to perform.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which sheet, which columns, and what each rule should do.
3. Authorize the connection to the spreadsheet and to wherever the report should go.
4. Describe the exceptions: a value that does not parse, a duplicate, a value outside a plausible range.
5. Run it against a copy and compare the before and after before pointing it at anything live.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

Deduplication, which deserves caution

The most requested cleaning operation and the most destructive one.

Normalise before comparing, or you compare two strings differing by a full stop and conclude they are different companies.

Match on more than one field. Email alone misses the same person with two addresses; name alone matches two different people.

Report duplicates rather than merging, at least initially. A list somebody spends ten minutes on is safer than an automatic merge you cannot unwind.

Decide which row wins explicitly when you do merge — most recent, most complete, or from the most trusted source — and record the decision.

Keep what the losing row had that the winner did not, rather than discarding it.

Near-duplicates are sometimes real. Two branches of the same company, two people sharing a family email, two genuinely similar product names — and only a person can tell.

Reporting what changed

Count changes per rule. A rule that fired on four rows and a rule that fired on four thousand need different levels of confidence.

Report the unusual. A rule suddenly firing far more than usual means the input changed, which is the thing you want to know.

List what was rejected, with the reason, since that list is the actual data quality report and is more useful than the cleaned output.

Show examples, not just counts. Five sample transformations per rule let somebody sanity-check it in seconds.

Send it to whoever owns the data, not to whoever built the automation, because the owner is the one who can tell a correct transformation from a plausible one.

When the fix belongs upstream

The honest section, since cleaning is frequently treating a symptom.

If the same problem arrives every week, the fix is at the source — a form validation, a dropdown instead of free text, a required field.

Cleaning makes the upstream problem invisible, which is why it persists. A weekly report of what had to be cleaned is the argument for fixing it.

A spreadsheet that needs cleaning to be usable may be the wrong tool. Constraints exist in databases and in structured form tools, and neither allows the states you are cleaning up.

Some cleaning is permanent and fine — data from a third party you do not control, historical records, a one-off migration.

Say which situation you are in. Building an elaborate cleaning pipeline for a problem that a required field would eliminate is a common and avoidable investment.

Cleaning data about people

A category that carries obligations the rest does not, and it is worth separating.

Deduplicating people is not the same as deduplicating products. Merging two records asserts that two people are one, and getting that wrong merges two individuals' data.

Corrections are visible to the person. Somebody whose name you "normalised" incorrectly will notice, and names do not follow the rules a cleaning rule assumes.

Do not discard what somebody entered about themselves in favour of a standardised form. Their spelling of their own name is correct by definition.

Keep the audit. In several jurisdictions people can ask what you hold about them and where it came from, which is difficult to answer if the original was overwritten.

Deletion has to reach the copies. A cleaning pipeline that writes to three sheets has created three places a deletion request must reach.

Raise it early. Whether your cleaning constitutes processing that needs a basis is a question for whoever advises you, and it is cheaper to ask before the pipeline exists.

Making it survive

Make it idempotent. Running the cleaning twice should produce the same result, which means rules that are safe to reapply to already-cleaned values.

Handle the new column. A sheet somebody added a column to should not break the automation or silently skip the new data.

Do not reorder or sort. A cleaning automation that changes row order breaks anything referencing rows by position.

Fail on the unexpected rather than proceeding. A column that has vanished should stop the run, not produce a cleaned sheet missing a field.

Report the outcome. Rows processed, changes per rule, rejections, and anything skipped.

Limits worth knowing about

Spreadsheets have a cell limit, and keeping original columns alongside cleaned ones consumes it faster.

Type coercion is aggressive. A spreadsheet will helpfully reinterpret what looks like a date or a number, which is the cause of a good share of the corruption you are cleaning.

Long identifiers lose precision when stored as numbers, so they belong in text columns before any cleaning happens.

Concurrent edits are possible. Somebody editing the sheet while the automation runs produces results neither of you expects.

API write limits apply, so a large cleaning run needs batching rather than cell-by-cell updates.

What to build first

Trim and normalise the columns you match or group on — whitespace, case, phone format — writing to new columns, with a report of what changed and how often. It is the safest set of rules, it fixes a large share of the downstream matching problems, and the report tells you what to encode next.

Two habits make the difference. Keep the original values, at least while the rules are new, because you will get one wrong and re-exporting from the source is not always possible. And flag rather than fix anything you are not certain about — a flagged row gets looked at, and a confidently wrong correction does not.

Frequently asked questions

What cleaning rules are safe to apply automatically?

Trimming whitespace and normalising case. Almost everything else encodes an assumption — a date format, that two spellings are the same company, that blank means zero — and should be flagged or reported rather than applied silently.

Why do dates get corrupted?

Because a format was assumed. Day and month swap silently for roughly a third of dates, producing a plausible wrong value rather than an error. Parse with an explicit expected format and reject what does not match.

Should duplicates be merged automatically?

Report them first. Merging is destructive and near-duplicates are sometimes genuinely different — two branches, two people sharing an address. When you do merge, decide explicitly which row wins and keep what the loser had.

Do I need to keep the original values?

While the rules are new, yes — you will get one wrong, and re-exporting from the source is not always possible. Drop them later as a deliberate decision rather than never having had them.

What should the report contain?

Changes per rule with example transformations, and the list of rejected rows with reasons. The rejection list is the actual data quality report and is more useful than the cleaned output.

When should I fix the source instead?

When the same problem arrives every week. Cleaning makes the upstream issue invisible, which is why it persists — and a form validation or a dropdown usually eliminates what an elaborate pipeline is treating.

Why did the automation break after somebody edited the sheet?

Probably a renamed, added, or reordered column. Fail on the unexpected rather than proceeding, since a cleaned sheet missing a field is worse than a run that stopped and said why.

Is cleaning personal data different?

Yes. Merging two people's records is consequential in a way merging product rows is not, names do not follow the rules a normalisation assumes, and in several jurisdictions people can ask what you hold and where it came from — which needs the original.

What does a cleaning pipeline do to deletion requests?

It multiplies them. Every sheet the pipeline writes to is another place a deletion has to reach, which is worth knowing before the pipeline has three outputs rather than after.

Related reading

• Connecting MongoDB to Google Sheets
• Automated reporting for agencies
• ETL pipelines explained