How to Clean Messy Excel and CSV Data With AI Without Breaking Your Totals
A cleanup that strips currency symbols and adds everything into one column cannot tell you whether it was right. On a 60-row vendor file I tested on November 15, 2026, that shortcut reported 21,748,113.34 as a single total. The file's real USD total is 51,464.59.
The working method separates USD from KRW into two columns. It parses dates with an explicit format list, removes duplicates by invoice number, and compares a checksum against the untouched source.
Key Takeaways
- A currency-blind cleanup reported 21,748,113.34 for a file whose real USD total is 51,464.59, an overstatement of 21,696,648.75.
- Comparing whole rows as text removed 0 of the 4 duplicate rows. Keying on invoice number removed all four and cut USD by 238.75 and KRW by 2,480,000.
- One date format read 19 of 60 rows. A three-format parser read 58 and rejected the 2 impossible dates instead of guessing them.
- DATEVALUE() read only 25 of the 58 valid dates in a Korean-locale sheet, so 33 rows would have vanished from every total.
- A seven-cell checksum strip in Google Sheets matched my independent Python totals to the cent, with a delta of 0.00.
What actually goes wrong when AI cleans a spreadsheet?
A spreadsheet goes wrong when a cell keeps its look but loses its type, because currency symbols and date strings are text rather than numbers.
Three failures below each left the file readable and changed an answer. All three were reproduced on the same 60-row file, built to look like a real accounting export.
- Currency symbols are thrown away. "₩185,000" becomes 185000 and joins the dollar column. Twenty-one rows in my file carried KRW amounts, and their unique total of 19,128,000 sat inside a figure reported as dollars.
- One date format is applied to all rows. With MM/DD/YYYY only, 41 of the 60 rows failed to parse. A tool that drops what it cannot parse loses those rows without saying so.
- Duplicates are compared as text. Padding, letter case, a different date format or "KRW 1,240,000" instead of "₩1,240,000" makes each copy unique text, so nothing looks duplicated.
| Mess | Count | Examples from the file |
|---|---|---|
| Date formats | 3 | 20 rows MM/DD/YYYY ("09/24/2026"), 19 rows YYYY-MM-DD ("2026-07-31"), 20 rows "Mon DD, YYYY" ("Sep 24, 2026") |
| Unreadable dates | 2 | "13/45/2026" and one blank cell |
| Currency notations | 4 | "$2,100.00", "₩1,240,000", "KRW 1,240,000", a bare "150.75" |
| Vendor name strings | 17 for 11 vendors | " Acme Corp ", "ACME CORP", "acme corp" |
| Duplicate rows | 4 | INV-1034, INV-1008, INV-1021 and INV-1003 each appear twice |
| Valid rows | 58 | 36 USD rows and 18 KRW rows once cleaned, 54 of them unique |
On a phone, swipe the table sideways to see every column.
How much did a currency-blind cleanup change the totals?
The currency-blind cleanup was wrong in both directions. It inflated the dollar figure by 21,696,648.75, and it silently dropped the 41 rows whose dates it could not read.
I ran both approaches on the same file on 2026-11-15. The first stripped characters and summed one column. The second split currencies, parsed three date formats, removed duplicates by invoice and ran a checksum.
| Measure | Currency-blind cleanup | Verified cleanup |
|---|---|---|
| Rows in the report | 60 | 54 unique rows out of 58 valid rows |
| Dates read | 19 of 60 | 58 of 60 |
| Rows rejected and listed | 0 | 2 |
| Duplicate rows found | 0 | 4 |
| USD total | 21,748,113.34 (one mixed number) | 51,464.59 |
| KRW total | not separated | 19,128,000 |
| Gap against the file's ground truth | +21,696,648.75 on the USD figure | 0.00 on both currencies |
On a phone, swipe the table sideways to see every column.
The inflated number is not a rounding problem. KRW amounts were added to dollar amounts and the four duplicate rows stayed in. Two undated rows rode along with whatever their amount cells contained.
Why don't duplicate vendor rows disappear when you remove duplicates?
A remove-duplicates step compares cell text, and each duplicate pair in this file differed in spacing, letter case, date format or currency notation.
- INV-1034, Northwind Traders, 88.00: one row reads "Sep 24, 2026 | Northwind Traders | $88.00", the other "09/24/2026 | Northwind Traders | 88.00".
- INV-1008, Cyberdyne Systems, 150.75: an ISO date on one row, a US date on the copy, plus a trailing space in the amount.
- INV-1021, Busan Freight, 1,240,000 KRW: "Aug 26, 2026" against "2026-08-26".
- INV-1003, Busan Freight, 1,240,000 KRW: "KRW 1,240,000" against "₩1,240,000".
Comparing whole rows as text removed 0 of these 4. The table below shows what each possible key did to the 58 readable rows.
| Key used | Rows left | USD total | KRW total |
|---|---|---|---|
| No dedupe | 58 | 51,703.34 | 21,608,000 |
| Invoice number | 54 | 51,464.59 | 19,128,000 |
| Vendor name plus amount | 42 | 51,464.59 | 6,376,000 |
On a phone, swipe the table sideways to see every column.
The invoice key is the only one that removed exactly the four copies. Keying on vendor plus amount removed 16 rows instead of 4. Twelve of them were real charges for the same vendor at the same price on a different date, and that key understated KRW by 12,752,000. The file still looked clean.
How do you verify an AI-cleaned file in Excel or Google Sheets?
Put helper columns beside the data and accept the cleanup only when the checksum cells match totals you computed from the untouched source.
The layout below is the one I ran. Data sits in A1:E61 with headers Date, Vendor, Invoice, Category and Amount. Helpers go in F to I, the month list in N2:N13, and the checksum strip in K2:L13.
- Keep the source untouched. Copy the raw export into its own tab and never edit that tab.
- Add the helper columns. Date_clean in F, Currency in G, Amount_num in H, Dup_flag in I, filled down to row 61.
- Fill the checksum strip. Seven cells in K2:L13, each a formula, no typed numbers.
- Reject on any mismatch. If one cell disagrees with the source tab, do not paste the cleaned numbers into a report.
The currency flag and the numeric amount are short formulas that work the same in Excel and Google Sheets.
G2: =IF(OR(LEFT(E2,1)="₩",LEFT(UPPER(E2),3)="KRW"),"KRW","USD")
H2: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(E2),"KRW",""),"₩",""),"$",""),",",""))
I2: =IF(COUNTIFS($C$2:C2,C2)>1,"DUP","UNIQUE")Dates need more care. DATEVALUE() follows the sheet's locale, and in a Korean-locale sheet it read 25 of my 58 valid dates. The explicit parser below reads all three formats and returns a blank instead of rolling an impossible date into the next month.
F2: =IFERROR(IF(ISNUMBER(SEARCH("-",A2)),IF(AND(VALUE(MID(A2,6,2))<=12,VALUE(RIGHT(A2,2))<=31),DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))),""),IF(ISNUMBER(SEARCH("/",A2)),IF(AND(VALUE(LEFT(A2,FIND("/",A2)-1))<=12,VALUE(MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1))<=31),DATE(VALUE(RIGHT(A2,4)),VALUE(LEFT(A2,FIND("/",A2)-1)),VALUE(MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1))),""),IF(AND(VALUE(MID(A2,5,2))<=31,ISNUMBER(MATCH(LEFT(A2,3),$N$2:$N$13,0))),DATE(VALUE(RIGHT(A2,4)),MATCH(LEFT(A2,3),$N$2:$N$13,0),VALUE(MID(A2,5,2))),""))),"")N2:N13 holds Jan through Dec. The parser compares against that list instead of asking the locale what November is called.
| Cell | Formula | Result |
|---|---|---|
| Rows in payload | =COUNTA(C2:C61) | 60 |
| Valid dates | =COUNT(F2:F61) | 58 |
| Duplicate rows | =COUNTIF(I2:I61,"DUP") | 4 |
| Unique valid rows | =COUNTIFS(I2:I61,"UNIQUE",F2:F61,">0") | 54 |
| USD total | =SUMIFS(H2:H61,G2:G61,"USD",I2:I61,"UNIQUE",F2:F61,">0") | 51,464.59 |
| KRW total | =SUMIFS(H2:H61,G2:G61,"KRW",I2:I61,"UNIQUE",F2:F61,">0") | 19,128,000 |
| USD lost if duplicates stay | =SUMIFS(H2:H61,G2:G61,"USD",F2:F61,">0")-L6 | 238.75 |
On a phone, swipe the table sideways to see every column.
All seven cells matched my Python totals: 51,464.59 and 19,128,000 after dedupe, with 60 rows in, 58 readable, 4 duplicates and 54 left. The only noise was floating point, where the unformatted USD cell returned 51,464.59000000001.
What prompt should you give the AI before it touches your file?
The prompt should forbid guessing, require a list of every row it changed or dropped, and ask for helper columns instead of a rewritten file.
You are cleaning a vendor expense export. Work on a copy and never edit the source tab.
Columns: Date (A), Vendor (B), Invoice (C), Category (D), Amount (E).
1. Do not merge currencies. Keep USD and KRW as two separate numeric columns.
2. Parse dates with exactly these formats, in this order: MM/DD/YYYY, YYYY-MM-DD, Mon DD, YYYY.
If none match, leave the date blank and list the row under REJECTED. Never guess a day, month or year.
3. Trim spaces and normalize vendor case. List every vendor string you changed.
4. Mark duplicates by invoice number: first occurrence UNIQUE, later copies DUP. Delete nothing.
5. Output helper columns Date_clean, Currency, Amount_num and Dup_flag for every row.
6. Output a checksum block: rows in payload, valid dates, duplicate rows, unique valid rows, USD total, KRW total.
7. End with counts of REPAIRED, REJECTED and UNCHANGED rows. If a checksum cannot be computed, say so instead of estimating.In this test I applied these rules with a script and then checked the result in a spreadsheet. If a chatbot's answer disagrees with your checksum, the checksum wins.
Where does this method break down?
Four conditions still break it: locale-dependent date functions, invoice numbers reused for real charges, currency conversion, and files too large to inspect in one piece.
- Your sheet's locale decides what a date is. DATEVALUE() read 25 of 58 dates here. Use the explicit parser, or set the sheet's locale to match the export before you trust any date count.
- Real charges can share an invoice number. A split invoice or a credit note breaks the key. Read the DUP rows before deleting them; in this file every pair also matched vendor, date and amount.
- Two currency totals are not one number. Merging them needs a dated exchange rate, and the rate date changes the answer. I did not convert anything in this test.
- Size was not tested. 60 rows fit in one paste. A 50,000-row export needs per-month chunks with a checksum per chunk, which I have not measured.
Your first step is to build the checksum strip before the next AI attempt. Copy the raw export to its own tab, add columns F to I, and fill the seven cells in K2:L13. Run the cleanup only if those cells match the source.
Related guides
This post is one of the guides mapped in AI Office Workflows That Actually Work: Verified Guides for Excel, PDFs, and Documents (2026), which lists every guide in this room with the number each one measured.
You can run the cleanup on the same file: download the 60-row test CSV (3.4 KB, no sign-in), clean it with your own tool, then check four things against the answer key — 60 rows, 58 usable rows, 4 duplicates, and separate totals of USD 51,464.59 and KRW 19,128,000.
These two posts use the same check-first approach on the rows you hand to an AI:
- How to Paste AI Markdown Tables Into Word and Google Docs Without Broken Borders
- REA for AI Agents: What It Can Prove About an App
Frequently Asked Questions
Can I do this with a free plan or a free chatbot?
Yes for the checking side: the helper columns and checksum cells are built into free Google Sheets. The script that produced my ground truth runs on free Python. I did not test a free chatbot's rewritten file in this run, so treat its output as unverified until your checksum matches.
What if my dates are DD/MM/YYYY instead?
Change the order of the format list, because the parser takes the first format that fits. If one column mixes day-first and month-first dates, no parser can separate them, so split the file by source system first.
Should I delete duplicate rows or only flag them?
Flag them and keep the amounts visible, because a deleted row cannot be restored from the cleaned file. My four duplicates carried 238.75 USD and 2,480,000 KRW, and those are the exact amounts you want to watch drop.
Why does my total differ by a few cents?
Floating-point storage, not a data error. Google Sheets returned 51,464.59000000001 for a total of 51,464.59, so round both sides before you call a file wrong.
Comments
Post a Comment