How to Find Duplicates without Deleting in Excel (7 Methods)

Method 1 – Use the COUNTIF Function to Find Duplicates in Excel without Deleting

Steps:

Enter the following formula in cell F5:

=COUNTIF($B$5:$B$15, B5)>1

How to Find Duplicates in Excel Without Deleting COUNTIF

Press ENTER.

Drag down to AutoFill rest of the series.

COUNTIF functions return output TRUE for duplicate items in a specified range and FALSE for Unique Values.

Select the entire dataset and press CTRL+SHIFT+L.

Filter the dataset for TRUE values.

Our duplicate result will look like the following image.

Method 2 – Insert the Excel IF Function to Find Duplicates without Deleting

Steps:

Enter the following formula in cell F5:

=IF(COUNTIF($B$5:$B$14,B5)>1,"Yes","")

Press ENTER.

Drag down to AutoFill rest of the series.

We already know the COUNTIF function returns TRUE as a result for duplicate values and FALSE for Unique ones. Here, COUNTIF($B$5:$B$14,B5)>1 yields the result TRUE and =IF(TRUE,”Yes”,””) formula gives final output Yes for TRUE and Blank cells if FALSE.

Apply the Filter Option to our dataset and filter it by Yes Values. We did something similar in Method 1.

Our final result will look like the following screenshot.

Method 3 – Find the 2nd Occurrence of Duplicates in Excel

Steps:

Enter the following formula in cell F5:

=IF(COUNTIF($B$5:$B5:$B5, B5)>1, "Duplicate", "")

Press ENTER.

Here, COUNTIF($B$5:$B5:$B5, B5)>1 will give us the output FALSE, as it is the first occurrence, not the duplicate. Then =IF(FALSE, "Duplicate", "") will give the final output as a blank cell.

Drag down to AutoFill rest of the series.

James, for the first occurrence, is not counted as a duplicate.

Filter the dataset and click on Duplicate. If you cannot recall how to filter, please check Method 1.

Method 4 – Apply the EXACT Function to Find Duplicates without Deleting

Steps:

Enter the following formula in cell F5:

=IF(SUM((--EXACT($B$5:$B$15,B5)))<=1,"","Duplicate")

Press ENTER.

SUM((--EXACT($B$5:$B$15,B5)))<=1 gives us the result TRUE as it does not count james as James’s duplicate. IF(TRUE,””,”Duplicate”) will yield the final output as blank cell.

Drag down to AutoFill rest of the series.

Filter the data by Duplicate values. Our final result will look like the following image.

Follow Method 1 if you cannot recall the filtering method.

Method 5 – Find Duplicates without Deleting by Counting

Steps:

Enter the following formula in cell F5:

=COUNTIF($B$5:$B$15, $B5)

Press ENTER.

Drag down to AutoFill for the rest of the series.

The formula gives us the result of an occurrence in numbers.

Filter the data by unchecking 1; more than 1 means duplicate here.

Method 6 – Count the 2nd Occurrence of Duplication in Excel

Steps:

Enter the following formula in cell F5:

=COUNTIF($B$5:$B5, $B5)

Press ENTER.

The difference between the previous formula and this formula is that earlier, we used B5:B15 as a range with absolute reference, whereas this time, we used $B$5:B5, a mixed reference, as the range. So, gradually, the range will change, and so will the number.