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:

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

Read More: How to Find Duplicate Values Using VLOOKUP in Excel

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

Steps:

• Enter the following formula in cell F5:

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

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

Read More: Excel Formula to Find Duplicates in One Column

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

Steps:

• Enter the following formula in cell F5:

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

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

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

• Drag down to AutoFill rest of the series.

The formula difference from the previous method is in the cell reference.

• Filter the data except number 1.

Follow Method 1 for a description of Filtering.

Method 7 – Use Conditional Formatting to Highlight Duplicate Values

Steps:

• Select the Name column range and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

• A dialogue box will pop up.
• Click OK.

• All the duplicate cells are highlighted.

Read More: Find and Highlight Duplicates in Excel

Download the Practice Workbook

Related Articles

• Find Duplicates in Two Columns in Excel
• How to Find Similar Text in Two Columns in Excel
• How to Find Duplicates in Excel Workbook
• How to Find Duplicates in Two Different Excel Workbooks

<< Go Back to Duplicates in Excel | Learn Excel