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 values in the Excel cell range.
1. How To Use Pivot Table To Count Distinct Values in Excel.
- Below are the example data cells. Column A contains 5 user names and the user name jerry li is duplicated and case insensitive.
- From column B we can see that the distinct user name count is 4 (excluding the duplicated user name jerry li ).
- Now I will tell you how to use a pivot table to count the distinct values 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 pop up the PivotTable from table or range dialog window.
- In the popup window, check the radio button New Worksheet.
- 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, and then it will open a new worksheet and create the Pivot Table in the worksheet.
- You can see the PivotTable Fields panel on the newly created worksheet’s 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 pop up 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.