How to Count Duplicates in Excel

This article will discuss various ways to count duplicates in Excel. In our methods, we will utilize different features and functions of Excel. While we work with datasets in Excel, sometimes we encounter duplicates. We may need to count the duplicates.

In addition, we will show the way to count duplicates with multiple criteria, the procedure to remove duplicates, etc.

Overview to Count Duplicates

1. Using COUNTIF Function to Count Duplicates in Excel (Including First Occurrence)

In the first method, let’s hover over the procedure to use the COUNTIF function to count duplicates, including the first occurrence.

  • Just apply the following formula to count duplicates with the first occurrence in the Grade column.
  • Finally, use the Fill Handle to copy the formula for the cells below.

Count Duplicates with First Occurrence in Excel

2. Inserting COUNTIF Function for Counting Duplicates in Excel (Excluding the First Occurrence)

We will count duplicates like the previous method except for this time, excluding the first occurrence.

  • Similarly, apply the following formula to count duplicates, except this time without the first occurrence.

Count Duplicates with First Occurrence in Excel

3. Combining SUM and EXACT Functions to Count Case-Sensitive Duplicates

We haven’t considered the case’s sensitivity so far. Let’s use the SUM and the EXACT functions to count case-sensitive duplicates.

  • Simply insert the following formula in a new cell to count case-sensitive duplicates.

Counting Case Sensitive Duplicates

4. Inserting Pivot Table to Count Duplicates in Excel

We can also insert the Pivot table to count duplicates. We will show you the way.

  • Firstly, select the whole dataset.
  • Then, go to Insert tab > Tables > Pivot Tables.

Create Pivot Table to Count Duplicates in Excel

  • Further, in the coming dialog box, select Existing Worksheet and select the Range for pivot output if you want the pivot in the same worksheet.

Selecting Range for Pivot

  • Lastly, in the Pivot Table Field from the right pane, take the Grade field in the Row and values sections.

7-Selecting Fields for Pivot

  • Finally, you will see the pivot counting the duplicates in the Grade column.

Counting Duplicates with Pivot Table

How to Count Duplicate Rows in Excel

Sometimes, a whole row can be a duplicate. Let’s see how to count them.

  • Simply, apply the following formula.

Count Duplicates Rows in Excel

How to Count Duplicate Values with Multiple Criteria in Excel

Now, we will show the procedures to count duplicates if they satisfy multiple criteria. For demonstration in our dataset, we will count the duplicates in column Region using multiple conditions from columns Region and Product.

  • Just insert the following formula in a new cell.

Duplicates Based on Multiple Criteria

How to Remove Duplicates in Excel

Finally, let’s see a simple way to remove duplicates from the dataset.

  • First, select the dataset.
  • Then, go to the Data tab > Data Tools > Remove Duplicates.

Removing Duplicates

  • Finally, we will see the duplicates removed.

Removed Duplicates

Things to Remember

  • Always use the “Absolute Cell Reference ($)” to “Block” the range
  • Also, while counting the case-sensitive duplicates, make sure to apply the formula as an “Array Formula” by Pressing “CTRL+SHIFT+ENTER”
  • In addition, use the unary operator (- -) to transform the result of the “EXACT” function to an array of 0 and 1’s.

Frequently Asked Questions

1. What does “Count Duplicates” mean in Excel?

Ans: “Count Duplicates” in Excel refers to the process of determining the number of occurrences or instances of duplicate values or entries within a column or range of cells in a worksheet.

2. Can I count duplicates based on multiple columns in Excel?

Ans: Yes, you can count duplicates based on multiple columns in Excel by combining criteria using formulas or PivotTables. For formulas, you can use the COUNTIFS function, which allows you to specify multiple conditions to count duplicates across different columns simultaneously.

3. Can I automate the process of counting duplicates in Excel using VBA?

Ans: Yes, you can automate the process of counting duplicates in Excel using VBA (Visual Basic for Applications). By writing a VBA macro, you can define custom logic to scan columns or ranges, count duplicates, and display or store the results in the desired format. This allows you to automate the duplicate counting process and apply it to multiple worksheets or workbooks efficiently.

Download Practice Workbook

You can download the practice workbook here and exercise.


Counting duplicates may come in handy sometimes while working in Excel. In this article, we have shown different methods to count duplicates in Excel. In addition, we have shown the procedures to count duplicates with multiple criteria, the method to remove duplicates, etc. I hope this article will help you count duplicates. Please leave comments if you have any.

Count Duplicates in Excel: Knowledge Hub


<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF