How to Find Duplicates in Excel

In this lesson, you will learn how to find duplicates in Excel. Identifying duplicate entries is crucial for data cleaning and analysis, and Excel provides efficient methods to quickly locate these duplicates, even within large datasets.

Applying conditional formatting to find duplicates

You have 3 columns of data and you want to highlight duplicates.

Compare Columns Table

First select the whole table.

Compare Columns Data

Go to the Ribbon. Home tab> Styles section. Click here to view Conditional Formatting > Highlight Cell Rules > Duplicate Values

Compare Columns Duplicate Values

A dialog box will appear. Choose how to highlight duplicates in Excel. I chose dark red text with a light red fill.

Compare Columns Values

Excel highlights duplicates in your table.

Excel compare columns find duplicates

Applying the match formula

Another way I know is to use the match function.

Excel includes a match function that allows you to find a value in a given range of cells. Another approach uses the MATCH function combined with ISNUMBER and NOT. The formula

=NOT(ISNUMBER(MATCH(B3,$C$3:$C$8,0)))

Match formula to find duplicates

checks if the value in B3 is found within the range C3:C8. The MATCH function returns the position of the match if found, or an error if not. ISNUMBER checks if the result of MATCH is a number (meaning a match was found). NOT then inverts the result:

  • TRUE if no match is found (unique value),
  • FALSE if a match is found (duplicate).

This formula, when copied down column B, will identify duplicates between column B and column C.

Conditional formatting provides a quick visual overview of all duplicates within a range, making it ideal for immediate identification and review. The MATCH formula approach, on the other hand, is more versatile for further data manipulation, such as filtering, sorting, or counting duplicates, as it provides TRUE/FALSE values that can be used in other formulas.