Finding Duplicates in Excel

Here's an easy way to find duplicate entries in excel using excel's OR function. This method is pretty much obsolete in Excel 2007 and above since it has a built-in duplicate finder/eraser, but for earlier versions, such as Excel 2003, this is still a useful trick.

Let's say you have a list of customer numbers and names and need to find duplicated entries. This sample list is unrealistically short, so imagine a list like this with hundreds of entries.

Using excel's OR function, we can check to see if the entry above or below a particular row contains the same number. To facilitate the calculation, create a new column in front of the data you wish to check.

Then enter the calculation into cell A2: =OR(B2=B1, B2=B3)

The logic behind this calculation will return a TRUE response if EITHER of the conditions are met. It looks to see if either the cell above (B1) or below (B3) cell B2 is a duplicate. Copy this formula down.

Remember that in order for this formula to work, the list of customer numbers will need to be sorted first, so that duplicate entries show up adjacent to each other. You can apply an auto-filter or just data/sort the list.

In this case, sort by customer number.

After the list is sorted, column A will show TRUE for the entries which are duplicated. To group the TRUE entries and find them more easily, you can further sort the list by column A, or apply an auto-filter to the sheet, or you can apply conditional formatting to column A to highlight TRUE entries in color.