How to Find Duplicates in Excel [CONDITIONAL FORMATTING/ COUNT IF]

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?

Find duplicate values

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.

Using Conditional Formatting

  • 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.


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.

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.

Leave a Reply

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