Operations · 10 min

Clean up a messy Excel export with Copilot

Excel, Copilot

Short answer: Check that Formulas > Calculation Options is set to Automatic, format the data as a table, then open Copilot in the lower-right corner. Use plan mode to see the steps first, or edit mode to let it change the sheet. Check the result on a few rows.

Last checked against Microsoft docs: Oct 20, 2026

You've seen people upload a messy CSV to ChatGPT and get it cleaned. Here's how in Copilot: open the file in Excel, make it a table, and ask Copilot to clean it in place. Your sheet stays editable.

A common situation (composite)

Every Monday the system spits out the same broken file.
Merged cells, blank headers, dates as text.
I fix it by hand every time.

What you get

Data: an Excel export saved as .xlsx.
Does: reshapes and flags problems in your sheet.
Output: a clean table plus a list of what changed.
How often: each export.

Before you start

Needs: M365 Copilot (Basic) or M365 Copilot (Premium), with Calculation Options set to Automatic. Not sure what you have? Start here.
Where: Excel, Copilot.
Access: Copilot only uses content you already have permission to open. Use only information your company allows in Copilot.

The steps

Save the file as .xlsx and set Formulas > Calculation Options to Automatic. AutoSave can be on or off.
Remove merged cells and extra header rows, then select Home > Format as Table.
Open Copilot and switch to plan mode. Paste the prompt.
Review the plan, run it in edit mode, then spot-check ten rows.

Copy this prompt

Clean this table:
- Split [column] into [first name] and [last name]
- Make [date column] a real date
- Trim spaces in all text columns
- Highlight duplicate rows in [ID column]
- Add a column "Issue" that says what's missing in each row (blank email, blank date, etc.)

Show me the plan before changing anything. Then list every change you made.

(Prompt from stuckwithcopilot.com/guides/excel-data-cleanup/)

What a good result looks like

Row count before and after matches unless you asked to remove rows. Spot-check ten rows.

If a feature is missing

Copilot edits the sheet only when Calculation Options are set to Automatic (Formulas > Calculation Options). AutoSave can be on or off. Data needs one header row, unique headers and no merged cells.

Make it smarter

Keep a short "What it got wrong" note. Paste it above the prompt next time, like "Don't invent dates".

Not sure what to ask? Copy the quick note for your Copilot admin.

Questions people ask

Clean up a messy Excel export with Copilot?

Check that Formulas > Calculation Options is set to Automatic, format the data as a table, then open Copilot in the lower-right corner. Use plan mode to see the steps first, or edit mode to let it change the sheet. Check the result on a few rows.

What do I need for this?

M365 Copilot (Basic) or M365 Copilot (Premium). Copilot edits the sheet only when Calculation Options are set to Automatic (Formulas > Calculation Options). AutoSave can be on or off. Data needs one header row, unique headers and no merged cells.

Next step

Try it once, then make it yours.

Run the prompt on one real task this week. Then pick your next workflow.

See the workflow library ↗

Cite or share this guide

Clean up a messy Excel export with Copilot. Stuck with Copilot. https://stuckwithcopilot.com/guides/excel-data-cleanup/ Last checked Oct 20, 2026.

One Copilot idea a week

One idea a week that makes your job easier, makes you smarter, and gets your Copilot doing more for you. Something genuinely cool, not a hack. Built for people in sales, marketing and ops.

Official references

Last checked against Microsoft docs: Oct 20, 2026.