While working with a large dataset or merging multiple worksheets into one, there is a possibility that you are getting duplicate values or columns into the worksheet. Sometimes, we may need to count those duplicate values to get a clear concept of the worksheet. However, Excel provides some basic functions and formulas by which you can easily count duplicate values in a column. In this article, I will show you 4 suitable ways to count duplicates in a column in Excel. Hence, read through the article to learn more and save time.
In this tutorial, I will show you how to count duplicates in columns in Excel. Here, I have used 4 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset includes the Name of the employee and Region.
1. Inserting COUNTIF Function in Excel to Count Duplicates in Column
Fortunately, the basic COUNTIF function helps you to find duplicates in columns for two different scenarios. For instance, we will show you two examples of counting duplicates in columns including the first occurrence and excluding the first occurrence. Let’s learn!
1.1 Counting Duplicates Including First Occurrence
Consider a situation where we are given a dataset containing columns Name, and Region of some sales employees. Here, we have to find duplicates in the Region column and we will include the first occurrence. Hence, follow the steps below.
📌 Steps:
- First of all, select cell C13 and write down the following formula.
=COUNTIF(C5:C11,C6)
- At last, hit the Enter key to count the number of duplicates.
1.2 Counting Duplicates Excluding First Occurrence
In this case, we will ignore the first appearance and the count rest of the duplicates in columns. However, the process is similar to the previous one.
📌 Steps:
- In the beginning, select cell C13 and enter the following formula.
=COUNTIF($C$5:$C$11,C6)-1
- In the end, press the Enter button to count the number of duplicates.
Read More: How to Count Duplicates in Two Columns in Excel
2. Combining SUM and EXACT Functions for Counting Case-Sensitive Duplicates
Unfortunately, the COUNTIF function in Excel is case-insensitive. Here, we will use the combination of the EXACT and the SUM functions for case-sensitive duplicates in columns. So, read through the steps mentioned below. For the purpose of the demonstration, we have changed the dataset a little bit.
📌 Steps:
- Initially, click on cell D13 and enter the formula below.
=SUM(--EXACT($D$5:$D$11,D5))
🔎 Formula Breakdown:
- In the Exact function, Text1 is $D$5:$D$11 and the Text2 is D5(A). Usually, it searches Text2 from the range Text1.
- However, to convert the values to an array of 0 and 1’s, the unary operator (–) is used.
- Finally, the SUM function provides the sum of the exact match of cell D5.
- Lastly, press the Enter button in order to get the final output.
Read More: How to Count Duplicate Values in Multiple Columns in Excel
3. Finding Total Duplicates in Column by Joining IF and COUNTIFS in Excel
Furthermore, we will combine the IF and COUNTIFS functions to count total duplicates in a column in Excel. Hence, follow the steps below in order to complete the operation properly. For instance, I have slightly modified the sample dataset.
📌 Steps:
- Firstly, click on cell D5 and enter the formula below.
=IF(COUNTIFS($C$4:$C$11,C5)>1,"DUPLICATE","")
🔎 Formula Breakdown:
- Initially, the COUNTIFS function counts the text in cell C5 from the range $D$5:$D$11.
- Then, the IF function checks the output with the given condition.
- Finally, if the COUNTIFS function counts values more than once, the IF function argument will show “DUPLICATE” otherwise blank.
- Secondly, press Enter and apply the AutoFill tool to the entire column.
- After that, select cell D13 and insert the formula below.
=COUNTIF(D5:D11,D6)
- Finally, hit Enter to find the total count of duplicates.
Read More: How to Count Duplicate Rows in Excel
4. Counting Duplicates in Column with Excel Pivot Table
Last but not least, we will use a Pivot Table to count duplicates in columns in Excel. However, this method is a handy and easy way to do the task. Hence, go through the steps mentioned below.
📌 Steps:
- Firstly, we will select the dataset by selecting cells B4:C11.
- Secondly, go to the Insert tab and from the PivotTable group, we will select From Table/Range.
- At this point, a PivotTable from table or range dialog box will appear.
- Then, check the Existing Worksheet and select cell F4 in the Location box.
- Furthermore, click OK.
- Then, a PivotTable Fields dialog box will appear at the right end of the Excel sheet.
- Afterwards, drag Region in the Rows and Values group.
- Finally, you can see the duplicate count of the Region in the Pivot Table.
Read More: Count Number of Occurrences of Each Value in a Column in Excel
Things to Remember
- First, use the Absolute Cell Reference ($) to Block the range always.
- Second, while counting the case-sensitive duplicates, make sure to apply the formula as an Array Formula by Pressing CTRL+SHIFT+ENTER simultaneously.
- Last, use the unary operator (–) to transform the result of the EXACT function to an array of 0 and 1’s.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow to count duplicates in columns in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- Count the Order of Occurrence of Duplicates in Excel
- How to Count Duplicates Based on Multiple Criteria in Excel
- How to Count Occurrences Per Day in Excel
<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
