How To Use Pivot Table Count Distinct Value In Excel

In the previous articles, we told you how to use excel formulas and VBA to count unique and distinct values in excel. In this article, I will tell you about another method that uses a pivot table to count distinct value in the excel cell range.

1. How To Use Pivot Table To Count Distinct Value In Excel.

1. Below is the example data cells. Column A contains 5 user names and the user name jerry li is duplicated and case insensitive.
   

   	A	B
   1	User Name	Distinct User Name
   2	Jerry Li	Jerry Li
   3	jerry li	Tom Ma
   4	Tom Ma	Tom Sun
   5	Tom Sun	Jackie Liu
   6	Jackie Liu

2. From the column B we can see that the distinct user name count is 4 ( exclude the duplicated user name jerry li ).
3. Now I will tell you how to use pivot table to count the distinct value in excel.
4. Select the cell range A2:A6.
5. Then click the Insert tab —> Tables group —> PivotTable down arrow —> From Table/Range menu item.
6. Then it will popup the PivotTable from table or range dialog window.
7. In the popup window, check the radio button New Worksheet.
8. And check the checkbox Add this data to the Data Model in the Choose whether you want to analyze multiple tables section at the dialog bottom area.
9. Click the OK button to save and close the dialog window, then it will open a new worksheet and create the PivotTable in the worksheet.
10. You can see the PivotTable Fields panel on the newly created worksheet right side.
11. Click the Active item in the PivotTable Fields panel top area and then expand the Range item.
12. Then you can see there is a User Name field below the Range item.
13. Check the checkbox before the User Name field, then you can see the User Name filed values in the PivotTable.
14. Drag and drop the User Name field under the Range item to the Σ Values list pane ( on the bottom right corner of the PivotTable Fields panel ).
15. Then you can see the item Count of User Name drop down list in the Σ Values list pane.
16. Click the down arrow at the right side of the drop down list, then click the Value Field Settings… item in the drop down menu list.
17. Then it will popup the Value Field Settings dialog window.
18. Click the Summarize Values By tab in the popup dialog window.
19. Scroll down the list panel under the text Choose the type of calculation that you want to use to summarize data from the selected field.
20. Select the item Distinct Count in the above list panel.
21. Click the OK button to save the changes and close the dialog window.
22. Then it will display the distinct count of the User Name field in the PivotTable like below.