How Do I Find Duplicates in Excel?

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.

Method 1:

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.

EXCEL 3/31/2017 , 8:17:30 PM Microsoft Excel - Book1
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.SortButton

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.AutoSumButton


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?

Method 2:

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s