How to use Microsoft Copilot to automate Excel data cleaning

Before letting Copilot work on your data, make sure your file is set up properly. AI is powerful, but it needs a structured environment to understand the columns, rows, and context of your spreadsheet.

Access and subscriptions

To use Copilot in Excel, you need a Microsoft 365 Copilot subscription or a Copilot Pro license. Your Excel workbook also needs to be connected to OneDrive or SharePoint because Copilot uses cloud-based computation. Keep AutoSave turned on so the workbook is synced.

Convert your range into an Excel Table

Copilot works best with official Excel Tables, not loose grids of cells. Click inside your data range, press Ctrl + T, confirm that My table has headers is checked, and click OK. This gives Copilot a clear map of your dataset.

Once your workbook is ready, open the Copilot pane from the Home tab of the Excel ribbon. Data cleaning usually follows a sequence: identifying structural issues, fixing formatting, removing duplicate records, and filling missing values.

1. Remove duplicates and extra spaces

Duplicate records and accidental trailing spaces are common in raw data. They may look harmless, but they can break formulas like VLOOKUP, XLOOKUP, and lookup-based dashboards.

Look at the Customer ID column and highlight any duplicate rows.

Remove all duplicate rows based on the Customer ID column.

Clean up the text "text-purple-400">in the Product Name column by removing extra spaces at the beginning and end.

Copilot can scan the relevant column, preview duplicate rows, apply conditional formatting, or remove duplicate records based on your instruction.

2. Standardize inconsistent text

If names or product labels are written in a mix of uppercase, lowercase, and sentence case, the sheet looks unprofessional and becomes harder to sort or group.

Format the names "text-purple-400">in the Employee Name column so that only the first letter of each word is capitalized.

Copilot can recognize that this requires proper case formatting and may suggest a new calculated column. Review the preview, then insert the column if it looks correct.

Yes. This is where Copilot starts to feel like a real assistant. Human data entry often creates inconsistent formats, misspellings, wrong data types, and values in the wrong columns.

Catch outliers and mismatched formats

Suppose a Price column should contain numbers, but a few rows contain values like TBD or Pending. Or maybe dates appear in multiple formats across the same column. Copilot can help you find these anomalies.

Scan the Price column and highlight any cells that do not contain numbers.

Replace any text values "text-purple-400">in the Price column "text-purple-400">with a blank space.

Standardize all dates "text-purple-400">in the Invoice Date column to the MM/DD/YYYY format.

Handle missing data intelligently

Blank cells can damage analysis, charts, and summary reports. Copilot can fill blanks based on simple replacement rules or more contextual business logic.

Find all blank cells "text-purple-400">in the Quantity column and fill them "text-purple-400">with 0.

For any missing values "text-purple-400">in the Regional Sales column, fill them "text-purple-400">with the average value of that specific region.

Cleaning data is not only about deleting errors. Often you need to restructure a dataset so it can support reporting, filtering, dashboards, and analysis. Copilot can generate calculated columns from simple descriptions.

Split and combine columns without Flash Fill

If a Full Address column contains street, city, and zip code in one field, you can ask Copilot to extract the part you need into a new column.

Create a new column that extracts the zip code "text-purple-400">from the Full Address column.

Apply conditional flags

Sometimes data cleaning means categorizing records so they are easier to filter, audit, or review. Copilot can translate business rules into IF-style logic.

Add a column named Audit Status. If the Order Amount is greater than $10,000, set the value to Review Required. Otherwise, set it to Approved.

Working with AI is a conversational process. To get accurate results and protect your data, use clear prompts and review each proposed transformation carefully.