Got a list of values in an Excel Spreadsheet and need to know if there are any duplicates?
I’ll give you two easy options here.
This is our sample list. Notice that we’ve got two Aardvarks.
In a blank column next to our data (add in an extra column if you have to, use an if statement to compare A1 to A2.
This formula returns 1 if the two values are the same, and 0 if they aren’t.
After you hit <Enter> on the formula, it returns a 0 because Aardvark and Bear don’t match. We need the same formula all the way down, so double click the tiny little black square shown here to replicate it.
Now the formula continues all the way to the bottom. But it hasn’t returned any matches. What gives? The problem is that this formula only works if the matches are right next to one another.
So highlight the list and click the sort button.
Now that the list is sorted, you get a 1 next to the first duplicated value. If you like, you can use the autosum button at the bottom of the column to get a count on the number of duplicates.
That’s great. It’s a nice easy count of the number of duplicates. The logic even runs really fast if you’ve got a 30,000 row spreadsheet.
But… what if you can’t resort the spreadsheet for some reason?
Here’s our same list, but since we can’t resort it, we’ll use the CountIf
Don’t forget the dollar signs on this formula. They tell Excel that you don’t want those values changed when you replicate the formula all the way down.
You can already see that this version found two Aardvarks even though they aren’t next to one another. Just like the previous example, double click the little black square to replicate the formula.
Now you can see that it gives you a count of how many times each value appears. Anything over a one is a duplicate. You can’t use AutoSum to find the number of duplicates again, so you might consider using CountIf again to get the duplicates:
In B9 enter this formula:
In our example, Excel will return a 2. That number is a little inflated, but at least it tells you that you’ve got some duplication to look for.