Learn how to find and highlight duplicate values in Excel using conditional formatting and formulas. 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.

First select the whole table.

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

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

Excel will automatically highlight all duplicate values in your selected range.

Applying the match formula

Another way to find duplicates in Excel is by using the MATCH function combined with ISNUMBER and NOT.

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)))

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.

Related posts:

How to Create Pivot Table from Multiple Sheets How to Do Mann-Whitney U Test in Excel Profit Margin Formula in Excel