Excel 2013 added some new features to its arsenal and one that has been well overdue was the distinct or unique count.

Previously when we created a Pivot Table and dropped a Customers field in the Row Labels and then again in the Values area we got the “total number of transactions” for each customer.

But what about if we want to show the total unique customers?

In Excel 2013 we can, by using the newly created Pivot Table Data Model:

STEP 1: Click in your data source and go to Insert > Pivot Table

STEP 2: The important step here is to “check” the Add this to the Data Model box and press OK


STEP 3: This will create a Pivot Table.  Now drop the Customers field in the Row and Values areas which will give you the “total transactions” for each customer

STEP 4: To get a Distinct Count, you need to click on the Values drop down for the Count of Customers and select the Value Field Settings

STEP 5: Under Summarize Values By tab, select the last option, Distinct Count and press OK


See how this is done with the following animated tutorial plus you can download the workbook…


Distinct Count



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Refresh a Pivot Table When the information in your data set gets updated you need to Refresh your Pivot Table to see those changes in your Pivot Table.  There are three ways to do this.  First click on your Pivot Table and: 1. From the Ribbon choose: PivotTable Tools > Options > Refresh 2....
Excel’s Fill Handle Tips This post was inspired by Mr.Excel's new book MrExcel XL: 40 Greatest Excel Tips of All Time and in particular the 3rd tip that I read called The Fill Handle Does Know 1,2,3... Excel knows to fill down/right when you are working with dates, days, months, years and even quarter...
Free Pivot Table Webinar Hey there, I have been having a huge response to this highly acclaimed Pivot Table webinar and will continue to give these lessons in to the new year!  If a date is not suitable for you, you can always come back to the Registration link below on a future date.  Happy Pivot...
Conditionally Formatting A Drop Down List In a previous post I showed you how to Create a Drop Down List in a Table.    We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list. This is useful if you want to highlight when a job is completed, c...