Count Distinct Values in Excel Pivot Table (Easy Step-by-Step Guide)

If you want to count how many different customers bought from each store, a Pivot Table looks like the obvious tool.

Excel Pivot Tables are amazing (I know I mention this every time I write about Pivot Tables, but it’s true).

With a basic understanding and a little drag and drop, you can get a bucket-load of work done in a few seconds.

But when you drop the Customer field in the Values area, you get the number of orders, not the number of customers.

And this is because a regular Pivot Table counts every row, including the repeats.

But nothing to worry about. The Distinct Count option you need is already there, it just takes a few extra steps.

In this tutorial, I will show you how to count distinct values as well as unique values in an Excel Pivot Table, and what to do if Distinct Count is not showing up.

But before I jump into how to count distinct values, it’s important to understand the difference between ‘distinct count’ and ‘unique count’.

Distinct Count Vs Unique Count

While these may seem like the same thing, it’s not.

Below is an example where there is a list of customer names, and I have listed the distinct and unique names separately.

Unique values/names are those that only occur once. This means that all the names that repeat and have duplicates are not unique.

Unique names are listed in column E in the above dataset.

Distinct values/names are those that occur at least once in the dataset. So if a name appears three times, it’s still counted as one distinct name.

This can be achieved by removing the duplicate values/names and keeping all the distinct ones. Distinct names are listed in column C in the above dataset.

In the example file, I’ve used the UNIQUE function to get both lists. UNIQUE needs Excel 2021 or Microsoft 365, but you don’t need it for anything that follows.

Based on what I have seen, most of the times when people say that they want to get the unique count in a Pivot Table, they actually mean distinct count.

And that’s what I am covering first in this tutorial.

Count Distinct Values in Excel Pivot Table

Suppose you have the order data as shown below:

It has 18 orders from three stores (Austin, Boston, and Denver). Some customers have ordered more than once, and some have ordered from more than one store.

With the above dataset, let’s say that you want to find the answer to the following questions:

1. How many customers are there in each store (which is nothing but the distinct count of customers in each store)?
2. How many customers bought Electronics in each store?

While Pivot Tables can instantly summarize the data with a few clicks, to get the count of distinct values, you will need to take a few more steps.

If you’re using Excel 2013 or a later version on Windows (including Microsoft 365), there is an inbuilt functionality in Pivot Table that quickly gives you the distinct count.

And if you’re using Excel for Mac or Excel 2010 and earlier versions, you will have to modify the source data by adding a helper column.

The following two methods are covered in this tutorial:

• Adding the data to a Data Model and using the Distinct Count option (Excel 2013 and later versions on Windows).
• Adding a helper column in the original data set (works in all versions, including Excel for Mac).

There is a third method which Roger shows in this article (which he calls the Pivot the Pivot Table method).

Let’s get started!

Get a Distinct Count in a Pivot Table Using the Data Model

Pivot Table added new functionality in Excel 2013 that allows you to get the distinct count while summarizing the data set.

It’s available in every Windows version since then, including Microsoft 365.

In case you’re using Excel for Mac or an older version, you’ll not be able to use this method (use the helper column method instead).

Below I have the order data where I want to get the distinct count of customers in each store.

Below are the steps to get a distinct count value in the Pivot Table:

1. Select any cell in the dataset, click the Insert tab, and then click on PivotTable (or use the keyboard shortcut Alt + N + V + T in Microsoft 365, or Alt + N + V in older versions).

2. In the PivotTable from table or range dialog box, make sure that the Table/Range is correct and New Worksheet is selected.

3. Check the box which says “Add this data to the Data Model”.

4. Click OK.

The above steps would insert a new sheet which has the new Pivot Table.

5. Drag the Store field in the Rows area and the Customer field in the Values area.

The above Pivot Table gives the total count of orders in each store (7 in Boston, 6 in Denver, and 5 in Austin), and not the distinct count of customers.

To get the distinct count in the Pivot Table, follow the below steps:

1. Right-click on any cell in the ‘Count of Customer’ column and click on Value Field Settings.

