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

Classic Pivot Table Layout View For all of you old schoolers out there who used to use the drag and drop Pivot Table option in Excel 2003 or prior, well you still have that option in Excel 2010 and onwards.To activate this option you need to Right Click in your Pivot Table and choose Pivot Table Options. ...
Show The Percent of Parent Total With Excel Pivot ...  Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT TOTAL calculation.In the example below I show you how to get the Percent of a Product Total i.e The % Sales of each Product ...
Different Ways to Filter an Excel Slicer Excel Slicers are the best thing since sliced bread!They are a new feature from Excel 2010 onward and Mac for Excel 2016 and are visual buttons that shows you what items have been filtered or selected in a Pivot Table.There are several ways that you can filter an Exce...
Customize an Excel Slicer  Excel Slicers are very boring looking and there are only 14 different Slicer Styles to choose from, ranging from Light to Dark and with a few colors to choose from.What if you wanted to create your own Slicer using your creative mind with super cool colors and f...