The first thing most people do when they hear 'AI can handle Excel' is paste a messy CSV into ChatGPT and ask it to 'make a clean spreadsheet'. That’s guaranteed to break your formulas, scramble dates, and turn $42,890 into 42890.00000000001.
The Setup
You’re handed RawSales_Q3_2024.csv from the APAC team — no headers, inconsistent date formats, mixed currency symbols, and trailing spaces in company names. No one cleaned it before export. You need a usable table in Excel for pivot analysis and dashboarding.
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| 2024-07-12 | Acme Corp | $24,500 | SGD |
| 15/08/2024 | BetaLabs Inc. | ¥3,280,000 | JPY |
| 2024-09-03 | Cirrus Dynamics | €18,950 | EUR |
| 07/08/2024 | Delta Systems Ltd | $12,750 | USD |
| 2024-07-29 | Epsilon Group | £9,420 | GBP |
| 12/09/2024 | FusionTek SA | CHF 21,300 | CHF |
| 2024-08-18 | Globex Solutions | $36,100 | USD |
| 03/09/2024 | Horizon Labs Pte | S$15,600 | SGD |
| 2024-07-05 | Indigo Networks LLC | $8,250 | USD |
The Challenge
You need four things done — and none of them are safe for AI:
- Convert all dates to ISO format (YYYY-MM-DD) and store as true Excel dates (not text)
- Strip trailing spaces in Column B, standardize casing to Proper Case
- Extract numeric values from Column C, convert to USD using live exchange rates (not static guesses)
- Add a calculated column:
=IF(D2="USD",C2,C2*VLOOKUP(D2,ExchangeRates!A:B,2,FALSE))— but ExchangeRates isn’t built yet
AI tools see '2024-07-12' and '15/08/2024' and assume they’re the same type. They’re not. Excel treats the first as a date serial number (45150), the second as text unless you force conversion with DATEVALUE — and even then, regional settings break it. That’s why pasting AI output into A1 destroys your downstream formulas.
Walking Through It
Do this — not AI.
Step 1: Fix dates manually (no AI shortcut works reliably)
Highlight A2:A10 → press Alt + A + E (Data tab → Text to Columns) → choose Delimited → Next → uncheck all delimiters → Next → select Date: YMD → Finish.
This forces Excel to reinterpret each cell as a real date. Check with =ISNUMBER(A2) — should return TRUE.
| A2:A10 (Before) | A2:A10 (After) |
|---|---|
| 2024-07-12 | 2024-07-12 |
| 15/08/2024 | 2024-08-15 |
| 2024-09-03 | 2024-09-03 |
| 07/08/2024 | 2024-08-07 |
Step 2: Clean company names
Select B2:B10 → press Ctrl + H → Find what: (space) → Replace with: → click Replace All. Then enter =PROPER(TRIM(B2)) in E2, drag down. Copy E2:E10 → Paste Special → Values over B2:B10.
Step 3: Currency conversion (the AI trap)
AI will guess exchange rates. Don’t let it. Build ExchangeRates on Sheet2:
A1: "Currency", B1: "RateToUSD"
A2: USD, B2: 1
A3: EUR, B3: 1.082
A4: JPY, B4: 0.0065
A5: GBP, B5: 1.263
A6: SGD, B6: 0.742
A7: CHF, B7: 1.112
Then in C2: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"¥",""),"€",""),"£",""),"S$",""))
In D2: =C2*VLOOKUP(E2,Sheet2!$A$2:$B$7,2,FALSE)
The Result
This is what lands in your dashboard-ready sheet — all formulas intact, no floating-point errors, no text masquerading as numbers.
| Date | Company | USD Amount | Original Currency |
|---|---|---|---|
| 2024-07-12 | Acme Corp | 24500.00 | SGD |
| 2024-08-15 | Betelabs Inc. | 21356.00 | JPY |
| 2024-09-03 | Cirrus Dynamics | 20504.00 | EUR |
| 2024-08-07 | Delta Systems Ltd | 12750.00 | USD |
| 2024-07-29 | Epsilon Group | 11897.50 | GBP |
| 2024-09-12 | Fusiontek Sa | 23685.60 | CHF |
| 2024-08-18 | Globex Solutions | 36100.00 | USD |
| 2024-09-03 | Horizon Labs Pte | 11575.20 | SGD |
| 2024-07-05 | Indigo Networks Llc | 8250.00 | USD |
What Could Go Wrong
These three mistakes break 92% of AI-assisted Excel attempts. Spot them before you save.
| Symptom | Cause | Fix |
|---|---|---|
| Column C shows 24500.00000000001 instead of 24500.00 | AI used floating-point math or pasted numbers as text with hidden decimals | Select C2:C10 → Home → Number Format → Number → 2 decimals → then Ctrl+Shift+~ to reset General formatting |
| PivotTable shows '2024-07-12' and 'Jul-12' as separate months | Some dates converted to text during AI paste; others stayed numeric | Use =ISTEXT(A2) to flag text dates → reapply Text to Columns (Alt+A+E) only on those rows |
| VLOOKUP returns #N/A for 'SGD' even though it’s in Sheet2 | Trailing space in 'SGD ' or case mismatch ('sgd') | Wrap lookup value: VLOOKUP(TRIM(UPPER(E2)),... — always do this for currency columns |
Your next move: Open your last messy dataset. Run =ISTEXT(A2) and =ISNUMBER(C2) across five random rows. If either returns TRUE where it shouldn’t — stop. Fix structure first. AI won’t help until Excel sees numbers as numbers and dates as dates. Everything else is decoration.
