Now drag the formula in column C till row number 5 as shown below.Īs you could see Y represents the duplicates. In this formula we are just checking the frequency of a value by using countifs inside if condition and displaying Y or N based on the duplicates. Second Way of checking duplicates by a combination of cells in excel.įor the same case that was explained above, we will see another way by creating a column called Duplicate and entering the formula =IF(COUNTIFS(A:A,A2,B:B,B2) > 1,”Y”,”N”) in it as shown below. That was a basic method of checking duplicates by a combination of cells in excel. So basically this countif formula is checking the count of each of the value in the entire range.Īs you could see “James” has a count of 2 as it appears twice in our data, so the row which has a count of more than 1 is duplicate and we could filter those records. Now we will create a column called count in which we will calculate the frequency of each of the item in column C using COUNTIF. Now drag this formula till row 5 as we have data available till row number 5. In our case the number of columns is two, so we will concatenate these two columns in column C.Įnter the formula =A2&B2 in cell C1, remember if we were to check duplicates by more than 2 cell combinations also, we would concatenate all of them. Select the Highlight Cells Rules option and from the secondary menu that appears, select the Duplicate Values.
![how to find duplicates in excel how to find duplicates in excel](https://cdn.ablebits.com/_img-blog/identify-duplicates/identify-duplicates-excel-formula.png)
It’s a very simple way to check the duplicates by multiple cells, first concatenate all the cells for which you want to check the duplicate for into single cell by using concatenation operator “&”. We will see two ways of checking duplicates in excel by a combination of cells/multiple cells.įirst Way of checking duplicates by a combination of cells in excel. In the below example you could see we have two columns with First Name and Last Name.
![how to find duplicates in excel how to find duplicates in excel](https://www.excel-easy.com/examples/images/remove-duplicates/remove-duplicates-dialog-box.png)
In this case we will have to check for duplicates by a combination of two cells of two columns in excel. There are situations when we need to check for duplicates in excel by multiple column values, suppose we have two columns with first name and last name and duplicate names are not allowed.
![how to find duplicates in excel how to find duplicates in excel](https://excelnotes.com/wp-content/uploads/2020/02/excelduplicates2columns07large.jpg)
In this post we will see how we could find if there are duplicates by combinations of cells in excel.