Excel's Clean Data button checks three things. Here is what it leaves in your file

What does the Clean Data button in Excel actually check?

Microsoft’s help page, Clean Data in Excel, describes it as a feature that uses AI to look over your table and suggest fixes. You open it from the Data tab, it shows its suggestions in a side panel, and for each one you choose Apply to change the cells or Ignore to leave them as they are. There are three kinds of suggestion and only three.

The first is inconsistent text, which means the same value written in different ways, such as different capital letters, punctuation or accents. Think of Acme Ltd, ACME LTD and Acme Ltd. in one customer column. A pivot table counts those as three customers, and that is exactly the wrong total Microsoft warns about.

The second is inconsistent number formats. That is a column where some cells are stored as numbers and some as text, so a SUM quietly skips the text ones and the total comes out low. Clean Data offers to convert the text cells to a number format. The third is extra spaces, at the start, at the end, or doubled between words, and it offers to remove them.

Microsoft also gives a few limits that are worth knowing before you rely on it. It is in Excel for the web and Excel for Windows for Copilot customers, it is being rolled out gradually so some countries do not have it yet, and it works best on up to 100 columns and 50,000 rows set up as an Excel table. It is not limited to English, but Microsoft says it performs best in English, and the page calls itself a description of the initial release that “might not behave as you anticipate”.

Which data problems does Clean Data not fix?

The easiest way to see the gap is to put the usual list of messes next to the three checks. I clean files in a fixed order, structure first, then types, then labels, then duplicates, and I explain why that order matters in my guide to cleaning messy Excel data. Clean Data covers part of the types step and most of the labels step. It does not touch structure or duplicates at all.

Two rows in that table need a word of explanation. Non-breaking spaces are the invisible character web pages use to stop a line breaking, and the TRIM function does not remove them. Microsoft says Clean Data removes extra spaces, but it does not say whether that includes this character. So if a value still will not match after Clean Data has run, test for it with =SUMPRODUCT(--ISNUMBER(FIND(CHAR(160),A2:A500))) on the column, and anything above zero means it is still there.

Duplicates are the other one, and they matter because they are the most common reason a total is too high. Clean Data does not look for repeated rows. Run it first, so that Acme Ltd and ACME LTD become one spelling, and then remove duplicates, because a duplicate check compares values exactly and would miss them otherwise. Which row survives depends on the tool, and I walk through that in removing duplicates in Excel without losing data.

Can Clean Data break a column of IDs or postcodes?

Yes, if you accept the number suggestion on the wrong column. The number check looks for a column that mixes text cells and number cells and offers to turn the text cells into numbers. On a column of amounts that is exactly what you want. On a column of codes, it is the opposite of what you want.

Here is why. Employee IDs, postcodes, invoice numbers and account numbers are often stored as text on purpose, because they are labels, not quantities. Microsoft’s page on keeping leading zeros and large numbers explains that Excel removes leading zeros from numbers and keeps only 15 significant digits, rounding any digit after the fifteenth down to zero. So 00417 becomes 417, and a 16 digit card or account number loses its last digit.

A mixed code column is also very common, which is why this is a real risk and not a corner case. When some IDs have leading zeros and some do not, Excel often stores the short ones as numbers and the zero ones as text, and that is precisely the mix the number check looks for. Once the zeros are gone, a lookup against the client’s master list stops matching, and the error shows up later in a report, far away from the button that caused it.

If nobody ever adds the column up, it is a code. Click Ignore on any number suggestion for that column.

How do I check what Clean Data changed?

The panel lets you accept or skip each suggestion, and that is good, but on a few thousand rows you cannot look at every cell it touches. What you can do is write down three numbers before you click Apply and look at them again afterwards. Each one should move in one direction only, and that makes a wrong change easy to spot.

1. 01
   Make the data an Excel table first

   Microsoft says Clean Data works best on a table, and it also makes the formulas below easy. Select the data and use Home, Format as Table.
2. 02
   Write down the row count

   Use =ROWS(Table1) with your table name. None of the three checks is about removing rows, so this number should be the same afterwards. If it has changed, find out why before you go on.
3. 03
   Write down the total of the amount column

   Use =SUM(Table1[Amount]). Text numbers are skipped by SUM, so after the number fix this total should stay the same or go up. If it goes down, read the suggestion again before you send anything.
4. 04
   Write down the distinct count of your key label

   Use =COUNTA(UNIQUE(Table1[Customer])). When spellings merge, this number goes down, and that is the point. If it drops much more than you expected, a suggestion may have merged two customers that really are different.

That last point deserves an example, because it is where a text suggestion can be wrong and still look tidy. Two branches called North Star Trading and Northstar Trading might be one company typed two ways, or two different clients. Clean Data cannot know that, so read every suggestion that merges names before you click Apply. This is the same habit I use for any AI output on a spreadsheet, and I set out the full routine in how to check an AI’s Excel numbers.

When should I use Clean Data, and when should I use Power Query?

Use Clean Data for a file you clean once. It is quick, it asks before it changes anything, and it handles the boring parts of the labels step well. But every new export arrives dirty in the same ways, and Clean Data has to be run and read again each time.

For a file that comes back every week, I build the cleaning once in Power Query instead. Trim, change type, replace values and remove duplicates become saved steps, and the next export is cleaned the same way by pressing Refresh. That is the point where cleaning stops being a task and becomes part of an automated report, and I explain that setup in automating a weekly Excel report.

So my order on a new file is simple. Fix the structure by hand, run Clean Data and read every suggestion, click Ignore on code columns, then sort out dates and duplicates myself, and finally check the three numbers. If the same file is going to come back next week, I move those same steps into Power Query before it does.