Cleaning Messy Spreadsheet Data with AI: What to Automate and What to Check Yourself

By the XLsheetAI Team · Updated August 15, 2026 · 10 min read

The one-sentence answer

Hand AI the cleaning jobs that have exactly one correct output for a given input — trimming whitespace, replacing a character, splitting on a delimiter, mapping a fixed list of labels — and keep for yourself the ones that require knowing something the data does not contain, because a language model responds to a missing fact by generating a convincing substitute rather than by admitting the gap. That single distinction, deterministic versus judgement, sorts nearly every data-cleaning task you will meet.

The rest of this article works through both sides of the line, then covers the verification habits that make the safe side genuinely safe.

Why the distinction is about mechanism, not model quality

This is not an argument that AI is unreliable and will improve. It is an argument about what the two modes of use actually produce.

When you ask for a formula, the output is a rule. You can read it, you can see what it does and does not touch, Excel applies it identically to row 4 and row 40,000, and if it is wrong it is wrong everywhere in the same visible way. Wrong rules announce themselves: a column of #VALUE! errors, or names that are obviously chopped in the wrong place.

When you paste 2,000 rows into a chat window and ask for them back cleaned, the output is text that resembles your data. There is no rule applied consistently, no mechanism that guarantees row 1,317 came back with the same digits it went in with, and no error state. Wrong values look exactly like right values. That is the entire risk, and it does not go away with a better model, because the failure is silent by construction rather than by accident.

Where AI genuinely earns its place

Writing the text formula you half-remember

This is the highest-value use and the lowest-risk one. Everyone knows TRIM exists; far fewer people remember that TRIM does not remove the non-breaking space (character 160) that arrives with every web copy-paste, which is why a cell that looks trimmed still fails a lookup.

=TRIM(A2)

=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), CHAR(9), " "))

Describe the symptom — "my VLOOKUP fails even though the two cells look identical" — and you get the second formula plus the reason. The formula is short enough to read, so you are not trusting the model, you are reviewing its work. That is the ideal shape of the interaction.

Splitting an inconsistently formatted column

A name column containing "SMITH, John", "john smith", "Dr. John Smith" and "Smith John" defeats Text to Columns because the delimiter and the order both vary. Describing the variants and asking for a formula gets you a nested conditional that handles each case explicitly. You will still find a fifth variant it did not cover — but you will find it in a visible #VALUE!, not in a silently mangled record.

Addresses behave the same way. Ask for the rule, apply it, then filter for rows where the output column is blank or errored and handle those by hand. That residue is usually small, and it is the honest part of the job.

Standardising labels against a list you supply

Turning "USA", "U.S.A.", "United States" and "us" into one value is safe when you supply the target list. You are asking the model to build a mapping table between things you have both seen, not to decide what the categories should be. Have it produce the lookup table, eyeball it, then apply it with a lookup formula so the mapping stays visible in the workbook.

The unsafe version of the same task is asking it to "tidy up the category column" with no list. Then it is inventing taxonomy, and it will happily fold two categories your business treats as distinct into one.

Generating and explaining a regex

Regex is the strongest case of all, because the output is pure rule and testable in seconds. Ask for a pattern that extracts an invoice number, paste it into a formula, and test it against ten known strings. Either it matches or it does not; there is no room for a plausible-but-wrong result to hide.

Describing what a column actually contains

Underrated. Paste a schema, or a sample of ten anonymised rows, and ask what the column probably represents, what formats appear in it and what would break a naive parse. This is analysis rather than transformation, so nothing is at stake if it is partly wrong, and it frequently surfaces a format variant you had not noticed.

Where it gets quietly dangerous

Every item here shares one property: the model cannot know the right answer, but nothing in its design lets it return "I do not know" for row 4,102 while returning a value for the rest.

Filling in blanks

An empty cell is information. It means the value was never captured, and downstream that distinction matters — AVERAGE skips blanks and includes zeros, and a filled-in guess becomes an observation the moment it is saved. Ask AI to fill gaps and you convert absence into fabricated presence, invisibly. If gaps must be filled, fill them with an explicit rule you chose (last known value, group median, a literal "Unknown") applied in a formula, so the choice is documented.

Inferring a value from another column

"Guess the country from the city" is the classic. It is right for Oslo and wrong for Springfield, Cambridge, San Jose and every other name that exists in several countries, and it is wrong without any signal that this row was harder than the last. Geographic, industry and currency inference all fail this way. Use a real reference table and a lookup that returns #N/A when the city is not in it, so unresolved rows stay visible.

Normalising identifiers

