Use Excel's AI to Clean Messy ERP Data Exports

What This Does

When you export data from SAP, Oracle, or any ERP system, it almost never comes out clean. Copilot in Excel can interpret what you need in plain English and apply Power Query transformations (removing blank rows, splitting columns, standardizing formats) without you needing to know Power Query syntax.

Before You Start

• You have Microsoft 365 with a Copilot license (M365 Copilot add-on)
• You have your ERP export open in Excel (the messy file)
• You're logged into Excel with your work Microsoft account

Steps

1. Open the messy data file in Excel

Open your ERP export. Don't try to clean it manually first. Let Copilot see the raw version so it understands what needs to be fixed.

1. Note the problems: What's messy? Merged cells? Account code and description in one column? Extra header rows? Inconsistent date formats?
2. Make a mental list of 2–3 things you need cleaned before you start

What you should see: The raw, unformatted export, possibly with merged cells, subtotal rows, or inconsistent formatting. Troubleshooting: If the file opens with data in only column A (common with .txt or .csv exports), use Data → Text to Columns first to split it into proper columns before opening Copilot.

2. Open the Copilot pane

1. Click the Home tab in the Excel ribbon
2. Click the Copilot button (sparkle icon) in the top-right ribbon area
3. The Copilot sidebar opens on the right

3. Describe the cleaning task in plain English

Type what you need. Be specific about what's wrong and what you want instead.

Useful cleaning prompts:

• "Remove all blank rows from this data"
• "Split the 'Account' column (format: 'XXXXX - Description'). Put the code in column A and the description in a new column B"
• "Remove all rows where column A says 'Total' or 'Subtotal'"
• "Convert all dates in column C to MM/DD/YYYY format"
• "Replace all values of '#N/A' in column D with 0"

4. Review and apply the changes

1. Copilot shows a preview of the transformation or the formula/Power Query step it will apply
2. Click "Apply" if the preview looks right
3. Verify the cleaned data looks correct before proceeding with your analysis
4. Save the cleaned file with a new name (e.g., "GLExport_March_clean.xlsx") to preserve the original

What you should see: The data cleaned per your instructions, with consistent formats, no blank rows, and split columns. Troubleshooting: If Copilot misunderstands your request, be more explicit: "Split column A at the hyphen character" instead of "separate the code from the description."

Real Example

Scenario: You export the March GL actuals from SAP. The export has 2,500 rows. The account code and account description are merged into one column (format: "600100 - Travel & Entertainment"). There are 15 subtotal rows inserted automatically. Dates are in YYYYMMDD format. You need this cleaned before you can run your variance analysis.

What you type: Three separate requests in sequence:

1. "Split column A at the hyphen. Put everything before the hyphen (trimmed) into a new column A called 'Account Code' and everything after (trimmed) into a new column B called 'Account Description'"
2. "Delete all rows where the first column contains only numbers with no description (the subtotal rows)"
3. "Convert column G dates from YYYYMMDD format to MM/DD/YYYY"

What you get: A clean, analysis-ready table in about 5 minutes. Work that previously took 30–45 minutes of manual formatting.

Tips

• Do one cleaning step at a time rather than asking for everything at once. It's easier to verify each step before moving to the next.
• If Copilot adds a Power Query step, check the Applied Steps pane (Data → Queries & Connections) to see what it did. This teaches you the underlying logic.
• Save your cleaning steps as a Power Query template so you can apply the same cleanup automatically next month

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.