Streamline Excel Data Cleaning with Copilot: 7 Common Issues Fixed in One Click

Messy data is rarely ruined by one big error. Usually, it is a pileup of small ones: names typed in different styles, dates entered in mixed formats, extra spaces, duplicate rows, and text split awkwardly across columns. Microsoft 365 Copilot in Excel makes data cleaning dramatically faster than manual formulas, Find & Replace, or Power Query. The dedicated Clean Data feature gives you one-click AI suggestions for common issues like extra spaces, inconsistent capitalization, and number formatting. For precise control and specific cleaning, you can opt for the Copilot pane.

In this tutorial, we will show how to clean messy data in Excel with Copilot. We’ll solve 7 common problems using both methods side-by-side, with ready-to-use prompts.

Prerequisites

You will need a Microsoft 365 subscription with Copilot enabled.

Excel’s Copilot now helps in two different ways:

• Clean Data feature scans for issues like spacing, formatting, spelling, text inconsistencies, and number-format problems. Remember, sometimes it may not appear due to version issues.
• In the Copilot pane, use plain-English prompts to clean, standardize, split, combine, or flag data in more flexible ways.
• Copilot works only when the file has AutoSave turned on.

Prepare the Data for Copilot

Format your data into Excel tables. Microsoft recommends using a table or supported range so Copilot can correctly understand and work with the dataset.

• Select your data
• Go to the Insert tab >> select Table or press Ctrl + T
• Check “My table has headers”
• Click OK
• Go to the Table Design tab >> select Table Name Box >> name the table SalesData
• Make sure AutoSave is on (top-left corner)

The Copilot pane in Excel can work with your table, respond to plain-English instructions, and help generate formulas, edits, sorting, filtering, and data transformations. Newer Excel updates have also improved Copilot’s ability to infer the relevant table or range from your request.

1. Rogue Spaces (Leading, Trailing, Extra)

Clean Data Method:

• Select the table
• Go to the Data tab >> click Clean Data
• Copilot will immediately flag “Extra spaces detected” and show a suggestion card
• Click Apply for the affected columns (or all)
• All leading, trailing, and extra spaces are removed automatically

Copilot Pane Prompt:

If you want more specific cleaning, you can use the Copilot pane.

• Go to the Home tab >> select Copilot
• You can type or copy-paste the following prompt

"Remove all leading, trailing, and repeated spaces in the SalesData table without changing the meaning of the text."

• Review the correction and click Done
• Copilot will update the SalesData table automatically

Extra tip: If Copilot inserts a helper column instead of replacing values directly, that is still useful. Review the new cleaned column, then replace the original once you are satisfied.

2. Inconsistent Capitalization

Clean Data Method:

• Go to the Data tab >> select Clean Data
• Copilot detects inconsistent text casing (e.g. “new york” vs “New York”) and offers “Standardize to Title Case” or “Proper Case”.
• Review the before/after preview >> click Apply

Copilot Pane Prompt:

• Type the following prompt:

"Standardize capitalization in the SalesData table. Make customer names, cities, products, and sales rep names use proper capitalization, but keep email addresses lowercase."

This prompt is more controlled than a generic “fix capitalization” request. It tells Copilot which fields to standardize and which field should stay unchanged. It also standardizes state abbreviations despite not mentioning them in the prompt — Copilot automatically detected this issue and resolved it.

3. Mixed Date Formats

Clean Data does not automatically fix dates (it focuses on text, numbers, and spaces). Use the Copilot pane, because mixed dates often need interpretation and standardization across the whole column.

Copilot Pane Prompt:

"Standardize the Order Date column in the SalesData table to one Excel date format: dd-mmm-yyyy. Convert text dates into real Excel dates where possible."

Copilot converts date-like text into actual dates and then applies one display format to the entire column. Rows like 01/02/2026 can be ambiguous depending on locale. After Copilot helps, manually spot-check a few converted dates to make sure day and month were interpreted correctly.

