Note: This version of the Clean Data is in Excel for the web and Windows, available to Copilot customers. It's being made available on a gradual basis and might not yet be available in some countries or regions.
Excel's AI-based Clean Data feature enables the cleaning of data with only the touch of a button. It does so with three available suggestion types: inconsistent text issues, inconsistent number format issues and extra spaces.
To use any of these functions, select the Clean Data menu item in the Data tab of the ribbon.
Inconsistent text in cells of the same value
This feature seeks to identify otherwise unnoticed variations across same values that could cause subsequent problems. For example, there could be wrong aggregations resulting from any of these situations:
-
A column with a mix of uppercase and lowercase text in the cells.
-
Cells in a column containing differences in punctuation or diacritics.
The Clean Data feature will help to locate these instances where you can easily correct them.
Inconsistent number format 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 help you convert the ‘Text' formatted cells to a number format.
Extra space removal suggestions
This specific Clean Data function detects and suggest the removal of unnecessary spaces in the inspected table—such as extra leading spaces, trailing spaces, and between-values spaces.
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, go 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 might 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, contact us by going to File > Feedback or by selecting Tell us what you think about Clean Data at the lower-right of the side-pane.
