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.
Table of Contents
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 highlights duplicates in your table.
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)))
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.
How To Count Number Of Duplicates • Pandas How To
[…] to calculate cumulative sum in Pandas How to count specific value in column How to drop duplicates How to find duplicates in Excel How to resolve ValueError: Index has duplicate keys […]