How to Use Copilot in Excel to Clean Messy Data

Messy spreadsheets waste time and can make a report wrong. This practical guide explains how to use Copilot in Excel to clean inconsistent text, dates, numbers, duplicates and missing values without losing the original data. It is written for analysts, office teams and small businesses in the US, UK, Canada and Australia.

Why messy Excel data breaks reports

A spreadsheet may look fine at first glance and still contain hidden errors. A sales date may be stored as text. One customer may appear as “North Star Ltd” in one row and “Northstar Ltd.” in another. A blank cell may mean “unknown” rather than zero. These small issues can change a total, stop a lookup from matching or make a PivotTable misleading. Copilot can help you spot patterns, but it is still your job to decide what each value should mean. This guide shows a safe workflow for cleaning Excel data with Copilot while keeping a copy of the source.

What Copilot can do in Excel today

Microsoft documents built-in Copilot skills for data cleanup and repeatable workbook tasks. The clean-data skill can help locate and correct issues that affect analysis. Microsoft also describes newer “Edit with Copilot” experiences that work with tables, formulas and, in supported plans, Python. Availability can differ by license, app version and organizational settings. Do not assume a button or prompt available in a video tutorial is present in your account. Start with Microsoft’s current Copilot in Excel guide and check which tools your account exposes.

Before you ask Copilot to clean anything

Save a copy of the workbook with a clear name such as “sales-raw-backup.xlsx.” Keep one untouched raw-data sheet. If the file contains customer names, financial records or health information, follow your organization’s rules for AI-assisted processing. Use a small, anonymized sample when learning the workflow. Turn the dataset into an Excel Table where possible, with one header row and consistent columns. Remove decorative merged cells from the data range, not from a presentation-only report. Decide which columns are identifiers, dates, categories and numeric measures.

A simple messy-data example

Imagine a table with Order ID, Order Date, Customer, Country, Amount and Status. In one row, the amount is “$1,200” as text. In another, it is 1200 as a number. Country values alternate between “US,” “USA” and “United States.” A duplicate Order ID appears twice, but one entry is a refund. You should not delete that second row until you know whether it is a valid transaction. The goal is to standardize formats without losing business meaning. Write down these rules before you ask Copilot to make edits.

Step 1: Find inconsistent formats

Open a copy of your workbook and select the relevant table. In the Copilot pane, describe the columns and ask for a review rather than immediate changes. A useful prompt is: “Inspect this sales table for values stored with inconsistent data types, extra spaces, inconsistent country names and invalid dates. List the affected columns and show examples before editing.” Review the findings. Copilot may identify text-formatted numbers or unusual date strings, but any correction must match your source system’s conventions. If the pane cannot directly edit the table, ask for an Excel formula or Power Query approach instead.

Step 2: Standardize text without changing meaning

Leading and trailing spaces can prevent exact matches. In a helper column, Excel’s TRIM function can remove ordinary extra spaces, while CLEAN can remove many nonprinting characters. Some imported data contains nonbreaking spaces that need a separate SUBSTITUTE step. Ask Copilot to propose a formula, then test it on sample rows. For country names, create an approved mapping table, such as “USA” and “US” to “United States,” instead of letting a model guess every abbreviation. Never change legal names or product codes based only on similarity.

Step 3: Convert dates and numbers carefully

A number stored as text may not add correctly. Dates can be more dangerous: “04/05/2026” could mean April 5 or May 4 depending on locale. Tell Copilot the expected date convention and ask for a conversion plan. Test the output on dates where day and month are easy to distinguish. Use Excel’s number formats only after confirming the underlying cell value is correct. For currency, separate the numeric amount from a currency code when your data covers the United States, United Kingdom, Canada or Australia. A dollar sign alone does not identify which dollar currency was used.

Step 4: Detect duplicates without deleting valid records

Ask Copilot: “Show potential duplicate Order IDs, but do not remove rows. Include the amount, date and status for each match.” Compare the resulting list with the business rule. Two lines may share an order number because one is a return, installment or correction. A duplicate email address is not always a duplicate customer. Use conditional formatting or a COUNTIF helper column to mark candidates, then decide which records are truly redundant. Keep a separate log of every removed row with its original identifier.

Step 5: Handle blanks, errors and outliers

Blank values need different treatment by column. A missing phone number may be acceptable; a missing invoice amount may require investigation. Do not replace every blank with zero. Ask Copilot to count blanks and errors by column and suggest questions to ask the data owner. For outliers, distinguish a typo from a genuine high-value order. A value ten times larger than usual deserves review, not automatic deletion. If a lookup returns #N/A, check spelling, data types and whether the lookup key actually exists.

Step 6: Use a repeatable checklist

After reviewing Copilot’s proposed changes, keep a record of the cleanup rules. This matters when the next week’s report arrives. A manual cleanup that cannot be repeated often creates new inconsistencies. Put recurring fixes into formulas or Power Query steps, and document any exceptions that still need a person. Use Copilot to explain a formula in plain English, but verify it against at least three known examples. Compare row counts, unique IDs and totals before and after the cleanup.

How to verify the clean result

Build a small validation sheet with the original row count, cleaned row count, distinct order IDs, blank counts and key numeric totals. For example, if an amount column originally totals $42,800, a formatting-only change should not silently change that sum. A change in totals may be legitimate if text values previously were excluded, but you must be able to explain the difference. Recheck a random sample of rows and every unusual record. Save the cleaned workbook separately and keep the original available for rollback.

When to use Power Query instead

If you receive the same kind of CSV each week, Power Query is often the better long-term place for stable transformations. You can import data, change data types, trim text, remove errors, merge reference tables and refresh the steps when the next file arrives. Copilot can help you plan or explain the steps, but the actual transformation should be visible and reproducible. Avoid mixing one-off manual edits into a pipeline unless they are documented. For more detail, see our related guide to AI spreadsheet analysis and the upcoming guide on importing data with Power Query.

Common mistakes to avoid

Do not apply a global find-and-replace to values with different meanings. Do not delete a row only because two names look similar. Do not convert ambiguous dates without knowing the source locale. Do not use AI output as the only record of what changed. And do not present an AI-generated summary as an audit of the underlying data. A good cleanup process is cautious, measurable and easy for another person to reproduce.

FAQs

**Can Copilot clean an Excel table automatically?** In supported experiences, Copilot can help find and correct issues. Review the proposed changes and test the result, especially for important data.

**Do I need Microsoft 365 Copilot?** Features depend on your subscription and the Excel experience available to your account. Check Microsoft’s current licensing and feature documentation.

**Will Copilot remove all duplicates?** You should first define what a duplicate means for your data. Matching values alone are not enough.

**Is Power Query better for repeat imports?** Often yes. It records repeatable steps so the next file can be processed consistently.

**Can I use Copilot for financial data?** Follow your organization’s privacy and security policies, use approved accounts and independently validate calculations.

Useful official resources

• Microsoft: Copilot in Excel built-in skills
• Microsoft: repeatable Copilot skills
• Microsoft: Power Query data filtering

Related GetTechDose guides

Read our ChatGPT for Excel guide and AI spreadsheet analysis comparison for more ways to check formulas and reports.