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.
- 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
- From the column B we can see that the distinct user name count is 4 ( exclude the duplicated user name jerry li ).
- Now I will tell you how to use pivot table to count the distinct value in excel.
- Select the cell range A2:A6.
- Then click the Insert tab —> Tables group —> PivotTable down arrow —> From Table/Range menu item.
- Then it will popup the PivotTable from table or range dialog window.
- In the popup window, check the radio button New Worksheet.
- 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.
- 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.
- You can see the PivotTable Fields panel on the newly created worksheet right side.
- Click the Active item in the PivotTable Fields panel top area and then expand the Range item.
- Then you can see there is a User Name field below the Range item.
- Check the checkbox before the User Name field, then you can see the User Name filed values in the PivotTable.
- 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 ).
- Then you can see the item Count of User Name drop down list in the Σ Values list pane.
- 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.
- Then it will popup the Value Field Settings dialog window.
- Click the Summarize Values By tab in the popup dialog window.
- 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.
- Select the item Distinct Count in the above list panel.
- Click the OK button to save the changes and close the dialog window.
- Then it will display the distinct count of the User Name field in the PivotTable like below.