IDs, SKUs, account numbers and postcodes are exactly the strings you must not let a model touch, because they are arbitrary by design. A model that has seen millions of formatted identifiers has a strong prior about what one "should" look like, and a mangled-looking ID may be perfectly valid in your system. Reformatting is fine as a deterministic formula — pad to eight characters, strip hyphens — but only as a rule you specified.

Deduplicating near-matches

Deciding that "Acme Ltd" and "Acme Limited" are one customer is a business judgement with consequences: merged records, misattributed revenue, a deleted row nobody can recover. Exact duplicates are a solved problem you should handle with Excel's own tools — see how to remove duplicates in Excel — and near-duplicates should stay a human decision, with AI limited to building the normalised match key that groups the candidates.

Correcting values that look wrong

The most insidious one. Ask for cleaned data and you may get "corrections" you never requested: a date reformatted from your regional convention into another, a price with a decimal point moved because the original looked implausible, a spelling fixed on a proper noun that was correct. None of these are announced.

The task-by-task table

The verification habits

These take about two minutes and they are what separates a cleaning step you can defend from one you merely hope is right.

1. Never overwrite the raw column

Insert a new column, put the formula there, and leave the original untouched until the whole job is finished and checked. Work on a copy of the file too. This single habit means every mistake below is recoverable rather than permanent.

2. Reconcile the row and value counts

Before and after should agree. COUNTA counts non-empty cells; COUNT counts only numeric ones. Run both on the source and the result:

=COUNTA(A2:A5000)          (source: cells with anything in them)
=COUNTA(D2:D5000)          (result: must match, or you lost values)

=COUNT(D2:D5000)           (result: cells Excel sees as numbers)
=COUNTA(D2:D5000)-COUNT(D2:D5000)   (how many did not convert)

If you were converting text to numbers, that last figure should be zero. If it is 63, then 63 rows are still text and will be skipped by every SUM and every pivot table downstream. This is the check that catches the largest class of silent failures.

3. Use LEN to see what actually changed

LEN is the cheapest audit in Excel. It tells you how much a transformation removed, which is a good proxy for whether it did what you intended:

=LEN(A2)-LEN(D2)

Expect small positive numbers when you are trimming. A row showing 40 means the transformation ate most of the value — worth opening. A column of zeros means your formula did nothing at all, which happens more often than people expect when the invisible character is not the one you targeted.

To count the affected rows in one cell before you commit:

=SUMPRODUCT(--(LEN(A2:A5000)<>LEN(TRIM(A2:A5000))))

If that returns 4 on a 5,000-row file, you have a four-row problem, not a whitespace epidemic, and you can fix those four by hand.

4. Check the distinct count, not just the total

After standardising labels, count unique values. Going from 40 spellings to 12 categories is the intended result. Going to 9 means the rule merged categories you needed kept apart, and that is far easier to spot now than in next quarter's report.

5. Read the twenty ugliest rows

Sort the result column ascending and descending and read the extremes at both ends. Rules break at the edges — the longest value, the shortest, the one with an apostrophe, the one already clean. Twenty rows of your own eyes catch things no count check will.

None of this is specific to AI, incidentally. It is the same discipline that catches the ordinary manual errors described in costly spreadsheet errors; AI simply raises the stakes by making it easy to transform 50,000 rows in one action.

A note on pasting company data into a chatbot

Before any of the above, there is a question of whether the data should leave the building. Consumer AI products may retain conversations, use them to improve models, or make them available for human review, and the settings that govern this differ between free and enterprise tiers and change over time. Customer records, employee data, salary tables, unreleased financials and anything covered by a client contract are usually not yours to paste, regardless of how careful the vendor is.

The workaround costs nothing and often produces a better answer. Describe the structure rather than the contents: "a column of UK postcodes where about a fifth have no space and some are lowercase". Or paste three rows with the values replaced by realistic fakes. You get the same formula, and it runs against the real file on your own machine. Since the safe pattern is to ask for a rule rather than for cleaned values, the privacy-preserving workflow and the accuracy-preserving workflow turn out to be the same workflow.

Stuck on a cleaning formula? Describe the mess in plain English in XLsheetAI and get the TRIM, SUBSTITUTE, TEXTSPLIT or regex you need, with an explanation of what it does — so you can check the rule before you run it on 50,000 rows.

Bottom line

AI has made the annoying half of data cleaning much faster: remembering which function strips which character, writing the nested conditional that handles four name formats, producing a regex on demand. It has not made the careful half optional. The test before you delegate any step is a single question — is there exactly one correct output for a given input, and can it be expressed as a rule? If yes, ask for the rule and run it yourself. If no, the model does not know the answer either, and the difference between the two situations is that only one of them tells you so.