How to count duplicate values in a column in Excel?
In the realm of data analysis and management, identifying and counting duplicate values in a dataset is a crucial task. Excel, provides several methods to count duplicate values in a column efficiently. This article explores various techniques to accomplish this, from simple functions to more advanced features, helping you choose the best approach based on your dataset size and specific requirements.
For example, if you have a list of data in a worksheet containing both unique and duplicate values, you may not only want to count the frequency of these duplicates but also determine the order of their occurrences as following screenshot shown.
Count the frequency of duplicates in Excel
In Excel, you can use COUNTIF function to count the duplicates.
Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:
Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the Column A indicates column of data, and A2 stands the cell you want to count the frequency, you can change them as you need).
Track the order of occurrence of duplicates in Excel
But if you want to identify the order of the occurrence of the duplicates, you can use the following formula.
Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A2,A2) (the range $A$2:$A2 indicates the list of data, and A2 stands the cell you want to count the order, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:
Count occurrences of each duplicate in a column with Kutools for Excel
Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch count the occurrences of each items in a column (the Fruited Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below:
1. Select the table containing the column where you will count each duplicate, and click Kutools > Merge & Split > Advanced Combine Rows.
2. In the Advanced Combine Rows, select the column you will count each duplicate and click Primary Key, next select the column you will put counting results in and click Calculate > Count, and then click the OK button. See screenshot:
And now it has counted the occurrence of each duplicate in the specified column. See screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
Demo: count duplicate values in a column in Excel by Kutools for Excel
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