4. Duplicate Entries

The built-in Clean Data feature focuses on formatting and consistency issues. Duplicates are better handled with a direct prompt.

Copilot Pane Prompt:

"Find potential duplicate rows in the SalesData table based on Customer Name, Email, Order Date, Product, Qty, and Unit Price. Highlight them and create a separate list of suspected duplicates."

This prompt asks Copilot to identify duplicates first rather than delete them immediately, then creates a new sheet with the duplicate entries. This gives you an audit trail before removing anything.

That is safer because some near-duplicates may be legitimate repeat orders. This approach is better than the built-in Remove Duplicates feature, since you can review and remove duplicates consciously.

5. Split Names (or Poorly Formatted Names)

A common issue: names in one column, sometimes “First Last”, sometimes “Last, First”, sometimes with extra spaces. This is where natural-language prompting becomes more useful than one-click cleanup.

Copilot Pane Prompt:

"Create separate First Name and Last Name columns from Customer Name in the SalesData table. If a name is written as Last Name, First Name, rearrange it correctly. If only one name exists, keep it in First Name and leave Last Name blank."

Copilot will insert the columns and populate them correctly. Once names are split, you can sort, filter, personalize emails, and detect duplicates much more accurately.

6. Inconsistent Number/Currency Formats

Clean Data may catch some number-format inconsistencies, since Microsoft lists number-format issues as a supported suggestion type. For full control, use the Copilot pane.

Clean Data Method:

• Go to the Data tab >> select Clean Data
• It will flag “Inconsistent number formats” (e.g. “$150.50”, “150”, “200 USD”)
• The suggestion usually offers “Convert all to Currency with 2 decimal places”
• Click Apply

Copilot Pane Prompt:

"Make Unit Price a consistent currency format in US dollars with two decimal places, and make Qty a whole number format."

Follow-up Prompt:

"Add a Total column that multiplies Qty by Unit Price for each row."

That gives you both a clean pricing column and a calculated field ready for analysis.

7. Spelling / Abbreviation Inconsistencies

Messy data often contains inconsistent labels or categories like ny, NY, ca, CA, tx, TX or wireless mouse, Keyboard, webcam, office chair.

Use Clean Data for simple text inconsistency fixes, then the Copilot pane for stricter standardization rules. Microsoft notes that Clean Data is designed to detect inconsistent text issues.

Clean Data Method:

• Go to the Data tab >> select Clean Data
• It sometimes catches obvious spelling variants under “Text inconsistencies”.
• Click Apply if shown

Copilot Pane Prompt:

"Standardize the State column to two-letter uppercase abbreviations and standardize Product names to consistent title case across the SalesData table."

Stronger Version:

"If similar product names appear with different capitalization or spacing, keep only one standardized version."

This is especially useful when later building PivotTables or summaries. Your data is now clean, consistent, and analysis-ready in minutes instead of hours. You can just copy-paste the given prompts.

Bonus: Combine Multiple Fixes in One Prompt

Instead of solving one issue at a time, you can say:

"Clean this dataset by removing duplicates, fixing capitalization, trimming spaces, and standardizing dates."

This is where Copilot becomes powerful — it can perform multi-step cleaning in one go, and it edits the dataset directly, which saves considerable time.

Final Tips for Success

• Always select your table before using Clean Data or prompting
• Clean Data works best on ≤50,000 rows and ≤100 columns
• Copilot pane prompts work even better when you reference column names (e.g. “Order Date column”)
• After cleaning, use Remove Duplicates as a safety net
• Always review the preview — Copilot is helpful but not perfect

Conclusion

By following the above methods, you can clean messy data in Excel using Copilot. The smartest approach is not to rely on only one feature. For fast, low-risk cleanup — such as handling inconsistent capitalization, rogue spaces, and number or currency formatting — the Clean Data feature works best. For smarter fixes like duplicate review, splitting columns, and standardizing data, use the Copilot pane. That combination is what turns a messy import into analysis-ready data.