# How to Count Duplicates with Pivot Table in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to count the duplicates in an Excel PivotTable for easy calculation. The Excel PivotTable is an amazing feature. In this article, we are going to learn how to count duplicates inÂ Excel Pivot Table with some easy steps. Now, letâ€™s start this article and explore these methods.

The following image demonstrates an overview of the steps discussed in this article.

## How to Count Duplicates with Pivot Table in Excel: 3 Simple Steps

In this section of the article, we will learn three simple steps to count duplicates in Excel Pivot Table. Letâ€™s say, we have the List of Regions for Employees of ABC Company as our dataset. Our goal is to count the duplicate values from the Employee column. Now, letâ€™s use the steps mentioned in the following section to do this.

### Step 01: Insert Pivot Table

In the first step, we will insert the PivotTable using our dataset. So, letâ€™s follow the instructions outlined below.

• Firstly, select the entire dataset and then go to the Insert tab from Ribbon.
• After that, click on the PivotTable option from the Tables group.

As a result, the PivotTable form table or range dialogue box will open on your worksheet.

• Now, click on the Existing Worksheet option in the PivotTable form table or range dialogue box.
• Then, click on the Location field.
• Following that, select cell B12 as the destination cell of the PivotTable.
• Lastly, click on OK.

Consequently, the PivotTable Fields dialogue box will appear on your worksheet as shown in the following image.

### Step 02: Define PivotTable Fields

In the second step, we will define the PivotTable Fields to count the duplicates. Now, letâ€™s follow the steps given below.

• Firstly, drag the Employee option to the Rows section in the PivotTable Fields dialogue box.
• Subsequently, again drag the Employee option to the Values section as demonstrated in the following picture.

### Step 03: Find Count of Duplicates

At this stage our Pivot Table is ready. Now, we need to identify the duplicates from the Pivot Table.

All the values that are 1 in the Count of Employee column are unique values. On the other hand, the values that are greater than 1 are our duplicate values.

• Now, identify any value that is greater than 1 in the Count of Employee column. In this case, Bob is the duplicate value.

## How to Count Distinct Values in Excel Pivot Table

In this section of the article, we will discuss two different methods to count distinct values in Excel PivotTable. The distinct values are defined by such values that are present at least one time in a dataset. For example, if a value is repeated three times in a dataset, it will still be counted as one distinct value.

### 1. Using Helper Column

Inserting a helping column is the most available way to count distinct values in Excel pivot table. Here, we will use the same dataset that we used in the previous method. Now, letâ€™s follow the step outlined in the following section.

Steps:

• Firstly, insert a helper column, â€˜Count Dâ€™ in the F4:F10 range.
• Next, select cell F5 and type the following formula.
`=IF(COUNTIFS(\$C\$5:C5,C5,\$B\$5:B5,B5)>1,0,1)`

Formula Breakdown

• COUNTIFS(\$C\$5:C5,C5,\$B\$5:B5,B5): Excel COUNTIFS function will return the number of cells from the given criteria range. Here this will return the number of C5 that exist between the criteria range1 \$C\$5:C5 Â & B5 from the criteria range2 \$B\$5:B5. We will need to make the first point of the range absolute to make it fixed. That means it wonâ€™t change by dragging.
• IF(COUNTIFS(\$C\$5:C5,C5,\$B\$5:B5,B5)>1,0,1):Â  Excel IF function will return 1 if the name is found for the first time and 0 if it is seen again.

• After that, press ENTER and use the Fill Handle tool to autofill the below cells.

• Now, drag the Location option to the Rows section in the PivotTable Fields dialogue box.
• Following that, drag the Count D option to the Values section as shown in the following image.

Consequently, the following PivotTable will appear on your worksheet. This indicates that the South and the West region have two distinct values. Whereas, the East region has only one distinct value although it has two entries.

Related Content: How to Ignore Blanks and Count Duplicates in Excel

### 2. Utilizing Data Model to Count Distinct Values

Utilizing the Data Model is another smart way to count distinct values in Excel PivotTable. Now, letâ€™s follow the instructions discussed below to do this.

Steps:

• Firstly, choose the entire dataset and then go to the Insert tab from Ribbon.
• Following that, click on the PivotTable option from the Tables group.

• Now, the PivotTable form table or range dialogue box will open on your worksheet. Then, click on the Existing Worksheet option there.
• Subsequently, click on the Location field.
• After that, select cell B12 as the destination cell of the PivotTable.
• Next, check the field named Add this data to Data Model.
• Finally, click on OK.

As a result, the PivotTable Fields dialogue box will open on your worksheet as demonstrated in the image below.

• Now, drag the Location option to the Rows section in the PivotTable Fields dialogue box.
• After that, drag the Employee option to the Values section as shown in the following picture.

• Subsequently, click on the Count of Employee option as marked in the picture below.
• Then, choose the Value Field Settings from the available options.

As a result, the Value Filed Settings dialogue box will be available on your worksheet.

• Now, select the Distinct Count option in the Value Field Settings dialogue box.
• Finally, click OK.

Consequently, the distinct values from the dataset will be available in the PivotTable shown in the following image.

## Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to count duplicates in Excel Pivot Table. If you have any questions, suggestions, or feedback related to this article, you can comment below.

## Related Articles

<< Go Back to Count Duplicates in ExcelÂ | Duplicates in Excel | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF