{"id":1967,"date":"2015-11-18T19:03:29","date_gmt":"2015-11-18T18:03:29","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1967"},"modified":"2024-03-29T21:36:10","modified_gmt":"2024-03-29T20:36:10","slug":"distinct-or-unique-count-with-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/distinct-or-unique-count-with-excel-pivot-tables\/","title":{"rendered":"Distinct or Unique Count with Excel Pivot Tables"},"content":{"rendered":"

Excel 2013 added some new features to its arsenal and one that has been well overdue\u00a0was the distinct or<\/a> unique count.<\/p>\n

Previously when we created a Pivot Table<\/a> and dropped a Customers<\/em> field in the Row Labels and then again in the Values area we got the\u00a0“total number of transactions” <\/strong>for each customer.<\/p>\n

But what about if we want to show the total unique customers?<\/p>\n

In Excel 2013 we can, by using the newly created Pivot Table\u00a0Data Model<\/strong>:<\/p>\n

STEP 1:<\/strong><\/span> Click in your data source and go to Insert > Pivot Table<\/em><\/strong><\/p>\n

STEP 2:<\/strong><\/span> The important step here is to “check” <\/strong>the Add this to the Data Model<\/span> <\/strong>box and press OK<\/strong><\/p>\n

\"Distinct<\/a><\/p>\n

STEP 3:<\/strong><\/span> This will create a Pivot Table. \u00a0Now drop the Customers<\/em> field in the Row and Values areas<\/strong> which will give you the “total transactions” for each customer<\/p>\n

STEP 4:<\/strong><\/span> To get a Distinct Count, you need to click on the Values drop down<\/strong> for the Count of Customers<\/em> and select the Value Field Settings<\/strong><\/p>\n

STEP 5:<\/strong><\/span> Under Summarize Values By<\/strong> tab, select the last option, Distinct Count<\/strong> and press OK<\/strong><\/p>\n

\"Distinct<\/a><\/p>\n

See how this is done with the following animated tutorial plus you can download the workbook…<\/p>\n

\n