How to detect duplicate entries in a list in Excel?

We can set data validation for a certain list to automatically detect duplicate entries in Excel. Please do as follows:

1. Select the list you will prevent duplicate entries, and click Data > Data Validation to open the Data Validation dialog box.

2. In the opening Data Validation dialog box, select Custom from the Allow drop down list, and type the formula =COUNTIF($A:$A,A1)=1 into the Formula box. See screenshot:

Note: In above formula, $A:$A is the selected list/column in step 1 you need to detect duplicate automatically, and A1 is the first cell in this list/column.

3. Click the OK button to apply this data validation.

From now on, a warning dialog box as below screenshot will pop out automatically when duplicate entry occurs in the specified list/column.

If you want to prevent duplicate entries without creating a validation formula manually, Kutools for Excel's Prevent Duplicate utility provides a quicker solution. Simply select the target list or column and apply the feature.

Select a list or entire column you need to prevent duplicate entries, and click Kutools > Prevent Duplicate. See screenshot:

After setting, the warning dialog box below will automatically appear when a duplicate entry is detected in the selected list or column.

The Prevent Duplicate utility of Kutools for Excel provides a straightforward way to prevent duplicate entries without creating and maintaining a COUNTIF-based Data Validation formula.

Preventing new duplicates is only part of the task. If your list already contains data, Kutools for Excel's Select Duplicate & Unique Cells utility can quickly locate the existing duplicate entries for further review or processing.

1. Select the list/column you will detect duplicates, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the opening Select Duplicate & Unique Cells dialog box, check Duplicates (Except 1st one) or All duplicates (Including 1st one) as needed, and click the OK button. See screenshot:

3. A dialog box will pop up and show you how many duplicate cells have been selected. Click the OK button to close it.

The duplicate cells in the specified list or column are now selected, as shown below:

Note: The Select Duplicate & Unique Cells utility can also highlight the duplicate cells by applying a background color if you check the Fill backcolor option. See screenshot:

With this utility, you can choose whether the first occurrence should be included, see how many duplicate cells were found, select the results directly, and optionally highlight them for easier review.