Most people think ChatGPT is useless for Excel because it can’t ‘create’ a file. That’s like saying a chef can’t cook because they don’t own your oven. You’re missing the point entirely.
The Setup
Last Tuesday, my colleague Sarah Chen forwarded me a messy CSV from her sales team’s CRM export. It had duplicate leads, inconsistent date formats (some as text like 'Mar 15 2024', others as serial numbers), and no clear ID column. She needed a clean, sorted, deduplicated list by Friday morning — no IT support, no Power Query access, just Excel 365 on Windows.
| Lead Name | Company | Date Contacted | Revenue Potential | Status |
|---|---|---|---|---|
| Jin Lee | Nexus Labs | Mar 15 2024 | $72,500 | Follow-up |
| Aisha Patel | Veridian Systems | 44991 | $114,800 | Proposal Sent |
| Jin Lee | Nexus Labs | Mar 15 2024 | $72,500 | Follow-up |
| Diego Morales | TerraFusion Inc | 2024-03-10 | $38,900 | New Lead |
| Sarah Chen | Acme Corp | Mar 12 2024 | $56,200 | Qualified |
| Rajiv Mehta | CloudPulse Ltd | 44985 | $89,100 | Proposal Sent |
| Elena Vargas | Strata Dynamics | 2024/03/18 | $63,400 | Follow-up |
| Diego Morales | TerraFusion Inc | 2024-03-10 | $38,900 | New Lead |
The Challenge
We needed to:
- Deduplicate rows where Lead Name + Company matched (not just name)
- Convert all Date Contacted entries to proper Excel dates in column C (A2:A9)
- Sort by date descending, then by revenue descending within same date
- Add an auto-incrementing ID in column A (starting at 1001)
Here’s what makes it tricky: ChatGPT doesn’t know your local date settings. Feed it “Mar 15 2024” and it might suggest =DATEVALUE("Mar 15 2024") — which fails in non-English locales. And if you paste raw CSV into Excel without Text Import Wizard, Excel auto-converts some dates and breaks others. I saw this happen twice last week — one person lost 3 hours reformatting.
Walking Through It
I asked ChatGPT: “Generate Excel formulas for columns B through F that will clean this dataset. Assume raw data starts at A1:E8. Output only formulas — no explanation.”
It gave me this — and yes, it worked:
| Column | Formula | Notes |
|---|---|---|
| B2 (ID) | =1001+ROW()-2 |
Starts at 1001 in row 2 |
| C2 (Clean Date) | =IF(ISNUMBER(A2),A2,IF(ISNUMBER(DATEVALUE(SUBSTITUTE(A2,"/","-"))),DATEVALUE(SUBSTITUTE(A2,"/","-")),DATEVALUE(SUBSTITUTE(A2," ","-")))) |
Handles 44991, "2024-03-10", "Mar 15 2024", "2024/03/18" |
| D2 (Revenue) | =SUBSTITUTE(SUBSTITUTE(E2,"$",""),",","")*1 |
Strips $ and commas, converts to number |
I pasted those formulas into F1:I1, then dragged down to row 9. Then — here’s the counterintuitive part — I didn’t copy-paste values right away. I used Alt + H + V + V (Paste Values) only after sorting. Why? Because sorting with formulas active recalculates IDs. So I copied F2:I9 → Paste Special → Values into F2:I9 first, then sorted.
For deduplication: I selected A1:E9, went to Data → Remove Duplicates → unchecked “My data has headers”, then checked only “Lead Name” and “Company”. Wait — no. That’s wrong. I un-checked headers, but then Excel treated row 1 as data. Big mistake. Fixed it by selecting A2:E9 instead. Took 12 seconds.
The Result
After sorting by Clean Date (descending), then Revenue (descending), and adding IDs — here’s what landed in A1:F6:
| ID | Lead Name | Company | Date Contacted | Revenue Potential | Status |
|---|---|---|---|---|---|
| 1001 | Elena Vargas | Strata Dynamics | 2024-03-18 | 63400 | Follow-up |
| 1002 | Jin Lee | Nexus Labs | 2024-03-15 | 72500 | Follow-up |
| 1003 | Sarah Chen | Acme Corp | 2024-03-12 | 56200 | Qualified |
| 1004 | Rajiv Mehta | CloudPulse Ltd | 2024-03-09 | 89100 | Proposal Sent |
| 1005 | Diego Morales | TerraFusion Inc | 2024-03-10 | 38900 | New Lead |
What Could Go Wrong
Mistake #1: Pasting ChatGPT’s table as plain text into Excel
It looks fine until you try to sort — Excel treats the whole block as text in column A. You’ll get “#VALUE!” when applying DATEVALUE. Fix: Paste into Notepad first, then copy-paste into Excel using Alt + D + E (Text Import Wizard) and choose Delimited → Tab.
Mistake #2: Assuming ChatGPT knows your regional settings
It suggested =DATEVALUE("03/15/2024") — which fails in Germany (where dd/mm/yyyy is default). Always test date formulas on one cell first. Use =TEXT(C2,"yyyy-mm-dd") to verify format before sorting.
Mistake #3: Forgetting to freeze panes before scrolling
When your cleaned data hits 200+ rows and you’re checking IDs, you lose track of headers. Hit Alt + W + F before reviewing — saves 3 minutes per scroll session.
So — can ChatGPT create Excel files? No. But it *can* generate production-ready formulas, structure logic, and even write full Power Query M code if you ask precisely. The bottleneck isn’t the AI. It’s knowing which cells to protect, which shortcuts to use, and when to stop trusting the first formula it spits out.
| Method | Time for 10K Rows | Accuracy | Difficulty |
|---|---|---|---|
| Manual cleanup (no AI) | 42 min | 82% | Medium |
| ChatGPT + Paste Formulas | 6 min | 97% | Low |
| Power Query (no AI) | 9 min | 100% | High |
| ChatGPT + Power Query M | 7 min | 100% | Medium |
