Clean Excel Data with AI: Dates, Blanks, and Duplicate Records

“Clean this spreadsheet” leaves several decisions unstated. Does a repeated name mean a duplicate registration? Does a blank payment amount mean zero? Is 10/11/2026 October 11 or November 10? AI can propose a tidy table while quietly choosing answers you never authorized.

Start with the rules that decide whether a record stays, changes, or needs a question. Then ask the assistant to apply them and explain each change. A cleaned table is useful only when you can still account for the original records.

Six rows with four different problems

This is an invented workshop registration list. All amounts use the same currency. Treat the date column as imported text for this exercise, even where it looks like a date.

R1 and R2 repeat every business field. R1 and R5 repeat a name but have different registration IDs. R3 has an ambiguous date. R4 has no recorded amount, while R5 explicitly records zero. Those last two states must remain different.

For this exercise, the registration ID identifies a registration. Remove an exact duplicate only when all four business fields agree, keeping the earlier source row. Keep both records if an ID repeats with conflicting fields and flag the conflict. Dates must become YYYY-MM-DD only when their meaning is known. A blank payment remains unknown; zero remains zero.

Microsoft's duplicate-removal guide explains that selected columns determine what counts as a duplicate and that removal deletes data. Keep the original sheet and do the exercise in a copy. Selecting only the name column would discard a legitimate Ada registration under these rules.

Ask for decisions before a replacement table

Paste the sample and these instructions into an assistant that can read tables:

Clean this practice registration list using only these rules:
- Registration ID identifies a registration. Remove a repeated record
  only when ID, name, date text, and paid amount all match.
  Keep the earlier source row and record the removed row's ID.
- Keep conflicting records and flag them. Do not resolve conflicts.
- Normalize dates only when their meaning is unambiguous.
- A blank paid amount is unknown. Do not replace it with zero.
- Repeated names alone are not duplicates.

Return a row-by-row decision log first, then the retained table.
Keep source row IDs, original date text, and original paid values.
Add a normalized date and a review-status column.
Do not overwrite the source or claim to have edited my workbook.

The expected decisions are specific:

R3's normalized date should stay empty until the source owner confirms its meaning. Changing every slash to a hyphen would produce a uniform appearance without resolving the date.

Check the result before using it

Count six source records, one documented removal, and five retained records. There should be five unique registration IDs after the removal. Ada should still appear twice. The unresolved fields belong to R3 and R4; they must remain visible in the review column.

The known retained payments sum to 20 + 20 + 0 + 20 = 60. Label this known recorded payments, not a final total received: R4 is unknown. If a summary says “all five registrations paid 60,” it has converted missing information into a business claim.

Also separate a date's display from its stored value. Microsoft documents converting text dates into Excel dates. Changing the number format alone does not establish what ambiguous source text meant. Resolve the meaning first, then verify the converted value by sorting or using a date calculation in your actual workbook.

Keep the source sheet, the cleaned copy, and the change log together. For a larger list, reconcile every removed row with a retained row and review every conflicting ID. AI can save time assembling those records; the decision to merge, delete, or fill an unknown value still needs an explicit rule or a source-owner answer.