How to Clean Messy Data in Excel With AI: Duplicates, Dates and Gaps Fixed Step by Step

Real spreadsheets are not born tidy. They arrive as exports from three different systems, a supplier’s CSV with dates in two formats, a customer list where “New York,” “new york” and “NY” are somehow three different places, and a column of numbers that are secretly text. Before AI can analyse anything — before you can analyse anything — that mess has to be cleaned, and cleaning used to be the most soul-destroying hour in data work. It is also, it turns out, work AI is unusually good at, because most mess follows patterns and pattern-fixing is the machine’s home turf. OpenAI’s own data-analysis documentation reads like an accidental cleaning manifesto: it warns that results suffer when a sheet has multiple unrelated tables, empty rows splitting the data, or values trapped inside images. This guide works through the cleaning process step by step — duplicates, inconsistent formats, gaps and stray text — using Copilot in Excel or ChatGPT, in an order that keeps you safe: profile first, clean a copy, verify the counts.

Step 0: Save a copy and profile the mess

Before any cleaning, save a duplicate of the file with a name like customers_RAW and work only on the copy. Then, before fixing anything, ask the AI to describe the mess. Upload the file to ChatGPT or open Copilot’s chat mode in Excel — Microsoft is explicit that chat mode “can analyze your workbook data and provide insights without making changes to your workbook,” which makes it the safe interrogation room. Ask: “Describe each column: what type of data it holds, how many blank cells it has, and any values that look inconsistent with the rest.” Ask separately about duplicates: “Which rows appear to be exact duplicates? Which names or emails look like near-duplicates of each other?” This profiling pass matters because cleaning without a diagnosis is how good data dies — you cannot tell whether the tool helped or harmed if you never counted the problems first. Write down the headline numbers: total rows, duplicate rows, blank cells per important column. Those numbers are your before picture.

Step 1: Standardise formats — dates, numbers and text

Format chaos is the most common and most fixable mess. Dates arrive as 03/04/2026, 4 Mar 26 and 2026-03-04 in the same column — and depending on your region, the first of those is ambiguous in a way that corrupts analysis silently. Numbers arrive wearing currency symbols, commas or trailing spaces, and Excel stores them as text, so they sort wrongly and refuse to sum. Names arrive in every capitalisation pattern known to humanity. AI handles all three well because they are pattern problems. Prompt specifically, one column at a time: “In column C, convert every date to ISO format YYYY-MM-DD. List any values you cannot interpret rather than guessing.” That final instruction — list, don’t guess — is the most important sentence in data cleaning; an AI told to flag ambiguity will, and an AI told only to convert will cheerfully pick a meaning for 03/04 and move on. For numbers-as-text: “Column D should be purely numeric. Identify every value that is stored as text or contains symbols, and show me your proposed conversion before applying it.” In Copilot’s edit mode the change can be applied in place; with ChatGPT you will typically download a cleaned file or copy corrected columns back — slower, but it leaves your original untouched by design, which is its own safety feature.

Step 2: Deal with gaps honestly

Blank cells are not one problem but three, and they need different answers. Some blanks mean “zero” (no units returned), some mean “not applicable” (no discount was offered), and some mean “we failed to record this” — and an AI that fills them all with the same guess manufactures data. Ask the tool to categorise before it touches: “For each column with blanks, tell me the likely reason for the gaps based on the surrounding data, and recommend whether to leave them, mark them as unknown, or fill them — do not fill anything yet.” Where a fill is genuinely justified — carrying a customer name down through merged-looking export rows is the classic case — let the AI do it and add a marker column noting the value was filled, so a future reader can distinguish recorded fact from reasonable inference. OpenAI’s guidance to avoid “empty rows or columns that split the data” cuts the other way too: fully blank separator rows inside a table should be removed, because they break the one-table structure every analysis tool expects.

Step 3: Remove duplicates — carefully

Exact duplicates are easy; near-duplicates are where value and danger both live. The same customer as “Jon Smith / [email protected]” and “John Smith / [email protected]” is one entity wearing two coats, but “J. Smith” at two different addresses may be two people. Work in two passes. Pass one: exact duplicates on a key column (email or ID) — safe to merge, and the AI can do it while reporting how many rows it removed. Pass two: ask for a suspect list, not a merge: “List groups of rows that may describe the same person or company, with the differences highlighted, and do not change anything.” You adjudicate the list — it is usually short — and only then instruct the merges. Microsoft notes Copilot can reshape and merge data across sheets in edit mode, which makes this fast once you have decided; the deciding, however, stays human. Every duplicate removed should reduce your row count by exactly the number you approved: check it against the before picture from Step 0.

Step 4: Verify, document, then analyse

Cleaning ends with an audit, not a shrug. Compare final row and column counts with your before picture and account for every difference. Spot-check five rows at random against the raw file — names, dates and amounts should match where no change was intended. Ask the AI to write a cleaning log: what it changed, in which columns, by what rule, and how many cells were affected; save that log with the file, because “the data was cleaned” is a claim your future self, your auditor or your successor will want evidenced. Only now is the sheet ready for the interesting work — the formulas in our formula guide and the charts in our charts and insights guide both assume data in exactly this state. The full context for why tidy structure matters so much to these tools is in the pillar, AI for Excel and Data.

What this means

Data cleaning has always been the invisible tax on analysis — the unglamorous hours that never appeared in anyone’s job description and quietly consumed the majority of every data project. AI does not abolish that tax, but it changes who can afford to pay it. A large organisation has analysts for whom cleaning is a profession; a five-person business has whoever drew the short straw, working at midnight, skipping steps. For that second group — most US small businesses, nonprofits, school offices and solo operators — an AI that profiles a messy export, proposes conversions, lists its uncertainties and logs its changes is the difference between data that gets used and data that gets abandoned in a folder called “exports_final_v2.” The comparison worth holding onto is with spell-check: it did not eliminate the need to write carefully, but it made the first pass at correctness nearly free, and everyone’s standard rose because checking became cheap. AI cleaning is doing the same to spreadsheets. The remaining human job is the one this guide keeps returning to — judgement about what a blank means, whether two names are one person, whether a “fix” is actually a falsification. The machine proposes; the person who knows what the data is about disposes. Keep that division of labour and the midnight cleaning sessions genuinely are over.

It is worth noticing what this does to the politics of data inside a small organisation. Messy data used to be a private embarrassment — everyone knew the customer list was unreliable, nobody had the hours to fix it, so decisions quietly routed around it and the file decayed further. Cheap, competent cleaning breaks that cycle: the export gets fixed the week it arrives, the duplicates get merged while someone still remembers creating them, and the humble spreadsheet becomes trustworthy enough to actually run things from. There is a compounding effect, too — clean data makes every future AI interaction better, from formula help to forecasting, because these tools are only ever as good as the structure they are handed. The organisations that benefit most from AI in spreadsheets will not be the ones with the cleverest prompts. They will be the ones that finally kept their tables tidy — and now have a machine that rewards the tidiness every single day.

Bottom line

AI makes cleaning affordable for the small teams who used to skip it — but it cleans what you tell it to. Profile the mess first, standardise honestly, and never let a tool quietly decide what your data means.

Read Next

• How to Use AI to Write Excel Formulas: A Step-by-Step Beginner’s Guide
• AI for Excel and Data: How AI Helps With Spreadsheets, From Formulas to Full Analysis

Sources

• Data analysis with ChatGPT — OpenAI Help Center (accessed 7 November 2026)
• Get started with Copilot in Excel — Microsoft Support (accessed 7 November 2026)