2. In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it).

3. Click OK.

You will notice that the name of the column changes from ‘Count of Customer’ to ‘Distinct Count of Customer’. You can change it to whatever you want.

Now you get 3 customers in Austin and 4 each in Boston and Denver.

Also, notice that the Grand Total is 8 and not 11. Priya, Marcus, and Tom have ordered from two stores, but they are still counted once in the total.

Some things you should know when you add your data to the Data Model:

• If you save your data in the data model and then open it in an older version of Excel, it will show you a warning – ‘Some pivot table functions will not be saved’. You may not see the distinct count (and the data model) when opened in an older version that doesn’t support it.
• When you add your data to a Data Model and make a Pivot Table, it will not show the options to add calculated fields and calculated items.

Count Distinct Values With a Filter in a Pivot Table

Now let’s answer the second question, which is how many customers bought Electronics in each store.

This is where the Data Model method is really useful. Since the distinct count is calculated inside the Pivot Table, you don’t need to change anything in the source data.

Here is the Pivot Table we created with the Data Model method above, which shows the distinct count of customers in each store.

Below are the steps to get the distinct count of customers who bought Electronics:

1. Drag the Category field in the Filters area.

2. Click the drop-down in the Category filter above the Pivot Table, select Electronics, and click OK.

Here is the result:

Now the Pivot Table shows 2 customers in Austin, 2 in Boston, and 3 in Denver.

The Grand Total is 6 and not 7, as Priya bought Electronics in both Boston and Denver, and she is counted once.

You can do the same with any other field. For example, drag the Category field to the Rows area (below Store) to see the distinct customers for each store and category.

Distinct Count Not Showing in Pivot Table?

If you open Value Field Settings and can’t find Distinct Count in the list, it’s almost always because the Pivot Table was not created with the Data Model.

Distinct Count only shows up in Pivot Tables where you checked ‘Add this data to the Data Model’ while creating them.

A regular Pivot Table only has options like Sum, Count, and Average.

And you can’t switch an existing Pivot Table to the Data Model. You’ll have to create a new Pivot Table from the same data, and this time check that box in the dialog box.

If you’re using Excel for Mac, you won’t find this checkbox at all, and Distinct Count may show up greyed out.

Excel for Mac doesn’t support the Data Model, so use the helper column method below.

Count Distinct Values Using a Helper Column

Note: If you’re using Excel 2013 or later on Windows, you can skip this method and use the Data Model method above.

It uses an inbuilt Pivot Table functionality – Distinct Count.

This is an easy way to count distinct values in the Pivot Table as you only need to add a helper column to the source data. It works in all versions, including Excel for Mac.

Once you have added a helper column, you can then use this new data set to calculate the distinct count.

While this is an easy workaround, there are some drawbacks to this method (covered later in this section).

Let me first show you how to add a helper column and get a distinct count.

Suppose I have the data set as shown below, and I want to count the distinct customers in each store:

Add the following formula in cell F2 (in a new column called Customer Count) and copy it down for all the cells that have data in the adjacent columns.

=IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,1,0)

Below is how your dataset would look like when you have added the helper column (1 for the first order from each customer at a store, and 0 for the repeats).

The above formula uses the COUNTIFS function to count the number of times a customer name appears in the given store.

Also, note that the criteria ranges are $B$2:B2 and $C$2:C2. This means that they keep expanding as you go down the column.

For example, in cell F2, the criteria ranges are $B$2:B2 and $C$2:C2, and in cell F3 these ranges expand to $B$2:B3 and $C$2:C3.

This ensures that the COUNTIFS function counts the first instance of a name as 1, the second instance of the name as 2, and so on.

Since we only want to get the distinct names, the IF function is used which returns 1 when a name appears for a store the first time and returns 0 when it appears again.

This makes sure that only distinct names are counted and not the repeats.

Now that we have modified the source data, we can use this to create a Pivot Table and use the helper column to get the distinct count of customers in each store.

Below are the steps to do this:

1. Select any cell in the dataset, click the Insert tab, and then click on PivotTable (or use the keyboard shortcut Alt + N + V + T in Microsoft 365, or Alt + N + V in older versions).

2. In the dialog box, make sure that the Table/Range is correct (and includes the helper column) and New Worksheet is selected.

3. Click OK.

The above steps would insert a new sheet which has the Pivot Table.

4. Drag the Store field in the Rows area and the Customer Count field in the Values area.

This gives you 3 distinct customers in Austin and 4 each in Boston and Denver. Now you can change the column header from ‘Sum of Customer Count’ to ‘Distinct Customers’.

Important: Don’t rely on the Grand Total here. It adds up the store counts (11), while there are only 8 distinct customers, as some shop at more than one store.

In the example file, I have turned off the Grand Total for this Pivot Table for the same reason.

Drawbacks of Using a Helper Column:

While this method is pretty straightforward, I must highlight a few drawbacks that come with modifying the source data in a Pivot Table:

• The data source with the helper column is not as dynamic as a Pivot Table. While you can slice and dice the data any way you want with a Pivot Table, when you use a helper column, you lose a part of that ability. Let’s say that you add a helper column to get the distinct count of customers in each store. Now, what if you also want to get the distinct count of customers who bought Electronics? You will have to go back to the source data and modify the helper column formula (or add a new helper column).
• The helper column only works for the fields used in its formula (Store and Customer here). If you drag another field, such as Category, into the Rows area, you will get wrong numbers.
• Since you’re adding more data to the Pivot Table source (which also gets added to the Pivot Cache), this can lead to a higher size of Excel file.
• Since we are using an Excel formula, it may make your Excel Workbook slow in case you have thousands of rows of data.

How to Count Unique Values in a Pivot Table

If you want to count unique values (and not distinct values), you don’t have any inbuilt functionality in the Pivot Table and will have to rely on helper columns only.

Remember – Unique values and distinct values are not the same. Click here to know the difference.

And if you only need the unique count with a formula (and not in a Pivot Table), here’s how to count unique values using the COUNTIF function.

One example could be when you have the below data set and you want to find out how many customers are unique to each store.

This means that they buy from one specific store only and not the others.

Note that here, a unique customer is one who shops at only one store, no matter how many orders they place there.

For example, Sofia has two orders, both from Austin. So she is a unique Austin customer. Tom has ordered from Austin and Denver, so he is not unique to either store.

In such cases, you need to create one or more than one helper columns.

For this case, the below formula does the trick:

=IF(AND(COUNTIFS($C$2:$C$19,C2,$B$2:$B$19,"<>"&B2)=0,COUNTIF($C$2:C2,C2)=1),1,0)

The above formula checks whether a customer name occurs in one store only or in more than one store.

It does that by counting the number of times the name appears with a store other than the one in that row. If this count is 0, the customer only shops at this store.

In case the name occurs in more than one store, the formula returns 0 in every row for that customer.

The formula also checks whether the name is repeated or not. If the name is repeated, only the first instance of the name returns the value 1, and all other instances return 0.

Now you can create a Pivot Table from this data (using the same steps shown above), with the Store field in the Rows area and the One-Store Customer field in the Values area.

This shows 2 unique customers in Austin, 2 in Boston, and 1 in Denver.

And this time, the Grand Total of 5 is correct, as each unique customer gets a 1 only once in the entire dataset.

This may seem a bit complex, but it again depends on what you’re trying to achieve.

So, if you want to count unique values in a Pivot Table, use helper columns.

And if you want to count distinct values, you can use the inbuilt Distinct Count option (Excel 2013 and above on Windows) or use a helper column.

I hope you found this tutorial useful.

You May Also Like the Following Pivot Table Tutorials:

• How to Filter Data in a Pivot Table in Excel
• How to Group Dates in Pivot Tables in Excel
• How to Group Numbers in Pivot Table in Excel
• How to Apply Conditional Formatting in a Pivot Table in Excel
• Slicers in Excel Pivot Table
• How to Refresh Pivot Table in Excel
• Delete a Pivot Table in Excel
• Pivot Table Sorting
• Pivot Table Limitations