Click on the drop-down of Duplicate value.After clicking on the filter icon, you need to click on the arrow placed in the header of the column duplicate.If you want automatic filtering then convert the data available in the table into a completely functional excel table for which you need to press Ctrl+T.Go to the Data tab and then click on a filter icon located in the Sort & Filter section.Filter Duplicates in ExcelĪnother method to find duplicates in excel is to filter data to get duplicate values. For counting duplicate values you need to use the countif formula. If you want to know the total number of duplicate values then you need to use the Count function. The formula to find duplicate values in two columns is =If (Countifs($A$2:$A$8, $A2, $B$2:$B$8, $B2)>1, "Duplicate Values"," ") Now we want to find duplicate values having the same name and fruits. In this example, we have taken a table where the candidate name is in column A and Fruits is in column B.
#Excel formula to remove duplicates in two columns how to
![excel formula to remove duplicates in two columns excel formula to remove duplicates in two columns](https://i0.wp.com/basicexceltutorial.com/wp-content/uploads/2019/07/Pasted-34.png)
Make sure that your data of duplicate values gets highlighted.This will highlight all the duplicate values.Choose the color contrast as per your preference.
![excel formula to remove duplicates in two columns excel formula to remove duplicates in two columns](https://i.ytimg.com/vi/81EOZv5Z-io/maxresdefault.jpg)
Choose a duplicate option from the drop-down list.Another window will get open that comes with customization options.After this find another option that is Duplicate Values.Click on the conditional formatting drop-down arrow.Find the conditional formatting icon available on the ribbon.Choose the cells from where you want to find duplicate values.After finding out the duplicate values, you can remove them if you want by using different methods that are described below. To find duplicate values in Excel, you can use conditional formatting excel formula, Vlookup, and Countif formula. Find Duplicates in Excel using Conditional Formatting Here you can check three different processes. The method or formula to find and remove the duplicate items make the process easier and saves your time. You might be thinking as to why should I apply any formula or method to find duplicate values as it is easy.īut it’s not about few data, you can apply a formula or method when you have lots of data. There are many ways to find duplicate items and values in excel. Find Duplicates in One Column using COUNTIF
![excel formula to remove duplicates in two columns excel formula to remove duplicates in two columns](https://i1.wp.com/content.edupristine.com/images/blogs/finding-and-removing-duplicates-in-excel_10.jpg)