This article illustrates how to **find duplicate values** in excel using a formula. It will be very tedious if you try to find duplicate values manually in a large excel worksheet. This article will help you to save time and effort by providing alternate solutions to that. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.

## Download Practice Workbook

You can download the practice workbook from the download button below.

## 9 Methods to Find Duplicate Values in Excel Using Formula

Imagine you have the following dataset containing the top smartphone brands in the USA. Now follow the methods below to find out if the list contains duplicate values. After that, you can apply it to your dataset.

### 1. Use the COUNTIF Function to Identify If a Value Is a Duplicate

The **COUNTIF function** counts the number of cells within a range that meet a given condition. The **COUNTIF** formula in this method will compare a value to each of the values in the dataset and return the count of its appearances. It will give you the boolean result **TRUE** if the dataset contains duplicate values and **FALSE** otherwise.

📌 **Steps**

- First, enter the following formula in cell
**C5**.

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

- Then press the
**ENTER**key (or use**CTRL+SHIFT+ENTER**combination). - Finally, drag the
**Fill Handle**icon all the way.

We can see the overall procedure and the results from the image below. 👇

### 2. Apply the COUNTIF Function for Any Extended Dataset to Find the Duplicates

You can modify the formula in the earlier method if you have an extended dataset in **Column B**.

📌 **Steps**

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

`=COUNTIF(B:B,B5)>1`

- Next, press
**CTRL+SHIFT+ENTER**. If you are using**MS Office 365**, you can press just**ENTER**instead, for an array formula. - After that, move the
**Fill Handle**icon or double-click on it.

Then you will see the same result as follows. 👇

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

### 3. Combine IF and COUNTIF Functions to Mark the Duplicate Values

You can also combine the earlier formula with the **IF function** to get a more organized and easily understandable result.

📌 **Steps**

- First of all, type the following formula in cell
**C5**. - Then press the
**ENTER**key (or use**CTRL+SHIFT+ENTER**combination).

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

- The
**IF function**in this formula will return**Unique**for values appearing only once and**Duplicate** - After that, drag the
**Fill Handle**icon all the way or double-click on it.

Finally, you will see the following result. 👇

You can change the **“Unique”** argument to double quotes (**“”**) if you are only concerned about the duplicate values. In that case, enter the following formula instead.

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

**Read More:** **Find Matches or Duplicate Values in Excel (8 Ways)**

### 4. Count the Occurrences of the Duplicates Using a COUNTIF Formula

You can also use a formula to count the occurrences of each value in the list.

📌 **Steps**

- Firstly, enter the following formula in cell
**C5**.

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

- Press
**ENTER**or press**CTRL+SHIFT+ENTER**simultaneously.

- Next, use the
**Fill Handle**icon to apply the formula to all the cells below.

Now you will see the same result shown in the picture below. 👇

**Read More: How to Find Duplicates without Deleting in Excel (7 Methods)**

### 5. Modify the COUNTIF Formula to Arrange the Duplicate-Count in an Increasing Order

You can modify the formula used in the earlier method if you want to find the order of occurrences of the values.

📌 **Steps**

- Type the formula given below in cell
**C5**.

Notice carefully how we have used the combination of absolute and relative references in this formula compared to the earlier formulas.

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

- Hit the
**ENTER**button or press**CTRL+SHIFT+ENTER**altogether. - Finally, drag the
**Fill Handle**icon or double-click on it to populate the cells below with this formula.

Then you will get the result shown below. 👇

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

**Similar Readings**

**How to Find Duplicates in a Column Using Excel VBA (5 Ways)****Use VBA Code to Find Duplicate Rows in Excel (3 Methods)****How to Find Duplicates in Two Different Excel Workbooks (5 Methods)****Find Duplicates in Excel Workbook (4 Methods)****How to Vlookup Duplicate Matches in Excel (5 Easy Ways)****How to Compare Two Excel Sheets Duplicates (4 Quick Ways)**

### 6. Find the Duplicate Values without the First Occurrence with an IF-COUNTIF Formula

You can say any value that appears first shouldn’t be considered a duplicate. That means you want to consider the first occurred values as unique. Then you need to apply a modified formula.

📌 **Steps**

- First, enter the formula given below in cell
**C5**.

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

- Press
**CTRL+SHIFT+ENTER**.

- Next, drag the
**Fill Handle**icon or double-click on it.

After that, you will get the following result. 👇

**Read More:** **Excel Formula to Find Duplicates in One Column**

### 7. Combine IF and COUNTIFS to Find If an Entire Row Has Duplicate Values

The **COUNTIFS function** counts the number of cells specified by a set of criteria. You can also use a formula combining **IF** and **COUNTIFS** to find duplicate rows in your dataset.

📌 **Steps**

Assuming that you have data in **Column B** and **Column C**.

- Enter the following formula in cell
**E5**as shown below and hit the**CTRL+SHIFT+ENTER**buttons altogether.

`=IF(COUNTIFS($B$5:B5,B5,$C$5:C5,C5)>1,"Duplicate Row","")`

The **COUNTIFS** function in the formula will check for duplicates in each column.

- Now move the
**Fill Handle**icon all the way.

Then you will see the result shown in the picture below. 👇

**Read More:** **Excel Find Duplicate Rows Based on Multiple Columns**

### 8. Formula with IF, OR, and COUNTIF Functions to Find If Any Duplicate Value Exists in a List

Now you can use an alternate formula with **IF,** **OR**, and **COUNTIF** functions if you are only concerned about finding whether a list contains any duplicates or not.

📌 **Steps**

- First, enter the following formula in cell
**D6**.

`=IF(OR(COUNTIF($B$5:$B$12,$B$5:$B$12)>1),"Yes","No")`

- Hit
**CTRL+SHIFT+ENTER**.

Then you will see a **Yes** in case of the list contains any duplicates and a **No** otherwise.

**🔎 How Does This Formula Work?**

➤ **COUNTIF($B$5:$B$12,$B$5:$B$12)**

The **COUNTIF function** returns the number of cells in the range which meet the given criteria.**Output: {3;1;3;1;2;1;2;3}**

➤ **{3;1;3;1;2;1;2;3}>1**

This returns **TRUE** or **FALSE** whether this condition is met or not.**Output: {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}**

➤ **OR({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE})**

Here the **OR function** returns FALSE, if any of the arguments are FALSE, else it returns TRUE.**Output: TRUE**

➤ **IF(TRUE,”Yes”,”No”)**

Finally, the **IF** function prints “Yes” or “No”, based on the criteria **TRUE** or **FALSE.****Output: “Yes”**

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

### 9. Formula with COUNTA and UNIQUE Functions to Find the Number of Duplicate Values in a Range

You can also apply a formula that uses the **COUNTA **and **UNIQUE **functions.

📌 **Steps**

- Type the following formula in cell
**D10**to find out the number of duplicate values in the range.

`=COUNTA($B$5:$B$12)-COUNTA(UNIQUE($B$5:$B$12))`

- Hit the
**ENTER**key.

The whole procedure and results are illustrated in the following image. 👇

**🔎 How Does This Formula Work?**

➤ **COUNTA($B$5:$B$12)**

The **COUNTA function** returns the number of cells in the range which are not empty.**Output: 8**

➤ **UNIQUE($B$5:$B$12)**

The **UNIQUE function** returns the unique values in the range.**Output: {“Apple”;”Samsung”;”LG”;”Motorola”;”Google Pixel”} **

➤ **COUNTA({“Apple”;”Samsung”;”LG”;”Motorola”;”Google Pixel”})**

Here the **COUNTA function** returns the number of items in the array obtained from the **UNIQUE function**.**Output: 5 **

➤ **8-5**

The subtraction gives the final count of duplicate values in the dataset.**Output: 3**

**Read More:** **How to Find & Remove Duplicate Rows in Excel**

## 2 More Ways to Find Duplicate Values in Excel

We have seen 9 formulas so far, to find duplicate values in Excel. In this section, we will see how you can use **Conditional Formatting** and the **Excel Pivot Table** to easily do the same job.

### 1. Find Duplicate Values with Conditional Formatting

To find the duplicate value with Conditional Formatting, just execute the following steps.

**Steps:**

- First, go to the
**Home**Then select**Conditional Formatting >> Highlight Cell Rules >> Duplicate Values**as shown in the following picture.

- After that select
**OK**in the popup window as shown below. You can change the highlighting color using the dropdown arrow.

- Then you will see the values occurring more than once highlighted as follows.

### 2. Find Duplicate Values with a PivotTable

To find duplicates in a dataset by quickly creating a **PivotTable**, just follow the steps below.

**Steps:**

- First, select anywhere in the dataset. Then select
**Insert >> PivotTable**as shown below.

- Then drag the column name (
**Brands**) of the table both in the**Rows**field and the**Values**field one by one as shown in the picture below.

- After that, you will see the count of each unique item in the
**PivotTable**as follows.

## Things to Remember

- Always use
**CTRL+SHIFT+ENTER**to apply the**array formulas**if you are not using Office365. - Be careful about using proper references in the formulas. Otherwise, you may not get the desired result.

## Conclusion

Now you know how to find duplicate values in excel using formula. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our **ExcelDemy** blog to explore more on excel. Stay with us and keep learning.

**Related Articles**

**How to Find, Highlight & Remove Duplicates in Excel****How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)****Find Duplicates in Two Columns in Excel (6 Suitable Approaches)****Excel Find Similar Text in Two Columns (3 Ways)****How to Find Matching Values in Two Worksheets in Excel (4 Methods)****How to Highlight Duplicate Rows in Excel (3 Ways)**