Clean up a messy spreadsheet with AI

A spreadsheet you can actually trust

Messy data is why reports don't add up. This guide shows two ways to fix it: AI as your guide inside Excel, or AI doing the cleaning and handing back a tidy file.

• Find and remove duplicate rows, and know how many there were
• Turn a column of mixed date formats into real dates Excel can sort and filter
• Remove stray spaces and fix inconsistent capitals ("north", "NORTH ", "North")
• Strip currency symbols so prices become numbers you can add up
• Flip "Surname, First name" into "First name Surname"
• Spot blanks and problems before they ruin a report

Good for: anyone who combines exports from different systems, people who get "the data" from colleagues, and anyone whose totals never quite match.

What you need

Do it in five steps

1. Get the AI to spot the problems

   Open the messy file in Excel. Copy the header row and about 10 rows of data, then paste them into the AI with this prompt. (With real work data, change names and numbers before pasting. The AI only needs to see the shape of the mess.)

   Copy this promptCopy

   I'm not an Excel expert. Here are the first rows of a spreadsheet I need to clean up. Please look at it and list every data quality problem you can see (duplicates, inconsistent formats, spaces, symbols, blanks and anything else), in plain English, with which column each one is in.
   
   [paste the rows here]

   You'll get a checklist. That alone is useful, because you now know what to fix before you build a report.

2. Route A: Have the AI walk you through fixing it in Excel

   Copy this promptCopy

   Thanks. I want to fix all of these in Excel (Microsoft 365) myself, without uploading the file. The data is on a sheet called "Sales", headers in row 1, data in rows 2 to 315. Columns: A Order ID, B Order Date, C Customer, D Region, E Product, F Quantity, G Unit Price, H Sales Rep.
   
   Please give me numbered steps a beginner can follow, one problem at a time. For each step, tell me exactly what to click or the exact formula to use, and how to check it worked. Start with removing duplicate rows, then the rest.

   Expect steps like these:

   • Duplicates: select the data, then Data › Remove Duplicates. Excel tells you how many it removed. In the practice file it should be 14, leaving 300 rows.
   • Spaces and capitals: a helper column with a formula like =PROPER(TRIM(D2)), copied down, then Paste Special › Values back over the original.
   • "£" signs: Find & Replace "£" with nothing.
   • Dates: a formula or Text to Columns to turn text into real dates. This is the fiddliest one, so ask follow-up questions.
   • Names: a formula to flip "Grant, Quinn" into "Quinn Grant".

   Work through them one at a time. If a step doesn't do what the AI said, tell it exactly what you see.

   Tip: do all this on a copy of the file. Save it as sales-cleaned.xlsx first, so the original is safe.

3. Route B: Let the AI clean the file for you

   Faster, if your data is allowed to be uploaded (and the practice file always is). Attach the CSV file to the chat and send:

   Copy this promptCopy

   Please clean this spreadsheet and give me a cleaned CSV file to download.
   
   1. Remove rows that are exact duplicates, and tell me how many you removed.
   2. Convert every Order Date to a real date in the format YYYY-MM-DD. The dates are UK style (day/month/year), so 08/01/2026 means 8 January.
   3. Trim extra spaces from all text, and make Region consistent: North, South, East or West.
   4. Remove the "£" from Unit Price so it's a plain number.
   5. Change Sales Rep names written as "Surname, First" to "First Surname".
   6. Don't delete rows with a blank Quantity. List their Order IDs so I can check them.
   
   Then give me a short summary table of every change you made, with counts.

   AI assistants that can run code (Claude, ChatGPT and Copilot all can) will process the whole file and give you a download link. Open it in Excel.

   The dates warning. "08/01/2026" is 8 January in the UK and August 1 in the US. Always tell the AI which style your dates use, or it may guess wrong without telling you.

4. Check the AI's work

   Never pass on a cleaned file without checking. These checks take two minutes:

   • Row count: the original had 314 rows. Did 14 go, leaving 300?
   • Spot check: pick three Order IDs and compare them in the old and new files.
   • Filter each text column: clicking the filter arrow on Region should show only North, South, East and West.
   • Sort by date: the oldest and newest dates should make sense.

   Or ask the AI to help:

   Copy this promptCopy

   Before I use this cleaned file, please double-check your own work: confirm the number of rows before and after, that every date is valid and within the range you'd expect, that Region has only the four values, and that no Order ID appears twice. Tell me about anything you're not sure of.

5. Save the recipe for next time

   If this is a report you clean every week or month, ask the AI to turn the steps into something reusable:

   Copy this promptCopy

   I'll get a new version of this file every month with the same columns and the same problems. What's the quickest way to repeat this cleaning each time? Please explain the options for a beginner (saving these prompts, an Excel Power Query, or a small script) and recommend one.

Quick fixes

Use it on your own work

An afternoon of fiddly fixes in a few minutes

Clean data is the boring step everyone skips, and it's why reports don't add up. AI turns an afternoon of fiddly fixes into a few minutes. Route A also teaches you the Excel tricks as you go, so next time you'll know them yourself.

What to build next