How to find duplicate rows in excel

Find Duplicate rows in Excel 

We use excel for different data processing and one of the major requirement is finding repeating data. We can easily remove duplicate data using remove duplicate in excel but finding unique or duplicate value is quite tricky. This list contains name of countries and their capital.  In Column A India and Brazil are duplicate records. If we consider both column A and Column B only Brazil has duplicate record.

CASE 1: Find duplicate in a column (Find duplicate country)

Step 1: Click on the Column Header to highlight Column A.

Step 2: Select the Conditional Formatting button on the Home tab.

Step 3: Select Highlight Cells Rules.

Step 4: Click on Duplicate Values...

Step 5: click OK (Optionally you can change color or pattern on values with box, after completing values press OK)

All duplicate values in that column will be highlighted with specific color. To view only duplicates values, follow the steps:

Step 1: select a duplicate value cell (colored cell) and right click on that cell.
Step 2: Click on Filter
Step 3: select Filter by Selected Cell's Color.

Only cell with duplicate value will be listed. To remove filters, go to Data tab in ribbon and do clear filter.

In next tutorial we will discuss case 2 where we will find duplicate value from multiple rows.