How to Count Unique Values in Excel Using Pivot Table

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.

Dataset - Count Unique Values Excel Pivot

 

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.

Practice Workbook

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.

Helper Column - Count Unique Values Excel Pivot

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

=COUNTIF($C$4:$C$23,C4)

COUNTIF formula to populate helper column

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.

AutoFill - Count Unique Values Excel Pivot

Now, select the entire table and click Pivot Table in the Tables section from the Insert tab.

Selecting Pivot Table

A Create PivotTable dialog box will appear in front of you. Make sure the table range is in the Table/Range field.

Create PivotTable dialog box

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.

Pivot Table fields - Count Unique Values Excel Pivot

Drag the Actor column to the Rows field and Helper Column to the Values field.

Fill the fields with values

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.

Value field settings-Count Unique Values Excel Pivot

Click Value Field Settings. This will lead you to the Value Field Settings dialog box.

Select Count from 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.

Count Unique Values Excel Pivot

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.

=IF(COUNTIF($C$4:$C$23,C4)>0,1)

Here we have counted using the criteria and for each count, we set 1 as the if_true_value in the IF function.

IF-COUNTIF to fill the Helper

Fill the rest of the rows from the column and then selecting the table click the Pivot Table option.

Select pivot table after filling helper

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.

Count unique with Sum

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.

Select Pivot Table for data

The Create PivotTable dialog box will appear in front of you. Adjust the range if required.

Select required value in Create PivotTable box

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.

Count the data from values

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.

Select the 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.

Select Distinct Count - Count Unique Values Excel Pivot

This will count the unique and distinct values.

Distinct count to count unique values

Conclusion

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.


Further Readings

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo