To find duplicates in Excel, you have several effective methods depending on whether you want to highlight, count, or remove duplicates:
1. Using Conditional Formatting to Highlight Duplicates
- Select the range of cells you want to check.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a formatting style to highlight duplicate entries.
This method visually marks duplicates without removing them
2. Using the Remove Duplicates Feature
- Select your data range or click any cell within your table.
- Go to the Data tab on the ribbon.
- Click Remove Duplicates in the Data Tools group.
- In the dialog box, select the columns to check for duplicates.
- Click OK to remove duplicate rows based on your selection.
Excel will notify you how many duplicates were removed and how many unique values remain
3. Using Formulas to Identify Duplicates
-
To find duplicates including the first occurrence, use:
=COUNTIF(A:A, A2)>1 -
To label duplicates with custom text (e.g., "Duplicate") and leave unique values blank:
=IF(COUNTIF($A$2:$A$8, A2)>1, "Duplicate", "") -
For finding duplicate rows across multiple columns, use COUNTIFS:
=IF(COUNTIFS($A$2:$A$8, $A2, $B$2:$B$8, $B2, $C$2:$C$8, $C2)>1, "Duplicate row", "") -
To count how many times each value appears:
=COUNTIF($A$2:$A$8, A2)
These formulas help identify duplicates dynamically and can be copied down the column
4. Using Advanced Tools or Add-ins
There are Excel add-ins like "Duplicate Remover" that can quickly find, highlight, select, or delete duplicates, including entire duplicate rows based on multiple columns, with more flexibility than built-in features
. In summary, for quick duplicate detection, conditional formatting is useful; to clean data, use Remove Duplicates; and for detailed analysis or custom handling, formulas like COUNTIF and COUNTIFS are powerful tools.
