How to count duplicate values in a column in Excel?
If you have a list of data in a worksheet with unique values and duplicate values, and you do not only want to count the frequency of duplicate values also want to know the order of the occurrence of the duplicate values. In Excel, the COUNTIF function can help you count the duplicate values.
- Count the frequency of duplicates in Excel
- Count the order of occurrence of duplicates in Excel
- Count and Select all duplicate values in a column with Kutools for Excel
- Count occurrences of each duplicate in a column with Kutools for Excel
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).
Quickly select and count all duplicate/unique values from a column in Excel
In general, we can remove duplicates from a list easily by Data > Remove Duplicates in Excel. But how to select duplicate/unique values, or count them from a column? And what if selecting or counting duplicate/unique except the first duplicate one? Try Kutools for Excel’s Select Duplicate & Unique Cells utility!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Count the order of occurrence of duplicates in Excel
But if you want to count 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 and select all duplicates in a column with Kutools for Excel
Sometimes you may want to count and select all duplicates in a specified column. You can get it done easily with Kutools for Excel’s Select Duplicates & Unique Cells utility.
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
1. Select the column or list that you will count all duplicates, and click the Kutools > Select > Select Duplicates & Unique Cells.
2. In the opening Select Duplicate & Unique Cells dialog box, check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need, and click the Ok button.
And then you will see a dialog box comes out and shows how many duplicates are selected, and at the same time duplicates are selected in the specified column.
Note: If you want to count all duplicates including the first one, you need to check the All duplicates (Including 1st one) option in the Select Duplicate & Unique Cells dialog box.
3. Click the OK button.
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
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:
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
1. Select the table containing the column where you will count each duplicate, and click Kutools > Content > 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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Demo: count duplicate values in a column in Excel by Kutools for Excel
Relative Articles:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

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!


























