1. Conditional Formatting: Highlight & Find Duplicates in Excel
Find duplicate values in Excel files by using “Conditional Formatting” as explained below.
Excel 2007 and above has this option enabled already.
Open the worksheet with duplicate values in a list & follow these steps:
- Select the Range.
- Go to “Conditional Formatting” in “Home” Menu
- Select “Highlight Cell Rules”
- Click “Duplicate Values”.
- Choose color to highlight.
- Click OK.
All the cells with duplicates entries will be highlighted with the color format that you have chosen above. With this method you can only review the values.
You will not be able to filter them based on the color, if your Microsoft office is not of latest version.
Also Read: Sort rows based on color in Excel
2. Using Formula to Find Duplicates in Excel?
To find duplicate entries in a range, you can use the excel formula “Countif” as explained below.
Countif formula counts the number of occurrences of a value in a range. This way, we get whether a value is unique or duplicate.
Formula to Find Duplicates in Excel
- Open Excel workbook.
- Enter duplicates values in some cells. (Ex: Range A2:A11)
- Insert formula “=COUNTIF(A:A,A2)” in cell B2
- Copy paste this formula to remaining rows in Column B.
- Col B will have count of values in Col A.
- Unique values will have count 1.
- Duplicate values will have count greater than 1.
You can filter the values greater than 1 to get duplicate entries. To see an illustration on how it works, watch the video at end of this page.
Most times this is solved through any VBA macro or the above explained method can be used.
