Distinct Count in Pivot Tables – Finally in Excel 2013

For as long as I can remember, Excel analysts have pleaded for a way to calculate Distinct Count in PivotTables. What do I mean by Distinct Count?

.

Well, when you count something like Customers in a pivot table, Excel is really giving you the count of records. In this pivot table for example, we were trying to get how many customers each Region has. Instead, you see how many records are attributed to each region. What we really want is a Distinct Count of Customers.

.

Year after year, we’ve been disappointed. Until now!

With Excel 2013, we now have a built-in way to calculate Distinct Count without fuzzy workarounds.

You’ll have to have Excel 2013 to get this new feature.

If you have an earlier version of Excel, you can employ one of the nifty Unique Count tricks Debra shows.

.

If you DO have Excel 2013 available, follow these steps.

 

Step 1:

Start a pivot table with any table or range of data.

On the Create PivotTable dialog box, be sure to click Add this data to the Data Model. This is where the magic is. Because you are loading the data into the internal Data Model, you’ll expose some features not normally available to you.

NOTE: See my previous post to learn about the Excel 2013 Internal Data Model.

.

.

Step 2:

Build your pivot table as you normally would.

Because we want to get a distinct count of customers, the customer field into the Values area.

Excel will automatically apply the count. But again, it’s only the count of records.

So right-click on any value for Count of Customers and select Value Field Settings.

.

.

Step 3:

In the Value Field Settings dialog box, choose Distinct Count as the operation to summarize by.

.

Once you confirm the change, your pivot table shows a Distinct Count!

.

.

Here is the same pivot with the original Count and the Distinct Count.

.

.

As far as I’m concerned, the Internal Data Model is the best invention since Salted Chocolate.

We should all be convincing our organizations to upgrade to Excel 2013 now!

