# 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`

• Press ENTER.

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.

• 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

## Related Articles

<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF