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.

**Table of Contents**hide

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

- Now, press the
**ENTER**key.

- Finally, 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**.

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.

### 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","")`

- Now, press the
**ENTER**key.

- Finally, 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**.

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.

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

Here, ** COUNTIF($B$5:$B5:$B5, B5)>1** will give us the output

**FALSE**, as it is the first occurrence, not the duplicate. Then

**will give the final output as a blank cell.**

` =IF(FALSE, "Duplicate", "")`

- Finally, drag down to
**AutoFill**rest of the series.

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

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.

**Steps:**

- First, type the following formula in cell
**F5**.

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

- Now, press the
**ENTER**key.

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

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

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

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

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

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.

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

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

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

- Thatâ€™s it, all the duplicate cells are highlighted.

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

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