Hi everyone, this is part 10 in a series of posts to show you some of the things that are possible to do with Copilot in Excel.
We’re thrilled to introduce a new AI-powered feature in Excel – Clean Data. With Copilot in Excel, you can clean data with just one click. Clean Data detects and offers solutions for text inconsistencies, number format issues, and extra spaces. This feature is now available to Excel for the web users and will be rolling out to Windows desktop users in the coming months.
Simply select the Clean Data option in the Data tab of the ribbon or interact with the pop-up banner to get started.
Excel window showing business bar with the text: “Copilot detected 4 data inconsistencies and formatting issues. Fix them to avoid wrong calculations and messy workbook”
Text inconsistencies in cells of the same value
This feature seeks to identify unnoticed variations across similar values that could cause subsequent problems. For example, a column with a mix of uppercase and lowercase text in the cells; or cells in a column containing differences in punctuation or diacritics.
Clean Data will locate these inconsistencies and will help you to easily correct them.
Inconsistent text suggestion card showing an example of mixed cases to the word “Excel”, including "Excel", "EXCEL", and "Excel."
Number format inconsistency in a column
This feature seeks to identify columns with a mix of both Text and Number formatted cells that could result in incorrect calculations. Clean Data will locate these inconsistencies and will help you convert the ‘Text’ formatted cells to a number format.
Inconsistent number format suggestion card showing an example of mixed Number and Text cells
Extra space removal suggestions
Clean Data will also detect and suggest the removal of unnecessary spaces in the highlighted table – such as extra leading spaces, trailing spaces, and between-value spaces.
Extra spaces suggestion card suggesting removing the extra spaces in the Table
Example of between-values extra spaces between the word “Microsoft” and “Excel”.
Practical tips
Here are some helpful pointers for getting the most out of Clean Data:
- Clean Data works best with tabular data of up to 100 columns and 50,000 rows.
- Clean Data works best with data that is formatted as an Excel table. To create an Excel table, click anywhere in your data and then select Home > Tables > Format as Table.
- While not limited to English, Clean Data is initially going to perform best in English.
Functionality and Feedback
This is just the initial release; we’re always improving Clean Data. It may or may not behave as you anticipate. If you don't get any expected Clean Data suggestions, get non-accurate suggestions or have any sort of feedback, please contact us via File > Feedback or by clicking the dedicated button at the bottom of the side-pane: Tell us what you think about Clean Data.
Stay tuned,
Microsoft Excel Team
Microsoft