AI Data Cleaning Tool for Excel: Copilot vs Formulas vs a Dedicated Cleaner
If you are searching for an AI data cleaning tool for Excel, the honest answer is that the best AI use in Excel today is to *write the cleaning step for you*, not to edit your cells directly. Excel's in-cell AI function is gone: Microsoft's support page states that "Starting September 14, 2026, the COPILOT function is no longer available in Microsoft Excel." What remains is the Copilot pane (a licensed add-on), Google's =AI() function in Sheets, chat assistants that can draft formulas or scripts, and the deterministic tools Excel has always had: TRIM, Power Query, Text to Columns and Remove Duplicates.
This guide compares them for the job most people mean by "cleaning": trimming whitespace, standardising nulls and case, fixing dates and currency, and removing empty or duplicate rows. We checked every vendor page cited below on October 10, 2026, and we ran one real test, described in its own section.
First, the news: =COPILOT() in Excel was retired
Many guides still describe typing =COPILOT("clean this", A2:A100) into a cell. That no longer works. Microsoft's page says previously calculated results "remain in the workbook as cached values", but when a cell recalculates "the function returns a #NAME? error because it's no longer recognized." Microsoft points users to Copilot in Excel, opened from the Copilot icon in the lower-right corner of the app.
Two things are worth remembering from the function's short life. It was a preview feature, available to Microsoft 365 Insiders rather than every user. And while it existed, Microsoft's own documentation described it as non-deterministic (it could return different results on recalculation) and advised using native Excel formulas for any task requiring accuracy or reproducibility. That advice is a fair summary of how to think about *any* generative AI feature in a spreadsheet, and it is why the rest of this guide leans on repeatable tools.
If you inherited a workbook that used =COPILOT(), the cached values are still there until something triggers a recalculation. Copy the affected column and paste it as values before you touch anything else, otherwise a recalc turns the whole column into #NAME?.
The AI data cleaning options for Excel and Sheets, compared
| Option | What it does for cleaning | Requirements | Watch out for |
|---|---|---|---|
| Copilot in Excel (pane) | Chat about the open workbook, get formula suggestions, summaries and classifications. | A Microsoft 365 plan plus the Copilot add-on licence (Microsoft Learn). Note the product was renamed from "Microsoft 365 Copilot" to "Microsoft Copilot" in 2026. | Licensing cost. Suggestions still need checking row by row. |
| =COPILOT() cell function | Nothing. Retired 2026-09-14. | n/a | Recalculated cells return #NAME? (Microsoft Support). |
| Google Sheets =AI() | Per-cell generation: categorise, summarise, rewrite text. | An eligible Google Workspace or Google AI plan (Google Help). | Google's docs say "only the first 350 selected cells with AI functions will be generated" per selection. Output is text, so numbers come back as text. |
| Chat assistant (ChatGPT, Claude, Gemini) | Drafts a formula, a Power Query step or a Python script from a description and a few sample rows. | Any plan. Check training and retention terms before pasting real data. | Non-reproducible if you ask it to edit data directly. Ask for the transformation, not the result. |
| Power Query (built into Excel) | Trim, split, merge, type conversion, remove duplicates, all recorded as repeatable steps. | Excel for Windows or Mac, no extra licence. | Learning curve. Case-sensitive matching for duplicates (Microsoft Learn). |
| Dedicated cleaner (e.g. PipeSheets) | Fixed, previewable steps: trim, standardise nulls, dates, currency, headers, dedupe, merge. | Upload CSV or XLSX; download the result. | Not a general spreadsheet. Good for recurring exports, overkill for a one-off five-row fix. |
What happened when we asked an AI assistant to clean a file directly
On October 10, 2026 we pasted our 13-row sample file (the same messy sales CSV PipeSheets uses for its demo) into a fresh session of Claude (Sonnet 5.5, through the Claude Agent SDK, no tools enabled) with the one-line prompt "Clean this data and give me back the cleaned CSV". We compared the output to the input line by line. Here is the input:
Order ID,Customer Name,Order Date,Amount,Product Category,Status,Notes
1001, John Smith ,2024-01-15,$89.99,Electronics,completed,First order
1002,Jane Doe,01/20/2024,125.50, Clothing ,SHIPPED,
1003, Bob Johnson ,2024-01-25,NULL,electronics,pending,Awaiting payment
1004,Alice Williams,2024-02-01,$45.00,ELECTRONICS,Completed,N/A
1005,Charlie Brown,02/10/2024,null, Home & Garden ,shipped,
1006, Diana Ross,2024-02-15,199.99,clothing,PENDING,VIP customer
,,,,,,
1007,Edward Chen,2024-02-20,$75.50,None,completed,--
1008, Frank Miller ,02/25/2024,N/A,Electronics,Shipped,Return requested
1009,Grace Lee,2024-03-01,320.00, CLOTHING ,pending,
1010,Henry Wilson,2024-03-05,$150.00,home & garden,COMPLETED,
1011, Ivy Thompson,03/10/2024,89.99,,shipped,Missing category
1012,Jack Davis,2024-03-15,$210.50,Electronics,pending,Large orderWhat the assistant did (13 rows in, 12 rows out):
- Trimmed whitespace in every field, correctly.
- Blanked NULL, null, N/A, None and -- in the Amount, Notes and Product Category columns. Reasonable, but it chose the null list itself, and it treated the category "None" as a null, which may or may not be what you want.
- Removed the fully empty row. Also reasonable, and it said so.
- Left capitalisation, date formats and dollar signs untouched, and listed those as things it did not change "because changing it would alter your data".
- Did not report row counts. We counted them ourselves. It also did not show which cells changed, so the only way to verify was a manual diff.
That is a good result for a 13-row file, and it would be an unverifiable one for a 13,000-row file. The output is a new copy of your data with decisions baked in. Nothing about it is reusable next month, and a second run could make different choices. The same assistant did much better when we asked it for a *script* with explicit rules and a row-count check; we document those tests in our prompt guide later this month.
The formula baseline: what Excel can already do
Before paying for an AI add-on, know what the built-in tools cover, and their blind spots.
TRIM and CLEAN
TRIM removes leading, trailing and repeated internal spaces, but Microsoft's documentation is explicit that it was designed for the ASCII space (character 32) and that "by itself, the TRIM function does not remove" the non-breaking space, character 160, which is common in data pasted from web pages. The working pattern is:
=TRIM(SUBSTITUTE(B2,CHAR(160)," "))We cover this and the other no-formula fixes in How to clean an Excel file online, so this post will not repeat them.
Power Query
Power Query is the closest thing Excel has to a saved cleaning pipeline: every transform is a recorded step, and Refresh re-runs them on new data. Two details from Microsoft's own documentation trip people up. Power Query's duplicate removal is case-sensitive, so "Electronics" and "electronics" are different values until you lowercase the column first. And Microsoft warns that it "doesn't guarantee that it keeps the first instance in a set of duplicates", so if you care which row survives, sort and index before deduplicating.
Text to Columns and Remove Duplicates
Both are fine for one-off fixes. Neither is repeatable, and Remove Duplicates gives you a count but not a list of what it removed, so you cannot review the decision afterwards.
Where AI genuinely helps in Excel cleaning
In our experience the useful AI jobs are the ones where the output is a transformation you can read and keep:
- Writing the formula. "Give me a formula that returns the number from a cell that may contain $, commas or the word NULL" is a prompt that produces something you can inspect and fill down. We tested that exact prompt and it returned a correct IFERROR/VALUE/SUBSTITUTE formula.
- Writing the Power Query step or the Python script, with a rule that ambiguous values are flagged rather than guessed.
- Explaining an existing formula or M step you inherited.
- Suggesting which columns look like dates, amounts or identifiers so you can check them. A suggestion is cheap to verify; an edit is not.
And the jobs where it hurts:
- Editing cells directly. You get a new copy with no diff.
- Anything with leading zeros, long identifiers or mixed date formats, where a confident guess is worse than a flag.
- Recurring monthly files, because a chat result cannot be re-run.
Which to use when: a short decision list
Pick by the shape of the problem, not by the newness of the tool:
- One file, a few hundred rows, a visible problem: TRIM, Text to Columns, Remove Duplicates.
- One file, you are unsure how to express the fix: ask an assistant for the formula, paste the formula into a helper column, keep the original.
- The same export every week or month: Power Query, or a saved pipeline in a dedicated cleaner.
- Multiple exports that need the same header names and a merge: a dedicated cleaner that normalises headers and joins on a key, then download as XLSX.
- Client or customer data you cannot paste into a consumer chat tool: a deterministic tool, or an assistant on a business tier with no-training terms. Our guide on whether AI is safe for financial data covers the rules.
What a dedicated cleaner does differently
PipeSheets is not an AI tool, and we think that is the right design for the cleaning step itself. Every operation is a fixed, named step (trim whitespace, standardise nulls, convert date format, normalise currency, normalise headers, deduplicate rows, merge files) with a preview of the affected rows before you download. Our column matcher, which maps messy export headers to the names a pipeline expects, is deterministic too: exact match, then normalised, then synonyms and abbreviations, then fuzzy, with value checks. On the 30 real export header sets we tested it against, it made every expected match and zero wrong automatic matches. That is the bar we would want an AI feature to clear before it touched a cell: suggest, show the evidence, and let you approve.
If you want to try the deterministic route on a vendor or product list, the vendor catalog CSV cleaner page walks through a typical pipeline. If your file is a one-off and the problem is visible, Excel's own tools are still the fastest fix, and an AI assistant is a fine way to write the formula you cannot remember.
Related guides
- How to Clean an Excel File Online (No Formulas Needed)You can clean a messy Excel file online in a few minutes: whitespace, inconsistent headers, empty rows, null-value chaos, and case problems, all without writing a single TRIM or SUBSTITUTE formula. Here is what online cleaning covers, how to vet a tool, and a full walkthrough.
- How to Clean an Excel Contact List for Mail Merge (Word and Gmail)Mail merge failures almost always trace back to the spreadsheet, not Word or Gmail. Here is exactly how to clean your contact list so every letter and email comes out right the first time.
- Amazon Inventory File Cleanup: From Supplier XLSX to Upload-Ready FileA supplier sends you an XLSX full of merged cells, marketing rows, and mixed columns. Here is how to turn it into a clean Amazon inventory file that uploads without stripping leading zeros or failing on missing required fields.
- How to Clean Supplier Master Data: A Practical GuideSupplier master data rots quietly: the same vendor under four name variants, SKUs in three formats, prices with mixed currencies, contacts that left years ago. Here is a practical audit checklist, a field-by-field cleanup workflow, and the governance that keeps the file clean.
Try the automated solution
PipeSheets can fix these issues automatically. Create a free account to clean your first file.
Create a free accountOr see how it works: Vendor Catalog CSV Cleaner