52 thoughts on “Distinct Count in Pivot Tables – Finally in Excel 2013

  1. Arlyn

    Has awesome as that would be to have the most updated version of Office, much less Excel, we are eagerly anticipating an upgrade to Office 2007 here.

    To give some perspective. I started in my current job in Jan of 2011 where the company had just upgrade to XP! And to Office 2003! We just upgrade to IE8 last year, and people freaked because of tabbed browsing.

    So much frustration here.

  2. Matt

    YES! Finally … that’s so awesome. Definitely another reason to upgrade to Excel 2013. I’m really digging the new timeline slicer too.

  3. Will

    First of all, that’s awesome and sounds like a really useful feature.

    Second, can you do a post about differences between Excel 2013 included in Office 365 Home Premium and Excel 2013 included in Office Professional Plus? Much to my dismay when I signed up for 365 Home Premium the other day I learned the Power Pivot is not available in that version (which is annoying, I was looking forward to learning that), and I’m curious to see if there are any other differences.

    Thanks. I recently discovered your site and really enjoy it.

  4. Jared

    Will….I would find that helpful as well. I suffered a period of depression when I found out that my shiny new excel 2013 didn’t come with Power Pivot.

  5. Will

    My condolences Jared, I was hoping they’d at least offer it as an add-in you can download. Jeff, thank you for linking that article, it was very helpful.

    I’m curious about this now; has Microsoft previously released different versions of Office programs based on what package you buy? I haven’t followed Office releases very closely until now, but I was always under the impression that while the package determined which programs you got, the programs were the same (for example, that Excel 2007 from the Home and Student pack was the same program as Excel 2007 from the Professional package). Is this a new thing or have I just never noticed it before?

  6. Brian C

    This is a great feature in Excel 2013. However, when I create the pivot and check “Add this to dataset model”, certain functions such as “grouping” are now disabled in the pivot table. Am I doing something wrong?

  7. Rory Archibald

    @Brian C,

    No, you’re not. You get an OLAP pivot using this method which has a few drawbacks – grouping and date filtering being two obvious ones.

  8. Kevin Dobo

    Well, I’ve just spent an entire evening upgrading to Office 2013. The ONLY reason I did it was to upgrade Excel, and the ONLY reason I did that was to get at Distinct Count.

    After all that, I discover that I can’t Group or filter dates, which means Distinct Count is of absolutely ZERO use for me. Now I’m going to have waste a bunch more time and effort trying to uninstall 2013 and get 2010 working again.

    Very disappointed to say the least.

  9. Marie Kopinski1

    This was awesome and helped me help solve a problem a co-worker was having trying to get a count of invoice numbers in a query–it kept giving a count for each line item and he just wanted to count the invoice#. We just happen to have Excel 2013 and this solved the problem! It was also explained very clearly so that I could easily apply it to our needs. THANKS!!!

  10. VIdar

    I’m having the same trouble as some of you.

    When I check ,,Add this data to the Data Model” I can’t group, it’s grayed out!

    Please help!

  11. Mon

    Unfortunately with this new improvement a function has been lost, unable to create a calculated field now – all that excitement for nothing!!!

  12. Pete Smith

    having a hard time seeing the real value of this feature only in DataModel ‘mode’ which removes core pivot features like GROUPING. Almost every scenario in which I want to use distinct count, I want to do so for Grouped data.

    am I an anomaly?

  13. beverly godwin

    When I refresh the pivot to include newly added rows (change data source), it wipes out my pivot and I have to rebuild it. Am I doing something wrong? Thanks to everyone for the helpful info!!

  14. dylan

    To group dates, you need to create a hierarchy in PowerPivot in your date table. Remember, this is OLAP. In Excel 2007 forward they gave you basic hierarcies in the form of date grouping, but here you need to do this yourself. create a date table that contains at least all the dates inside the dataset, bring it into the data model, create a calculated column for each summary =month[date] =year[date] etc and then create a hierachy. then refresh your pivot table and add your hierachy not the underlying dates. It will automatically understand that months are inside years and so on. This will also allow custom groups to be created… you can create week numbers that can live under months, all based on calculated columns set against the underylying date table.

    This can also be done with your other data. You could for instance create a team hierachy so the pivot table automatically understands that your salespeople are under their respective teams, who are under their respective stores, who are under their respective divisions, reigons etc…

    This is not a limitation, it opens a whole new set of possibilities.

  15. Nigel

    Thanks for this. Was swearing at my machine but then realised I was using a spreadsheet sent by my client. Imported data into new 2013 file and it works great

  16. John

    Nigel gave me the hint to solve the “add this data to the Data Model” greyed out issue.
    My file was a CSV save as .xlsx and suddenly the check box is available.
    Have to say this is very counter intuitive and inconsistent behaviour but probably what I should expect from MS.

  17. Shravan Kumar

    When I refresh the pivot to include newly added rows (change data source), it wipes out my pivot and I have to rebuild it. Am I doing something wrong? Thanks to everyone for the helpful info!!

  18. Martin

    I have the same problem that all my settings for the pivot are reset when I change the data… Really annoying.
    Hope someone can help with this.

  19. Wendy J

    I was really looking forward to my organisation upgrading to Excel 2013 so I could do a distinct count on a pivot table I created some time back, but am gutted to find out about not being able to group dates. I don’t really follow the instructions given above for how to do this, and suspect that it only applies to Power Pivot (which I’d never heard of before finding this post). My organisation is a not for profit (in fact I am a once-a-week volunteer) so has no money to spend on any extras. Can someone explain simply how to do date grouping in my pivot table if I add it to the data model?

    Also, can the data model addition be retrofitted to an existing pivot table, or can it only be checked at the time of creating a new one?

  20. SeanMc

    EVENTUALLY! thank you so much! So happy it is all within excel and that i dont have to go into PowerPivot! Happy Happy Happy!

  21. Mohamed

    if tick on add this data to the data model, the fields, items and sets in analyze pivot table tool will disappear so you cannot add any formula on your pivot table and once you untick it you can add formula, so any idea how to do it, keeping the data model as i need the count distinct

  22. Greg

    I may be missing something here but when I add up the values for the distinct column in the example I get a total of 7457 but the grand total in the pivot table is 5999. How is that possible? Which set of numbers if correct? the individual rows or the grand total?

  23. LaurieS

    unfortunately, this doesn’t seem to work if you imported data from Access to use as the table data source. once you’ve pulled the data into Microsoft query in excel, and then click pivot table report on the import data dialog box (ex: select how you want to view the data in your workbook), the box to check for” add this data to the data model” is disabled. what a major let-down.

  24. simb

    The distinct count could be great, but it counts blanks as a ‘value’.
    What logic allowed that to happen ?

    If i have 3 baskets, 1 with an apple in it, another with a banana, and the last basket is empty – then how many kinds of fruit do I have ?

    Microsoft is telling me the answer is 3.

    Unbelievable.

  25. SpillerBD

    Actually you can build a Pivot Table from a Pivot Table result. A little unconventional but avoids OLAP limitations when going to Data Model and allows for pre-2013 to get the desired results.

  26. SUDHAKAR

    Hi, How can i get distinct count for the existing pivot tables which i have created without checking the ‘Add data to the data model” option?

    Using office 2013

    Please send me a mail

    Thanks a lot in advance

    Regards
    Sudhacar

  27. Monika

    Hi, when I update the “distinct count” pivot table the columns are gone if there is no data on it. I need to have exact the same number of columns even if there is no data filled in.

    Any advice how can I do this?
    Thanks.
    Monika

  28. Pingback: Brushing up on data manipulation and analysis skills | Libwebrarian's Blog

  29. Kitesurf School

    Kitesurfing is usually a mineral water video game that is
    not handiest realized without difficulty but
    also very speedy, and in many cases additional fun inside kitesurfing paradise poker.

    There isn’t something much better than the particular incredible experiencing you have while you go in the turquoise h2o, easiest shoved by means
    of your blowing wind, the sun and also sodium h2o coming in contact with ones skin tone as well as a awesome Caribbean air flow on your experience.

    There isn’t often better spot for a discover, exercise and also do Kiteaboarding.

  30. Lynn

    This is FANTASTIC, thank you so much for posting and thanks to Google for helping me find the post – it has saved me lots of unnecessary and time consuming work!!

  31. Ritika

    You have no idea how much of my time you just saved there!!! Thank you and bless you!! 😀

  32. Pingback: Apache POI Pivot Table: Distinct count (Excel 2013) - Tech

  33. Domski Hill

    At last they upgraded me to 2013 at work and I can now do my headcount analysis without all the usual hassle it involves. I’m so happy!!! 🙂

  34. Samuel Molina

    I did it, but when i can’t refresh the pivot table using macro. The macro runs well, but do not refresh the pivot table. I must do manually

Leave a Reply

Your email address will not be published. Required fields are marked *