Cleaning messy form data in a spreadsheet

Before any technique, one principle. Do the cleaning in new columns beside the originals rather than overwriting what arrived.

The reason is that every cleaning step encodes an assumption, and some assumptions will be wrong. A name you title-cased was McDonald. A number you normalised had a country code you guessed. A duplicate you merged was two different people at the same company.

If the original is intact, a wrong assumption costs you a formula edit. If it is not, it costs you the data.

This is also what makes cleaning safe to automate. A formula in a working column can be changed and recalculated; an overwrite cannot be undone once the version history has aged out.

Leading and trailing spaces are the most common defect in form data and the hardest to see. They arrive from copy and paste, from autofill, and from people who hit space before tabbing to the next field.

They break exact matching silently. A lookup returns nothing, a filter misses rows, two identical-looking values sort separately, and nothing anywhere reports an error.

Trimming is the single highest-value cleaning step and should be applied to essentially every text column. Non-breaking spaces are worth handling too, since they survive an ordinary trim and come from pasted content.

If you do only one thing to a column of form data, trim it.

Email addresses are conventionally treated as case-insensitive in the part that matters for matching, so lowercasing a working copy for deduplication is safe and useful.

Names are a different matter. Automatic title casing produces Mcdonald, Van Der Berg and O'brien, all of which are wrong and all of which a person notices immediately when they receive an email addressed that way.

The rule is to normalise casing for matching and leave the original for display. Two columns, two purposes.

Company names sit in between. Lowercase for comparison, but never send correspondence using the normalised version.

If your submission timestamps are inconsistent, the cause is almost always how the value was written rather than how people typed it.

A date arriving as text may be interpreted by locale, and the ambiguity between day-first and month-first ordering is silent for the first twelve days of every month and wrong thereafter. The safest storage format is the international one, year first, which sorts correctly as text and cannot be misread.

Watch for timezone confusion too. A timestamp recorded in one zone and read in another produces submissions that appear to arrive before they were sent, which is a genuinely disorienting thing to see in a lead report.

Establish which zone your pipeline records in and note it in the column heading. It costs nothing and saves an argument.

Phone numbers, postcodes, reference codes and anything with a leading zero are text, not numbers, and a spreadsheet will helpfully convert them if given the chance.

The damage happens at write time and is not reversible by formatting afterwards, which is the detail that catches people out. A number that lost its leading zero cannot be repaired from the sheet.

Check a sample of any such column for values in scientific notation or missing leading digits. If you find them, the intake needs fixing before any cleanup is worth doing.

The same applies to values beginning with an equals or plus sign, which a spreadsheet may interpret as a formula rather than text.

A cell can contain an empty string, a space, the word null, a hyphen, or the letters N slash A, and none of those are the same as empty to a formula.

Form pipelines produce these when a field is present in the payload but has no value, which is different from the field being absent. Both are common.

Normalise them to genuinely empty in your working columns, then count them. A column that is ninety percent blank is telling you something about the form rather than about your customers, and it is usually the same message as an unused field.

Exact duplicates are easy. The interesting cases are the near ones: the same person with a trailing space, a different capitalisation, or a personal address on one submission and a work address on the next.

Trim and lowercase into working columns first, because that alone collapses most apparent duplicates. What remains needs judgement.

Resist automatic merging on fuzzy similarity. Two people at the same company with similar names is a real situation, and a merge you cannot see is a mistake you will never find. Flag candidates for review instead, which is the same reasoning as flagging rather than discarding repeats.

Message bodies cannot be cleaned in any meaningful sense, but they can be made workable.

Very long messages break row height and make the sheet unusable, so a truncated preview column beside the full text keeps the sheet scannable while preserving everything. Line breaks inside a cell cause similar trouble and can be collapsed in the working copy.

And it is worth extracting the one or two things you always look for, such as a phone number mentioned in the body or a deadline, into their own columns as you read.

That is a human step, and it is faster than any parsing you could write for the volumes most businesses handle.

Every recurring cleaning step is a defect in the intake wearing a disguise.

Trimming every week means the form or the pipeline is not trimming. Constant date repair means the write format is wrong. Repeated deduplication means the submission path allows repeats. Splitting one field into two every month means the form should have had two fields.

Fix those and the cleaning stops. Clean the history afterwards, once, when it will stay clean.

The order matters: cleaning a column that is still being fed badly is a chore you have volunteered for indefinitely.

Original columns exactly as submitted, never edited. Working columns beside them with trimmed and normalised values, driven by formulas so they update themselves. Human-added columns for status, category and notes, which is where your judgement lives.

Reports and lookups read the working columns. Correspondence uses the originals. Nothing is ever overwritten.

It takes half an hour to set up and it survives everything you subsequently do to the sheet.

Protect the original columns if your spreadsheet supports it, so that a well-meaning colleague cannot type over an arriving value. It is the same instinct as giving most people view access: the sheet is a record first and a workspace second.

Trim everything. Normalise for matching, keep originals for display. Store dates unambiguously and text as text. Flag near-duplicates rather than merging them. Extract what you always read.

Then look at which of those steps you are repeating, and fix that upstream instead. The goal is not a clean sheet, it is a sheet that arrives clean.

Most of the way there is achievable in an afternoon, and the remaining mess is human variation rather than damage, which is the mess worth keeping. People write their names and their situations differently, and a pipeline that flattened all of that would be destroying information rather than tidying it.

Should I clean data in the original columns?

What is the single most valuable cleaning step?

Why did my numbers lose their leading zeros?

Should I automatically merge similar records?

How should dates be stored?

What about empty values that are not empty?

Can I automate all of this?

How do I stop having to clean every month?