Start herebeginner15 min
Clean a Messy CSV and Build a PivotTable in Plain English (with AI)
The real skill is describing the result you want in plain English and letting AI do the work. Paste your messy rows into Claude or ChatGPT and ask it to "remove duplicates, split the full-name column, and summarize revenue by month." It returns the cleanup steps and exact formulas to drop into Excel or Sheets — no macros, nothing to install.
Clean a Messy CSV and Build a PivotTable in Plain English (with AI)
The real skill is describing the result you want in plain English and letting AI do the work. Paste your messy rows into Claude or ChatGPT and ask it to "remove duplicates, split the full-name column, and summarize revenue by month." It returns the cleanup steps and exact formulas to drop into Excel or Sheets — no macros, nothing to install.
The skill is describing, not formulas
Forget memorizing `VLOOKUP` and `TEXTSPLIT`. The smart move is **describing the result you want** and letting an AI translate it into the exact steps and formulas. "Split the full-name column into first and last" beats hunting through menus. The tool you paste into is interchangeable; what matters is learning to say precisely what "clean" and "summarized" mean for your data.
Format as a Table first
Before anything else, open your CSV and turn the range into a Table. In Excel, click in the data and press **Ctrl+T**; Google Sheets treats a clean header row the same way. This one move makes everything downstream more reliable, because your columns become **named fields** — "Email", "Revenue" — instead of a loose blob of cells. AI-written formulas reference those names and just work.
Describe the mess to AI
Open [Claude](/tool/claude/) or [ChatGPT](/tool/chatgpt/) and describe the cleanup in normal words. Paste a few anonymized sample rows, or just list your column names so it knows the shape: ``` My sheet has columns: Full Name, Email, Country, Revenue, Date. Give me the exact steps and formulas to: - remove duplicate rows - delete rows where Email is blank - split Full Name into First and Last - standardize Country to two-letter codes ``` It returns numbered steps plus the precise formulas to paste in.
Ask for the summary you need
Now get the analysis the same way — by describing the outcome, not the formula: ``` Then show me how to build a PivotTable of total Revenue by Month, with a column for month-over-month change. ``` Or hand it the numbers and ask, "What are the three most interesting trends here?" You go from raw export to a **boss-ready summary** without writing a single formula from scratch — just pasting in the ones it hands you.
Apply on a copy, then check
Work on a duplicate of the file the first few times so mistakes cost nothing. Paste each formula where the AI tells you, then sanity-check the result against what you expected. Did the row count drop after deduping? Does the monthly total look right? **Ctrl+Z** reverses anything instantly. This review habit keeps the analysis trustworthy — and teaches you the formulas by reading them in context.
Zero setup? Built-in buttons
If you only need quick charts and have no time to prompt, both spreadsheets hide a one-click helper: **Analyze Data** in Excel, **Explore** in Google Sheets. Select your table, click it, and you get suggested charts, totals, and PivotTables automatically. Less flexible than describing exactly what you want to an AI, but free, built in, and plenty for everyday reporting.
Turn it into a habit
Make the win repeatable. Once the PivotTable is built, save the workbook as a **template** and just paste next month's data in. Stuck on one formula? Ask the AI "write a formula that flags any order over 30 days old" instead of Googling syntax. The whole approach is tool-agnostic — the same plain-English workflow runs identically whether your data lives in Excel or Sheets.
Clean one column now
Pick the single messiest column in a real export — a `Full Name` to split or a `Country` to standardize. Describe just that one fix to Claude or ChatGPT, paste its formula onto a copy of the file, and watch the column tidy itself. That small win is the whole skill in miniature.
Try this now
Your turn: open claude and set up the first step. Just do step one now — the rest takes minutes. Save this guide to pick up where you left off.
FAQ
Do I need to know Excel formulas for this?
No, that is the point. You describe the result you want ("split this column", "remove blanks", "total sales by region") and the AI writes the exact formulas for you to paste in. Reading what it wrote is a great way to learn the formulas over time, but you do not need them to start.
Is it safe to paste company financial data into a chatbot?
Follow your company data policy first. The safe habit is to anonymize: strip names, emails, and IDs, or describe the columns and paste a few dummy rows instead of the real ones. You usually only need the structure and a sample for the AI to write correct steps and formulas you then apply locally.
Will it mess up my original data?
It cannot, because the AI only gives you instructions and formulas, you apply them yourself in a copy of the file. Work on a copy the first few times and use undo (Ctrl+Z) freely. Formatting your range as a Table first also makes every change cleaner and easier to track.
What if I just want quick charts with no AI at all?
Both Excel and Google Sheets have a built-in button (Analyze Data in Excel, Explore in Sheets) that suggests charts, totals, and PivotTables automatically. It is less flexible than describing what you want to an AI, but it is free, built in, and covers a lot of everyday reporting.