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

Filter & Search The Search box within the Filter button is powerful if you know how to use it to your advantage. For example, if you had a list of customers the Search box allows you to find specific customers by typing in a few letters only, allows you to add a selection to your filtered lis...
Top Excel Data Cleansing Techniques Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it. So what is data cleansing? In the words of Oz du Soleil, Excel MVP, it is like "Hercules being sent out to capture the three-he...
Show The Percent of Difference From Previous Years...   I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer. One is using Formulas, but that will take time to set up and you are exposed to errors! The other method is t...
Lock the Excel Pivot Table but not the Slicer Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format. What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interact...