# How to Find Duplicates in Excel

## Jun 15, 2020 ยท 3 minute read

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.