In Excel, we need to perform various count operations, i.g. count text cells, count unique, counting duplicates, and many more. Today we are going to show you how to count unique (distinct as well) values in Excel using the Pivot Table.
First things first, let’s get to know about the dataset that is the base of our examples.
Here, we have a table that contains several movies with their lead actor and releasing year. Using this dataset we will count unique values with the help of the Pivot Table.
Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.
You are welcome to download the practice workbook from the link below.
Count Unique Values Using Pivot
1. Using A Helper Column with Pivot Table
We can use a helper column prior to the Pivot operation. This is an easy way to count the unique and distinct values.
For populating the helper column we can use the COUNTIF function. This function will count cells in a range that meets a single condition.
Let’s write the formula
The formula counted the occurrences of the criteria value cell reference C4 within the C4:C23 range.
Let’s exercise the Excel AutoFill feature to populate the rest of the cells.
Now, select the entire table and click Pivot Table in the Tables section from the Insert tab.
A Create PivotTable dialog box will appear in front of you. Make sure the table range is in the Table/Range field.
It’s a good practice to choose New Worksheet to place in the Pivot Table. Then click OK.
Pivot Table (fields and options) will open in another worksheet.
Drag the Actor column to the Rows field and Helper Column to the Values field.
Typically, while dealing with numbers, Pivot Table returns the sum of the column provided into the Values field. So, you will get the sum.
To change that, right-click on the name of the column, different options will come in front of you.
Click Value Field Settings. This will lead you to the Value Field Settings dialog box.
Select Count from the Summarize value field by and click OK. You will find the count of unique (and distinct) values.
If you don’t want to switch from Sum to Count within the Pivot Table operation, you can apply another trick.
First of all, write the following formula to populate the Helper Column.
Here we have counted using the criteria and for each count, we set 1 as the if_true_value in the IF function.
Fill the rest of the rows from the column and then selecting the table click the Pivot Table option.
Then drag and drop the Actor and Helper Column to the Rows and Values respectively. Now, the sum of the helper column will show the count of unique values.
2. Using Pivot Table without any Helper Column
We can use the Pivot Table to count the unique values without any helper column.
Select the table and click Pivot Table from the Table section under the Insert tab.
The Create PivotTable dialog box will appear in front of you. Adjust the range if required.
Remember to check the Add this data to the Data Model, then click OK.
Pivot Table will come in front of you. Drag the Actor column to the Rows field and the Movie column (any column you can take any column, even the Actor column as well) to the Values field.
When we use any text values in the Values field, usually Pivot Table returns the count of the values.
For this example, it returns the count of unique and distinct values. But in most of the occasions, you need to do a few other steps to count the distinct values
Right-click on the Pivot Table column, and select Value Field Settings.
Here you will see an option called Distinct Count (for showing this option we have checked Add this data to the Data Model in the Create PivotTable dialog box). Select this option and click OK.
This will count the unique and distinct values.
That’s all for the session. We have listed several approaches to count unique values in Excel using the pivot table. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.