How to Find Duplicates in Excel

There are different ways to Find duplicate items in Excel, here we are going to explain two of them.

How to Find Duplicates in Excel with Formula.

1- Combine the contents of the cells in the rows.

For that

• create the new column.

• write the name of each cell and add “&”, for instance write “=C3&D3&E3&F3&G3”.

2- Specify this formula to other cells of the column.

3- Now we should count number of the items which we have in combine column.

For that we can use this Formula;

“=Countif($H$3:H3,H3)”,

You should pay attention, H3 is the address of the first cell of the column which we want to count the number of their items.

4- Specify this formula to the other cell of column.

For Finding unique items we should filter the items which the number of them are higher than 1.

5- Select the column.

6- Go to the Data tab.

7- Click on Filter key.

8- At the top right corner of the column click on the arrow.

9- Deselect all items.

10- Select number one and press Ok.

11- Now you can see only unique Items in your screen.

How to Find Duplicates in Excel with Conditional Formatting.

1- Combine the contents of the cells in the rows.

For that

• create the new column.

• write the name of each cell and add “&”, for instance write “=C3&D3&E3&F3&G3”.

2- Specify this formula for other cells of column.

3- Select the column.

4- Click on the Home tab.

5- Click on the Conditional Formatting.

6- Click on the Highlight Cells Rules.

7- Click on the Duplicate Values.

8- Select the Unique.

9- In the “Values with” part you can specify your desired color for unique and duplicate cells.

10- Click on Ok.

Now you should filter unique items for that:

11- Select the column.

12- Click on the Data tab and click on the Filter.

13- At the top right corner of the column click on the arrow.

14- Click on Filter by Color and choose your desired color.

15- Click on ok.

16- Now you have unique items on the screen.

How to Find Duplicates in Two Worksheets in Excel

1- Select all cells in the sheet which you want to find duplicated items on it.

2- Click on the Conditional Formatting.

3- Select New Rule.

4- Select “Use a formula to determine which cells to format” item.

5- In the formula part write;

=Countif(

6- Go to the other sheet which you want to compare their items with that.

7- Select the cells which you want to compare them with the data in sheet one.

you can see the formula was changed to

=Countif(sheet2!$A$2:$A$11,

$A$2:$A$11 are the addresses of the cells in the second sheet which you want to compare them with the first sheet.

8- Complete the formula

=Countif(sheet2!$A$2:$A$11,A2)>0

Here A2 is the address of the first cell in the second sheet.

8- Click Format.

9- Choose your desired colour for duplicated items.

10- Click Ok.