Cleaning messy data with AI: names, dates and duplicates

Can AI tidy a messy column?

It can try, and the makers say it can edit your sheet directly. In edit mode, Copilot in Excel can edit your workbook directly based on your requests.¹ In Gemini in Sheets, you click Apply on an "Action preview card" first.²

Microsoft's own examples are small and specific, such as splitting a first name out of a full name.³ Small, clear jobs are where to start.

What can go wrong?

Cleaning means changing things. So three risks matter.

• A short date can mean one month or another, depending on whether the day comes first. The AI may pick one and not tell you.
• Two real people with similar names can be treated as one.
• A "fixed" spelling can change a real name, product code or street.

None of these gets flagged by itself. Both makers say to check the result. Microsoft's help page tells you to verify what Copilot creates.³ Google's page says Gemini can be wrong or unsuitable.²

The date point is worth a note: day-first and month-first order differs between Australia, the UK and the United States.

How do I check that nothing was lost?

• Copy the sheet first. Work on the copy.
• Count the rows before. Write the number down.
• Count them again after. If they differ, find out why.
• Compare a total, such as a sum of amounts, before and after.
• Sort the cleaned column and read the top, the bottom and some from the middle.

If you've got a column of plain text IDs, check a few against the original by eye.

Plan mode helps here. Microsoft says it creates a structured approach so you can review and confirm the steps before it starts.¹

What about duplicates?

Removing duplicates is a built-in Excel job, and it's worth knowing what it does. Microsoft says that removing duplicate values means you're permanently deleting them, while filtering only hides them.⁴

When duplicates are removed, the first occurrence of the value in the list is kept and other identical values are deleted.⁴

There's a trap. Data is removed from all columns, even if you don't select all of them. The columns you tick form the "key" used to find a match, and a matching row is removed whole.⁴

So if you ask AI to "remove duplicates" and it matches on name alone, two different customers with the same name could lose a row. Say which columns count.

Microsoft's advice is to copy the original range or table to another worksheet or workbook before removing duplicates.⁴ Good advice for any clean-up.

Should I use AI at all for this?

For a few hundred rows of free text, such as mixed spellings, it may save time, as long as you check. For exact jobs, like trimming spaces or splitting a name on a comma, a plain formula gives the same answer every time. More on exact jobs is in this guide to repeatable answers.

Before you send a customer list to any AI tool, read the privacy page.