How to Clean Data with Copilot in Excel

Data cleaning is one of the most time-consuming parts of working with spreadsheets, and one of the best uses of Copilot in Excel — because it can scan your whole dataset at once and surface problems that would take hours to find manually. Here's how to actually use it.

Setup: format as a table first

As with all Copilot features in Excel, data cleaning works best on a properly formatted Table with clear column headers. Select your data and go to Insert > Table if you haven't already. Make sure the file is saved to OneDrive or SharePoint with AutoSave on.

Finding errors and inconsistencies

Start by asking Copilot for a general health check on your data:

• Are there any errors or inconsistencies in this data?
• Check this spreadsheet for data quality issues
• What problems can you find in this dataset?
• Are there any cells with errors in the Status column?

Copilot will scan the data and report what it finds — typos, inconsistent capitalization, mixed formats, formula errors, and similar issues. It often spots things you wouldn't notice scrolling through manually.

Finding and handling missing values

• Which rows have missing values?
• Highlight any cells in the Email column that are empty
• How many rows are missing a value in the Region column?
• Flag all rows where the Phone Number field is blank

Fixing inconsistent formatting

Inconsistent date formats, mixed capitalisation, and extra spaces are common data quality problems that break lookups and filters. Copilot can fix these:

• Standardize the date format in the Order Date column to DD/MM/YYYY
• Convert all text in the Country column to proper case
• Remove extra spaces from the Name column
• Make all values in the Status column consistent — some say "Active" and some say "active"
• Format all values in the Phone column as (XXX) XXX-XXXX

Finding duplicates

• Are there any duplicate rows in this table?
• Find duplicate entries in the Customer ID column
• Highlight any rows where the email address appears more than once
• Show me which order numbers appear more than once

Copilot identifies duplicates and can highlight them for you to review. It won't automatically delete rows — it flags them so you can decide which to keep, which is the safer approach with real data.

Adding a new column to flag data quality issues

For ongoing data quality monitoring, ask Copilot to add a helper column:

• Add a column that flags any row where the Revenue is negative as "Check"
• Add a Validation column that marks rows with missing email addresses as "Incomplete"
• Add a column that checks whether the email address in column C contains an @ symbol

Splitting and combining columns

• Split the Full Name column into separate First Name and Last Name columns
• Combine the Street, City, and Postcode columns into a single Address column
• Extract just the domain name from the email addresses in column D
• Split the date and time in column A into two separate columns

Always verify before applying to the full dataset

Copilot's data cleaning suggestions show as previews before being applied. Review what it's about to do — especially for bulk find-and-replace operations — on a sample of rows before accepting. Use Ctrl+Z to undo anything that doesn't look right. For large or business-critical datasets, it's worth keeping a backup copy before running bulk cleaning operations.

The bottom line

Data cleaning is where Copilot saves the most time relative to doing it manually — it can scan thousands of rows instantly for patterns that would take hours to catch by hand. The combination of asking for a health check first, then drilling into specific issues, gets clean data faster than any other method short of writing complex validation formulas yourself.