Pivot Tables is a vital Excel tool used to perform numerous functions. One of the essential uses of Pivot Tables is counting the distinct and unique values. Unique values are those values that occur once in the dataset, while on the other side, the distinct values are those that appear at least once in the dataset. This post will discuss how to count distinct values in Pivot Tables.
Using the Value Setting Tool
Value Setting Tool can be used to count the distinct values in a Pivot Table. Below are the steps to follow while using this method to count the distinct values:
1. Open the Excel application.
2. Enter the dataset in the empty cells. The dataset can be split into terms of columns. Alternatively, open an existing Excel document with the dataset you wish to convert to a Pivot Table.
3. Highlight the cells that have the dataset. To highlight cells, press the CTRL + A keys or click on the Left mouse and move the cursor along the dataset you wish to highlight.
4. Click on the Insert tab on the Ribbon. The Insert tab is located on the far-left side of the Ribbon and in the Tables section.
5. Then, click on the PivotTable button. In the Create PivotTable dialogue box, ensure the selected cells are captured in the Table/Range section.
6. Next, toggle on the New Worksheet if you want the Pivot Tables to be displayed on a new worksheet. If you need the Pivot table to appear on the same Worksheet, toggle on the Existing Worksheet. If you choose the Existing Worksheet, click on the Location section and add the cell references where the Pivot table will be displayed.
7. Then, click the OK button. The selected dataset will automatically be converted to a Pivot Table.
8. Right-click on the added Pivot Table and choose the Value Field Setting option from the side-view menu.
9. In the Value field Settings, locate the "Summarize value field by" section. From this section, choose the Count option, and click the OK button. That is all you need to do.
Using Data model Tool
Steps to follow while using this method:
1. Open the Excel application.
2. Enter the dataset in the empty cells. The dataset can be split into terms of columns. Alternatively, open an existing Excel document with the dataset you wish to convert to a Pivot Table.
3. Highlight the cells that have the dataset. To highlight cells, press the CTRL + A keys or click on the Left mouse and move the cursor along the dataset you wish to highlight.
4. Click on the Insert tab on the Ribbon. The Insert tab is located on the far-left side of the Ribbon and in the Tables section.
5. Then, click on the PivotTable button. In the Create PivotTable dialogue box, ensure the selected cells are captured in the Table/Range section.
6. Next, toggle on the New Worksheet or Existing Worksheet.
7. Check the "Add this data to the Data Model" checkbox.
8. Finally, hit the OK button.