How to find duplicates in excel- 3 ways to check for duplicates

As a part of data analysis , in excel we can find and remove duplicate values. there are different ways to remove duplicates in excel. In this article let us understand how to find duplicates in excel.

3 Different ways to check duplicates:

There are three different ways to find duplicates in excel.

  1. Conditional formating to find duplicates
  2.  Using Remove duplicate function in Data tab
  3.  Countif function to find duplicates in a column.

Let us understand these there ways.

Conditional formating to find duplicates

Excel provides various conditional formatting feature. In that we can find out the duplicates in a selected range and it will highlight the duplicate cells in the selected range. 

To put conditional formatting:

  • Select the range to find duplicates
  • go to conditional formatting in Home tab
  • Select> Highlight Cells rules > Duplicate Values and click ok on box.

Excel check for duplicates and Highlight the duplicate value cells with Color.

find duplicate value

Using Remove duplicate function in Data tab

One more option to remove duplicates is to select the column and click on Remove Duplicates in Data Tab. It will ask to select the column to identify duplicates in excel sheet and delete duplicates in excel and shows only unique values.

FIND DUPLICATES

Excel will delete duplicates and show the confirmation message like below

Countif function to find duplicates in a column

Using Countif function also we can find out how many times a perticular value is repeated in a column. Then you can manually delete or remove the duplicate values.

Leave a Comment