If you come across situations where Excel is not recognizing duplicates you may need to investigate further to see what is happening. As a rule, if Excel says it is not a duplicate, then it is not a duplicate. Just because you think it is does not mean you are correct.
Table of contents
To see if Excel has not gone crazy, copy and paste the one item into the cell of the ‘duplicate’. If Excel now says it is a duplicate then you know that something about that cell is different (even if you can’t visually see it)
Below some of the reasons that a human sees a duplicate and Excel sees unique items.
Formatting of data in some tools
As per the Remove Duplicates tool not working post, the format of data causes a problem with the Remove Duplicates button.
In most cases Excel does not care if a number is for example shown as
- 10, or
Excel sees it as 10 and considers it a duplicate.
However the Remove Duplicate tool does not.
Trailing or leading spaces
Probably the most common cause of Excel not recognizing duplicates. Check if the one cell has trailing, leading or extra spaces in the cell. Excel sees the space as an individual character but humans tend to ignore it.
Trailing spaces are especially difficult to spot as the human has no reference point. Leading spaces will cause a slight misalignment, extra spaces just look slightly too big, but trailing spaces are invisible to humans. You may need to remove these spaces. You can see how to remove spaces in the Data Cleanup course
A more common problem recently, there is a type of space which Excel sees as a ‘different’ space from the one generated when you click the space bar. To see if this the issue, copy the ‘space’ and paste it into the Find/ Replace tool and ‘replace’ it with the more common space character generated by the space button on the keyboard. See more on this in the Data Cleanup course
Text versus Number
You may see 123 in the cells, but Excel may see the one as 123 ( a number ) and the other one as “123” (text). This may seem minor to you but for Excel these are not the same and therefore it will not recognize it as a duplicate.
You will need to correct either one of these to be seen as both text or both numbers before it will ‘see’ the duplicate. You can see how to convert text to numbers in the Data Cleanup course.
Your Ranges are not correct
Just because the first few work, it doesn’t mean that your formulas are working over the entire area. Especially when working with templates, formula are created for a specific area (say the first 1000 rows) but over time the template grows to include say 2000 rows. If the formula does not include the full 2000 rows, Excel will never see the duplicate.
Your $ signs are not correct
A very common problem. If you forget to put $ signs on your formula, Excel may not be looking at the entire area. So as you drag the formula down, you think Excel is looking at the full list, but instead it is looking at only the last few cells. Click on one of the last formula and use the Auditing Toolbar to see where it is looking.