How to use AI to clean up a spreadsheet: 2 prompts, 4 tools

Which AI tool should clean your spreadsheet?

Copilot Clean Data fixes only three kinds of problem, and mixed dates and duplicate rows are not among them, so the problem in the sheet narrows the choice as much as its size. Four ready-made tools and two chat routes, compiled from the vendors' own help pages as checked on 26 September 2026, with the dedicated AI cleaners after the table:

Microsoft says Clean Data "works best with tabular data of up to 100 columns and 50,000 rows" (Clean Data in Excel), and the same page lists exactly the three suggestion types in the table. "Last, First" name splits are missing too, so a sheet with those problems still needs a prompt or a script. Google's cleanup suggestions sit under Data, then Data cleanup, and flag problems in red. Gemini in Sheets gained multi-step edits in Google's 16 October 2025 update, so one prompt can delete inactive rows and reformat a column together.

ChatGPT is the only one of the four that writes code and runs it on your file: OpenAI's data analysis help page says it runs Python in a Jupyter notebook environment, and its upload FAQ caps a spreadsheet at about 50 MB (the 400,000 row figure above is that 50 MB divided by 120 characters a row, our arithmetic).

Dedicated AI cleaners are the third route. Numerous, Powerdrill, Grist and Bricks each clean inside their own sheet or upload, so no chat reply cap cuts the table short, but none of their pages describes a line-by-line change log. What each one's own pages say, checked 26 September 2026:

The two chat routes below are the ones where you set every rule and get a line-by-line change log back, which is why the rest of this page is about them.

What prompt cleans a spreadsheet and logs every change?

The prompt that cleans a spreadsheet safely asks for two outputs: the cleaned table, and one log line for every cell it changed. Paste the sheet as CSV or attach it; the model reads cell values as text, so a formula column arrives as whatever it last computed (what happens to each file type). Then send this, edited for your columns:

```

Here is a table pasted as CSV. Clean it and return two things.

1. The cleaned table as CSV: same columns, same order, same number of rows unless a row is an exact duplicate of an earlier one.
2. A change log: one line per change, in the form "row 14, Email: trimmed trailing space". If you make the same change to many rows, list the rows, not a summary.

Rules. Dates become YYYY-MM-DD; where a date could be read two ways (03/04/2025), do not guess, leave it as it was and list it under AMBIGUOUS. Names in Title Case, emails in lower case, spaces trimmed everywhere. Split "Last, First" into two columns named Last name and First name. Turn amounts like "$1,299.00" into plain numbers. Never fill a blank cell; leave it blank and list it under BLANK. If you drop any row, say which and why. Finish with the row count in and the row count out.

```

The change log is the whole point of the prompt. A cleaned table on its own looks finished, and looking finished is the problem: you cannot tell a fixed cell from a rewritten one. With the log, checking the work is a ten minute scan, and any line that says "row 31, Amount: changed 1,299 to 1.299" tells you the model read a European thousands separator as a decimal point.

Then run the same prompt in a second model and compare the two logs. A line one model wrote and the other did not is exactly the cell to open in the original file. Whizi's compare view sends one prompt to two models in two columns; in any other app, open a fresh chat so the second model works from the original CSV and not from the first reply. For formulas, lookups and pivots after the cleanup, use the spreadsheet prompt pack.

How do you use AI to clean data in Excel without pasting every row?

For a big Excel file, paste the header and about 30 sample rows and ask the AI for Power Query steps, which Excel records and reruns on next month's export. Include the ugly rows in the sample, and say how many rows the real file has:

```

Here are the header row and 30 sample rows from a CSV of about 12,000 rows. Do not clean the sample. Write [Power Query steps in the order I click them / a Python script using pandas / a Google Apps Script] that does the following on the full file.

Parse the Date column as day first and write it back as YYYY-MM-DD, and flag any value that fails to parse instead of dropping the row. Trim whitespace in every text column. Lower case the Email column. Title Case the Name columns. Split "Last, First" into Last name and First name. Convert Amount values like "$1,299.00" to numbers. Remove rows that are exact duplicates of an earlier row. Leave blank cells blank.

Write every change to a second sheet or file called changelog with four columns: row number, column, old value, new value. Print the row count before and after. Comment each step in one line so I can read it without knowing the language.

```

Pick the target by where the file lives. Excel users get the most from Power Query, because every step lands in the Applied Steps pane. One trap: Microsoft's Power Query documentation (updated April 2026) says it guesses column types from the first 200 rows, so a date column that is clean for 200 rows and messy after that looks fine in the preview and fails later. Google Sheets users get Apps Script. Anyone comfortable in a terminal gets Python with pandas, which handles a 12,000 row file and a 1,048,576 row file (Excel's hard sheet limit) the same way.

In a plain chat the model writes the script and you run it. ChatGPT's data analysis is the exception, since it runs its own Python on the uploaded file, and OpenAI's help page still tells you to review the generated code, outputs and assumptions before relying on the result. Best AI for data analysis compares the sandboxes; the habit of reading AI-written code before it runs is the one in AI for coding. Once the file is clean, analyzing the spreadsheet with AI is the next workflow.

How many rows can AI clean at once?

In a chat that writes the table back, the app's reply cap is the limit. Whizi caps a reply at 20,000 tokens, which holds about 580 ten-column rows, and about half that once the change log shares it. The numbers below are rough, because tokenizers do not count characters. Anthropic's rule of thumb is about 3.5 English characters per token, so 20,000 output tokens is roughly 70,000 characters.

The input side is looser. In Whizi one message, including text extracted from an attached file, is capped at 100,000 characters (about 28,000 tokens), and each turn at 40,000 input and 20,000 output tokens on every model (the caps, quoted). Other chat apps set their own reply caps, and most do not publish them, but the shape is the same: more rows fit in than come back out.

Now halve the last column, because the change log shares the reply with the table. That is where the checklist's "about 500 rows" comes from for a narrow sheet in Whizi, and it is nearer 150 for a wide one. Past those numbers the model still answers, but the table it returns is shorter than the one you sent, and it will not always say so. A context window of a million tokens does not change this, because the reply cap is separate from the window.

Three failures to check before you trust an AI-cleaned sheet

Check an AI-cleaned sheet for three failures: dropped rows, invented values in blank cells, and dates flipped between day first and month first. Each one leaves a file that looks clean, so none of them shows up on a glance at the result.

Silent row drops. A table longer than the reply cap comes back cut short (the row math is in the section above), and a row that does not parse cleanly, such as a merged cell, a note typed into the amount column or a line with one comma too many, is the kind a cleaner may skip. Neither shows in a table that looks tidy. The prompt above asks for the row count in and out for this reason. If they differ and the log does not say why, the file is not cleaned, it is shorter.

Invented values in blanks. Some tools fill blanks on purpose: Numerous's own blog lists filling in missing values as something one prompt does. A filled cell looks the same as a real value afterwards, so "Never fill a blank" in the prompt sets the rule and the BLANK list shows whether the model kept it. A blank says nobody knew; a guessed value says somebody did.

Date locale flips. 03/04/2025 is 4 March in London and 3 April in New York, and nothing in the cell says which. Microsoft's Power Query documentation shows what a wrong convention does: UK day first dates loaded under a United States locale throw errors on every day above 12. A model reading the column is missing the same information, so the fix in both routes is to name the file's convention in the prompt and make the model flag ambiguous rows instead of resolving them.

A smaller fourth: currency. "1.299,00" and "1,299.00" are the same amount in two countries, and a model that strips the wrong separator turns €1,299 into €1.299. If the sheet has any European amounts, say so in the prompt.