Excel Pivot Table Distinct Count to count unique value

This Excel tutorial explains how to use distinct count in Pivot Table to count number of unique value in a column grouped by other fields.

Excel Pivot Table Distinct Count to count unique data

In Excel 2013, there is a new aggregate function in Pivot Table called Distinct Count, which counts number of unique value in a column. For example, if a column contains employee names, you can use the distinct count function to count number of unique employee names in the column such as below.

In this tutorial, I am going to demonstrate how to do distinct count.

Example – distinct count number of unique employee names by department

Suppose we have a staff list below. We want to see how many unique employee names are in the same department.

 

Select the concerned data, navigate to Insert > Pivot Table, then in the Create PivotTable dialog, check the box Add this data to the Data Model > OK

This option is very important as Distinct Count function will not be available if you don’t check this box.

 

In the Pivot Table, drag Department and Employee Name to the Rows, drag Employee Name to the Values. By default, the aggregate function on the value is Count.

 

Click on the arrow next to Count of Employee Name, select Value Field Settings

In the Value Field Settings, select Distinct Count > OK

 

Now the Pivot Table displays the distinct count of employee name by department and display each all the names under each department.

Overall, there are a total of 17 staff, as there are two Cathy and two Icy, the distinct count of employee name in the whole company is 15.

 

Alternatively, display the distinct count without displaying the employee name.

 

Outbound References

https://support.office.com/en-us/article/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273

Wyman W
Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *