Clean a Messy CSV and Build a PivotTable with Excel Copilot, in Plain English
Updated June 3, 2026 · #m365-copilot#excel-automation
The short answer
Open the file in Excel, format it as a Table, then tell Microsoft 365 Copilot in plain English: "remove duplicate rows, split the full-name column, and make a PivotTable of revenue by month." Copilot does it and shows the steps so you can check its work. No formulas or macros, nothing to install. If you don’t have a Copilot license, Excel’s free "Analyze Data" still builds basic charts and insights.
Which tool should you use?
Use Copilot in Excel (the licensed M365 Copilot) if your company has it. It cleans data and builds analysis from plain English, right inside the app you already have.
- switch Excel "Analyze Data" (free), you don’t have a Copilot license. It still suggests charts and basic insights for free
- switch Gemini in Google Sheets, your data lives in Google Sheets, Gemini does the same plain-English cleanup there
Cleaning a messy spreadsheet export is the kind of task that quietly eats an afternoon: dedupe the rows, fix the inconsistent columns, build the summary your boss actually asked for. Copilot in Excel does it from a sentence, and it’s already sitting in the ribbon if your company has it. No formulas, no macros, no install.
What you’ll do
- Get your messy data into Excel and format it as a Table.
- Ask Copilot, in plain English, to clean it.
- Ask Copilot to summarize it (a PivotTable or chart).
- Review what it did, and learn from it.
Step 1, Format the data as a Table
Open your CSV in Excel. Click anywhere in the data and press Ctrl+T to turn it into a Table. This one step makes Copilot dramatically more reliable, because it now understands your columns as named fields rather than a loose blob of cells.
Step 2, Tell Copilot to clean it
Open Copilot from the Home tab and describe the mess in normal words. For example:
“Remove duplicate rows, delete rows where Email is blank, split the ‘Full Name’ column into First and Last, and standardise the Country column to two-letter codes.”
Copilot makes the changes and tells you what it did. Read that summary, it’s your check that it understood you correctly.
Step 3, Ask for the summary you actually need
Now get the analysis:
“Create a PivotTable showing total Revenue by Month, and add a column for month-over-month change.”
Or just:
“What are the three most interesting trends in this data?”
Copilot builds the PivotTable (or chart) on a new sheet. You went from raw export to a boss-ready summary without writing a single formula.
Step 4, Review and learn
Click into the cells Copilot created and you’ll see the real formulas and PivotTable fields behind them. Over a few weeks this is how you actually learn Excel, by reading what the AI wrote for tasks you understand.
No Copilot license? Use Analyze Data (free)
If your company hasn’t bought Copilot seats, you’re not stuck. Select your table and click Analyze Data on the Home tab. Excel suggests charts, totals, and PivotTables automatically. Not conversational, but free, built-in, and enough for most everyday reporting.
Make it yours
- Recurring report: once Copilot builds the PivotTable, save the workbook as a template and just paste next month’s data in.
- Plain-English formulas: stuck on a formula? Ask Copilot “write a formula that flags any order over 30 days old” instead of Googling syntax.
- Google shop? The exact same approach works with Gemini in Google Sheets.
This is the highest-leverage AI skill for anyone who lives in spreadsheets. Want a worked example on your real (anonymized) data, or a video walkthrough? Add it to the request queue.
Frequently asked
Do I need to know Excel formulas for this?
No, that’s the point. You describe the result you want ("split this column", "remove blanks", "total sales by region") and Copilot writes the formulas or builds the PivotTable for you. Reviewing what it did is a great way to learn the formulas over time, but you don’t need them to start.
What if my company hasn’t bought Copilot?
Use the free "Analyze Data" button (formerly Ideas) on the Home tab. It suggests charts, trends, and PivotTables automatically. It’s less conversational than Copilot but covers a lot of everyday analysis with no license and nothing to install.
Is it safe to use Copilot on company financial data?
Copilot in Excel works on the file you already have open under your work account and is covered by Microsoft’s enterprise data protections, it’s designed for exactly this. As always, follow your company’s data policy, but this is a sanctioned, in-app tool, not a third-party upload.
Will it mess up my original data?
Work on a copy the first few times, and use Excel’s undo (Ctrl+Z) freely, Copilot’s changes are normal edits you can reverse. Once you trust it, you’ll let it work directly. Formatting your range as a Table first also makes its changes cleaner and easier to track.
Want this one done for you?
Get the enriched version with the importable workflow file, or have me build and hand over the whole thing, tested, with a walkthrough.