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

While dealing with a huge amount of data, we often encounter duplicate entry problems in Excel. The most used methods to find duplicates in Excel are the COUNTIF function and Conditional Formatting. Apart from these two, we will see several other methods on How to Find Duplicates in Excel without Deleting. We will use a sample dataset for your better understanding of where the Name column has some duplicate entries.

How to Find Duplicates in Excel Without Deleting


Download Practice Workbook


7 Ways to Find Duplicates without Deleting in Excel

In this article, we see the use of Conditional Formatting and different functions like COUNTIF, IF, EXACT, etc. to find duplicates based on our preferences.


Method 1: Find Duplicates without Deleting Using COUNTIF

The easiest way to find duplicates without deleting them is the COUNTIF function.

Steps:

  • First, type the following formula in cell F5.
=COUNTIF($B$5:$B$15, B5)>1

How to Find Duplicates in Excel Without Deleting COUNTIF

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

How to Find Duplicates in Excel Without Deleting by COUNTIF Function

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

After that, select the entire dataset and press CTRL+SHIFT+L.

Finally, filter the dataset for TRUE values.

Our duplicates result will look like the following image.

How to Find Duplicates in Excel Without Deleting by COUNTIF Function

Read More: Finding out the number of duplicate rows using COUNTIF formula


Method 2: IF Function to Find Duplicates without Deleting

Now, we will use a combination function of IF and COUNTIF to find duplicate entries in Excel.

Steps:

  • First, type the following formula in cell F5.
=IF(COUNTIF($B$5:$B$14,B5)>1,"Yes","")

How to Find Duplicates in Excel Without Deleting by IF Function

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

How to Find Duplicates in Excel Without Deleting by IF Function

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.
Now, we will apply Filter Option in our dataset and filter it by Yes Values. We have done similar in Method 1.
Our final result will look like the following screenshot.

How to Find Duplicates in Excel Without Deleting by IF Function

Read More: Formula to Find Duplicates in Excel (6 Easy Ways)


Method 3: Find 2nd Occurrence of Duplicates

What if we want to find duplicates except for the first occurrence? No worries! There is the way. Let’s see, how to do this.

Steps:

  • First, type the following formula in cell F5.
=IF(COUNTIF($B$5:$B5:$B5, B5)>1, "Duplicate", "")

  • Now, press the ENTER key.

How to Find Duplicates in Excel Without Deleting by IF Function

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.

  • Finally, drag down to AutoFill rest of the series.

How to Find Duplicates in Excel Without Deleting by IF Function for 2nd occurrence

As you can see, James for the first occurrence is not counted as a duplicate.
Finally, Filter the dataset and click on Duplicate for filtering. In case, you cannot recall how to filter, please check Method 1.

How to Find Duplicates in Excel Without Deleting by IF Function and filter

That’s it. Easy.


Method 4: EXACT Function to Find Duplicates

If you closely look at the sample data below, you can notice that james and alice are two new entries. The EXACT function is useful for case-sensitive matches. For better understanding follow the method.

How to Find Duplicates in Excel Without Deleting by EXACT Function

Steps:

  • First, type the following formula in cell F5.
=IF(SUM((--EXACT($B$5:$B$15,B5)))<=1,"","Duplicate")

  • Now, press the ENTER key.

How to Find Duplicates in Excel Without Deleting by EXACT Function

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.

  • Finally, drag down to AutoFill rest of the series.

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

Follow Method 1 in case you cannot recall the filtering method.

Read More: Excel Formula to Find Duplicates in One Column


Similar Readings


Method 5: Find Duplicates without Deleting by Counting

In this method, we will count duplicate values, which will give us an exact number of how many double entries are made. The COUNTIF function will be at our rescue again.

Steps:

  • First, type the following formula in cell F5.
=COUNTIF($B$5:$B$15, $B5)

  • Now, press the ENTER key.

How to Find Duplicates in Excel Without Deleting by Counting

  • Finally, drag down to AutoFill for the rest of the series.

So, what is happening here, the formula is giving us the result of an occurrence in numbers.

How to Find Duplicates in Excel Without Deleting by Counting

Finally, filter the data by unchecking 1, as more than 1 means duplicate here.

Read More: How to Find Duplicates in Excel Workbook (4 Methods)


Method 6: Find Duplicates by Counting 2nd Occurrence

In the previous method, we saw the number of occurrences for each value, we will count the number of occurrences here in this method also, but we want the number of occurrences serially this time.

Steps:

  • First, type the following formula in cell F5.
=COUNTIF($B$5:$B5, $B5)

  • Now, press the ENTER key.

How to Find Duplicates in Excel Without Deleting by Counting 2nd occurrence

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

  • Finally, drag down to AutoFill rest of the series.

How to Find Duplicates in Excel Without Deleting by Counting 2nd occurrence

As we mentioned, the difference in formula with the previous method is in cell reference. Have a close look and hope you will get it. Finally, filter data except number 1.

Follow Method 1, to have an elaborated description of Filtering.

Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)


Method 7: Using Conditional Formatting

In our last method, we will see how to highlight cells with duplicate values using Conditional Formatting to find the duplicates.

Steps:

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

How to Find Duplicates in Excel Without Deleting by Conditional Formatting

  • Now, a dialogue box will pop up, and click OK.

  • That’s it, all the duplicate cells are highlighted.

Read More: Excel Top 10 List with Duplicates (2 Ways)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

How to Find Duplicates in Excel Without Deleting


Conclusion

That’s all for the article. These are 7 different methods on how to find duplicates in Excel without deleting them. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo