Md. Shamim Reza
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.
➤ 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. 👇
➤ 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)
