How to Find Duplicates in Excel Using Formulas

To identify duplicates in Excel, you can use the COUNTIF Function. The formula may vary slightly depending on whether you want to include the first occurrence or not. Here are the steps to identify duplicates in Excel:

How to Find Duplicate Records Including 1st Occurrences

Suppose you have a list of items in column A that you want to check for duplicates. To find Duplicates, including the first occurrences, enter the following formula in cell B2(considering A2 is the topmost cell):

=COUNTIF(A:A,A2)>1

Copy the formula from cell B2 and drag the fill handle down to copy it to other cells in column B.

This will apply the formula to each cell and evaluate if it is a duplicate. This formula will return TRUE for duplicate values and False for unique values.

Note: If you want to find duplicates in a specific range of cells rather than the entire column, use a locked range by using the dollar ($) sign. For example, if you want to search for duplicates in cells A2:A10, use the formula:

=COUNTIF($A$2:$A$10,A2)>1

To make the result more accurate, you can use the IF Function. This allows you to assign labels to duplicate and unique values. For example:

=IF(COUNTIF($A$2:$A$10, $A2)>1,"Duplicate","Unique")

This formula will return “Duplicate” for duplicate records and “Unique” for unique records.

If you only want to identify duplicates and leave unique values blank, modify the formula as follows:

=IF(COUNTIF($A$2:$A$8,$A2)>1,"Duplicate","")

This modified Formula will return “Duplicate ” for duplicate records and leave the unique records blank.

Duplicates are those which appear more than one time. Finding and highlighting duplicates in Excel is a common task that helps in data analysis and management. While working with a large Excel worksheet or converting many small worksheets into bigger ones, you may find many duplicates in it.

In this article, we will look at different ways we can find duplicates in Excel easily and quickly.