Remove duplicates NOT WORKING if number formats different

Be careful when using the Remove Duplicates tool in Excel. Remove duplicates DOES NOT work if number formats are different. It is very literal in that even if the format is different it will assume it is not a duplicate. So the numbers 10 and 10.00 will be seen as unique and not deleted. However, conditional formatting DOES see the correct items as duplicates (ignores the formatting).

When not finding duplicates is likely to be a problem

This shouldn’t be a problem if the data is from a system as they tend to be consistent in how they format each column. However, if it is based on human entry or a copy/ paste from another system/ source, it may well NOT delete all the true duplicates.

Remove duplicates NOT seeing duplicates

As shown below (1) is what we effectively want the Remove duplicates tool to see, however it may look like (2) or (3), especially if a human has been involved or data from different sources are put together. Normally Excel ignores formatting and looks at the truth of the number, but the Remove Duplicates tool does not, and can appear as if it is not working.

Remove duplicate NOT WORKING

If we run the Remove Duplicates tool on the 3 blocks, this is what is left behind. Notice that the only difference in (2) and (3) is the way some of the numbers are formatted.

Does Conditional Formatting duplicates have the same problem?

Simple answer is NO. Below, using Conditional Formatting Duplicate values, in (2) and (3) you will see that all 4 cells are considered duplicates even though formatting is different.

Solution- Make all the formats the same

If you use Remove Duplicates, perhaps consider formatting the first row the way you want it and then using the format painter to make all of them the same. Remove Duplicates is more likely to get it correct if you do this.

Goto the next duplicate in Excel

Excel not recognizing duplicates

Excel formula to Rank list with duplicates

Data scrubbing using Excel course