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

2015-11-18_18-58-56

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

2015-11-18_19-01-46

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

DOWNLOAD EXCEL WORKBOOK

Distinct Count

HELPFUL RESOURCE:

728x90

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin