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.
