How to get a distinct count in a Pivot Table

Do you need to know how many customers you have invoiced this month?  When creating a Pivot Table and adding your customers to both the row labels and again in the value area, each transaction is totaled for each customer.  This does not give a true reflection of how many customers you have invoiced.

In Microsoft® Excel® 2013 and 2016, a new feature called “Distinct Count” was added which will return an accurate count of unique customers.

Applies To: Microsoft® Excel® 2013 and 2016.

1. Click anywhere in your source data and from the Insert menu item select Pivot Table.

2. The next step, which is vital, is to select “Add this data to the Data Model”.

Image 1

3. The Pivot Table is now created and ready for use. Drag and drop “CustomerName” in the Row and Values areas.

Image 2

4. Your Pivot Table will now display, as can be seen below, which is not a true reflection of how many customers have been invoiced, but a count of how many transactions took place.

Image 3

5. To get a unique count of customers, click on the “count of CustomerNames” drop down and select “Value Field Settings”

Image 4

6. Under the “Summarize Value Field By” section, scroll down to the bottom and select “Distinct Count” and then OK.

Image 5

7. When following the above steps an accurate count of the number of customers invoiced will be displayed.

Instead of applying complex formulas, you can use the Distinct Count option to have an accurate count of transactions. This will save time and lead to better decision making as the correct information will be used.