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.

The overview image of the methods to count duplicates in Excel PivotTable


Download Practice Workbook


3 Simple Steps to Count Duplicates in Excel Pivot Table

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.

Sample dataset


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.

Using Insert tab to create a PivotTable

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

PivotTable form table or range dialogue box

  • 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.

Specifying different fields in the PivotTable form table or range dialogue box

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

PivotTable Fields dialogue box


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.

Defining PivotTable Fields to count duplicates in Excel


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.

Output obtained after counting duplicates in Excel PivotTable


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.

Using COUNTIF function to identify the duplicate values

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

Using Fill Handle to get the remaining outputs in the Count D column

PivotTable Fields dialogue box

  • 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.

Specifying various parameters inside the PivotTable dialogue box

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.

Final output obtained after Counting Distinct Values in Excel Pivot Table

Related Content: How to Count Duplicates in Column in Excel (3 Ways)


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.

Using Insert tab to create a PivotTable

  • 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.

Modifying options in the PivotTable form table or range dialogue box

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

PivotTable Fields dialogue box

  • 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.

Specifying Rows and Columns of the PivotTable

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

Selecting Value Filed Settings option

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.

Choosing Distinct Count option in the Value Field Settings dialogue box in Excel

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

Output obtained after Counting Distinct Values in Excel Pivot Table


Practice Section

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

Sample Practice Section provided in each worksheet of the Practice Workbook

Read More: How to Count Duplicate Values Only Once in Excel


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo