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

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.

How to Find Duplicate Values in Excel Using Formula


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

Identify If a Value Is Duplicate with the COUNTIF Function


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

Apply the COUNTIF Function for Any Extended Dataset to Find the Duplicates


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

Combine IF and COUNTIF Functions to Mark the Duplicate Values

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


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

Count the Occurrences of the Duplicates Using a COUNTIF Formula


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

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


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

Get the Duplicate Values without the First Occurrence with an IF-COUNTIF Formula


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

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


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.

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

🔎 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”


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

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

🔎 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


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.

Find Duplicate Values in Excel with Conditional Formatting

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

Find Duplicate Values in Excel with a PivotTable


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.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo