AI for Excel: messy sheet to clean data, with formulas we verified

Takeaway: Paste a few dummy or redacted rows into ChatGPT with one sentence - never real customer or private records - and you get back the exact spreadsheet formulas to clean the full sheet yourself: extra spaces, mixed capitals, duplicates. We ran those formulas in a real spreadsheet program and verified the results. The prompt, the formulas, and the proof are below.

A typical shop stock sheet or office list has the same three problems: extra spaces, mixed capitalization, and duplicate rows typed differently ("basmati rice 5kg" vs "BASMATI RICE 5KG"). Totals go wrong and duplicates hide.

The sample we used

Six rows, exactly as typed into the test (item, qty, rate):

• basmati rice 5kg - qty 10, rate 550
• BASMATI RICE 5KG - qty 10, rate 550
• tata salt 1kg - qty 25, rate 28
• aashirvaad atta 10kg - qty 5, rate 465
• tata salt 1kg - qty 25, rate 28
• maggi noodles 70g - qty 100, rate 14

Step 1: paste this prompt

Then paste your rows under it - dummy or redacted rows only. Never paste customer details or private stock records into a chatbot; use made-up rows or blank out real names first. If your columns differ, say which column holds what.

Step 2: the formulas it gives (and what each does)

Assume item names in column A, quantity in B, rate in C, data from row 2 to row 201.

D2 - clean the name, then drag down to D201: =PROPER(TRIM(CLEAN(A2)))

TRIM removes leading, trailing and doubled regular spaces, CLEAN removes nonprinting ASCII control characters, PROPER fixes capitalization. Two honest limits: TRIM does not remove every Unicode or non-breaking space, and CLEAN does not catch every invisible character - if a name still looks odd after cleaning, that is why. One more quirk: PROPER capitalizes any letter that follows a number, so "5kg" becomes "5Kg" and "70g" becomes "70G". That can be wrong for case-sensitive SKUs or brand spellings - verify labels and units manually after the cleanup.

E2 - flag every duplicate, drag down to E201: =IF(COUNTIF(D:D, D2)>1, "DUPLICATE", "UNIQUE")

F2 - keep the first copy, flag the rest: =IF(COUNTIF(D$2:D2, D2)>1, "DUPLICATE", "UNIQUE")

G2 - flag only rows that fully match (same name AND qty AND rate):

=IF(COUNTIFS(D:D, D2, B:B, B2, C:C, C2)>1, "DUPLICATE", "UNIQUE")

Working with more rows? The whole-column references (D:D and so on) already cover any number of rows.

Step 3: our verification, and what to check before deleting

We did not stop at the AI's answer. We ran the exact formulas in a real spreadsheet program (LibreOffice Calc) on the six sample rows and read the computed results:

The cleaned names with the duplicate flag from column E, exactly as computed:

• Basmati Rice 5Kg - DUPLICATE
• Basmati Rice 5Kg - DUPLICATE
• Tata Salt 1Kg - DUPLICATE
• Aashirvaad Atta 10Kg - UNIQUE
• Tata Salt 1Kg - DUPLICATE
• Maggi Noodles 70G - UNIQUE

Everything checked out: two duplicate groups, four rows flagged - the correct answer. The "keep first, flag the rest" variant in F correctly left the first copy UNIQUE and flagged only the repeats.

Two cautions worth keeping:

• The chat's own summary line said "3 duplicate rows", but its table flagged 4. The formulas were right; the summary was not. Always verify the table, not the AI's summary of it.
• A DUPLICATE flag is a candidate for review, not proof a row is safe to delete. Two rows with the same name can be separate valid stock entries. Review every flagged row, and save a copy of the original sheet before you start.

Tested 4 November 2026 on the free, logged-out ChatGPT experience. Formulas verified by running them in LibreOffice Calc on the sample above. These are standard functions also documented by Microsoft for Excel - see the official references: TRIM, CLEAN, PROPER, COUNTIF, COUNTIFS. Function behaviour in Google Sheets can differ in edge cases; test on a copy first.

Join the Nudge Daily community at nudgedaily.in for one practical AI nudge every day.