How to Find Duplicates in Excel (Conditional Formatting/ Count If/ Filter)

When you are working on a large number of data then you will encounter few duplicate entries. Sometimes these duplicate values are very useful but many times you want to remove the duplicate data. If you have 1000 of data then it becomes quite difficult to find and remove the duplicate data. Here we have provided the step by step process on how to find duplicates in excel. Take a look and follow the process.

How to Find Duplicates in Excel

There are many ways to find duplicate items and values in excel. You might be thinking as to why should I apply any formula or method to find duplicate values as it is easy. But it’s not about few data, you can apply formula or method when you have lots of data. The method or formula to find and remove the duplicate items make the process easier and save your time. Here you can check three different processes.

1. Find Duplicates in Excel using Conditional Formatting

To find duplicate values in Excel, you can use conditional formatting excel formula, vlookup, and countif formula. After finding out the duplicate values, you can remove them if you want by using different methods that are described below.

Find duplicate values

  • Choose the cells from where you want to find duplicate values.
  • Then go to the home tab or ribbon.
  • Find the conditional formatting icon available on the ribbon.
  • Click on the conditional formatting drop-down arrow.
  • Click on the “Highlight Cells Rules”.
  • After this find another option that is Duplicate Values.

How to find duplicate in excel

  • Choose duplicate values.
  • Another window will get open that comes with customization options.
  • Choose a duplicate option from the drop-down list.

find duplicate in excel

  • Choose the color contrast as per your preference.
  • Click OK.
  • This will highlight all the duplicate values.
  • Make sure that your data of duplicate values gets highlighted.

Remove duplicate values

  • Select all the duplicate cells or highlighted cells
  • Delete the values by pressing on Delete button.
  • After deleting the values, go to the conditional formatting.
  • Choose Clear rules.
  • And then choose clear rules from an entire sheet.

Duplicate values

Another way to remove duplicate value

This method will delete all the duplicate data permanently. If you want the duplicate data again, then copy the data on another worksheet.

  • Select the cells on which you want to find and remove the duplicate values.
  • Go to the ribbon and find the data option.
  • In the data tab, you will find the remove duplicates option.

Remove duplicate values

  • Now check or uncheck the column on which you want to apply this constraint.
  • Then click OK.

2. Find Duplicates in One Column using COUNTIF

Another way to detect duplicates is via COUNTIF function. If you want to apply the COUNTIF function then check here how to find duplicates in Excel using Excel COUNT function.

  • If you have the list of items from where you want to find duplicates, then you need to apply COUNTIF formula on to it.
  • The syntax for COUNTIF: “=COUNTIF(B:B, B2)>1”.

Remove and delete duplicate values

  • In column A, you have the Buyer’s name and in column B, you have the fruits name that he or she likes. Now you want to find the duplicate items.
  • Put this COUNTIF formula in column C.
  • For all duplicate fields, it shows TRUE whereas, for non-duplicate fields, it shows FALSE.
  • Now by following the above step by step process, you can delete all the duplicate items that you do not need.

Find Duplicates in Two Columns in Excel

Above we have seen how to find duplicate values in one column, now we will see here how to find duplicates in two columns in excel.

In this example, we have taken a table where candidate name is in column A and Fruits is in column B. Now we want to find duplicate values having same name and fruits.

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”,” “)

How to Count Duplicates in Excel

If you want to know the total number of duplicate values then you need to use count function. For counting duplicate values you need to use CountIf formula.

=Countif($A$2:$A$8, $A2)

3. Filter Duplicates in Excel

Another method to find duplicates in excel is to filter data to get the duplicate values.

  • Get a data set
  • Select your table
  • Go to the Data tab and then click on a filter icon located in the Sort & Filter section.

find duplicates in excel

  • 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.
  • After clicking on the filter icon, you need to click on the arrow placed in the header of the column duplicate.

How to find duplicates in excel using filter

  • Click on the drop-down of Duplicate value.
  • To show the duplicate, you need to check the Duplicate Values option whereas if you want to filter the values then select blank and unselect “Duplicate values” to get the unique value.

How to find duplicates in excel using filter 1

Remove Duplicates Online

To remove duplicates, you need to follow the below-mentioned process

  • Select the table by dragging the mouse.
  • Right click on the table.
  • Delete the row by choosing Delete Row option.

Highlight Duplicates in Excel

In order to highlight the duplicate values, you can either use a conditional formula to find duplicates in excel or follows the below-mentioned process.

  • Select the duplicate values.
  • Go to the Fill color option and select the color.

How to find duplicates in excel 2

  • This will highlight the duplicate values in excel.

Apart from these, there are other processes available by which you can find the duplicate items available in your data set. After finding out the duplicate values, you can remove them easily. Stay connected with our website to know other Excel features.

Related Queries

Here we have listed a few queries that are asked by excel learner. If you are also facing this kind of problem then you can check it here.

Q1. How to delete duplicate e-mail addresses if you have 10,000 emails in your data set?

Ans. If you have all email addresses in a single column then select the column and go to the “Data” ribbon. You will find remove duplicates tab in data tools. Click on Remove Duplicates and you will find another dialog box where you need to make a selection. Press Ok and you receive the unique email addresses.

Q2. How to find the repeated teacher’s name in Column A? The data is

  • B1= Maths/English Jamine, Eon
  • B2= Physics Johana
  • B3= History Poole
  • B4= English/Physics Sam, Jamine
  • B5= Chemistry Rameo

Ans. To solve this problem, create three separate columns. In the first column, mention the class name. In the second column, put the teacher’s name and in third mention the another teacher’s name. Now in the fourth column, put the formula =COUNTIF(B:B,B1)>1

Using this formula, you will find “TRUE” if the value is duplicate.

Q3. How to sort data before applying a filter?

Ans. To sort data you need to choose Sort tab in excel. Sorting a data set is a quite easy process. Follow the process mentioned below and sort your data completely.

  • Select the data set.
  • Go to the Data ribbon.
  • In the Sort & Filter section, you will find Sort icon. Click on the icon and sort your data.

Q4. How to compare two columns in excel?

This question was mainly asked by the interviewer at the time of an interview. There are many ways to compare the two columns. To get detailed information Click Here.

  1. =A2=B2 This is the simple formula used to compare two columns.
  2. Conditional Formatting: In this, you need to follow the above process, you can check the conditional formatting excel link to get detailed information.
  3. Vlookup: You can also use Vlookup formula to compare two columns using Vlookup for research, ISNA for performance, and If to customize result.
  4. Excel If Statement: This is a quite useful function in excel and it can be used in many ways. To get the detailed information Click Here.

Q5. How to filter data in excel?

Filtering in excel is an easy process. In the sort & filter section, you will find Filter icon. Click on the icon and filter your data. For more details Click Here.

In this tutorial, we have covered all the excel topics that are best for beginners and experts. We always try to solve all the problems that people face while working on excel. If you have any other query or want to ask something then you can mention in the comment box below. One of our team member will be back with the answer.

Leave a Reply

Your email address will not be published. Required fields are marked *