Home » Business and Tech » Distinct Count in Pivot Tables in Excel 2013

In a slightly departure from my normal posting habits, I wanted to share a quick hack to solve something that has been bothering me in Excel for ages. Finally, with Excel 2013, I’ve discovered there is a way to determine the distinct count of unique items in a list using pivot tables, something that wasn’t previously possible without a fair bit of work. Numerous Google searches on the topic didn’t reveal much (aside from some older forums and a number of Stack Overflow threads) so I thought I’d share here in case anyone else has had similar frustrations.

If you have Excel 2013, then select your data to create a pivot table, and when you create your table, make sure the option ‘Add this data to the Data Model’ tickbox is check (see below).

Tick the box next to ‘Add this data to the Data Model

Then, when your pivot table opens, create your rows, columns and values normally. Then click the field you want to calculate the distinct count of and edit the Field Value Settings:

Edit field value settings

Finally, scroll down to the very last option and choose ‘Distinct Count.’

Choose the option ‘Distinct Count

This should update your pivot table values to show the data as a distinct count of unique values. Job done!

14 thoughts on “Distinct Count in Pivot Tables in Excel 2013

  1. Found a helpful eBook for learning Thai. “Romancing the Thai”. I utilized this book in the nana plaza to pick up the Thai girls – they really like it, a lot of love & romance phrases it’s a breath of fresh air!English thai bar guideThanks a lot!

  2. Hi there, its nice post regarding media print, we all be aware
    of media is a impressive source of facts.
    http://www.Facebook.com recently posted…http://www.Facebook.comMy Profile

  3. My partner and I absolutely love your blog
    and find most of your post’s to be what precisely I’m looking for.

    Does one offer guest writers to write content
    to suit your needs? I wouldn’t mind composing a post or elaborating on a few of the subjects
    you write related to here. Again, awesome blog!
    apartments in vivienne westwood jcpenney recently posted…apartments in vivienne westwood jcpenneyMy Profile

  4. I enjoy the dear facts a person deliver on your articles or blog posts.. proxy, proxy search, search engine I most certainly will save your current weblog and also take a look at all over again here regularly. My business is quite certain I am educated loads of brand new material correct here! Enjoy for the next!

  5. Use the manual to be taught and improve your skills. We’re not restricted simply
    on homeowners manuals!
    http://www.autoownermanual.com recently posted…http://www.autoownermanual.comMy Profile

  6. eduvaza.com says:

    Blackberry OS takes benefit the Blackberry’s specialised input
    devices, particularly the trackball and scroll-wheel.
    eduvaza.com recently posted…eduvaza.comMy Profile

  7. If you have no idea where to begin, don’t try
    it on your own. However if like many computer users, you
    are uncomfortable with DIY, then remote is not for you.
    What about the cost of hiring a computer repair Orange County California company.
    computer service miami recently posted…computer service miamiMy Profile

  8. Making them feel at ease will also gain their trust.
    grado de conciencia tiene el paciente en ese momento.
    I will endeavour to present everything in a very step by step
    method for sure, to enable you to understand without difficulty.
    hipnosis ericksoniana recently posted…hipnosis ericksonianaMy Profile

  9. Android Games which aare more interactive can sell well in the Android Game market.
    If you are an admirer of online bingo games by mind and desire to feel something extra from
    these types of games, then you have knocked the right door.
    Thhe games developed by the third party developers are submitted to
    Apple app store.
    marvel contest of champions gameplay king recently posted…marvel contest of champions gameplay kingMy Profile

  10. If you have repeat outbreaks, taking medication on a regular basis can help reduce the observable
    abjectnurse6107.mybizziblog.com recently posted…abjectnurse6107.mybizziblog.comMy Profile

  11. Goji Extreme says:

    Appreciating the persistence you put into your site and
    in depth information you present. It’s great to come across a
    blog every once in a while that isn’t the same unwanted rehashed material.
    Fantastic read! I’ve saved your site and I’m including
    your RSS feeds to my Google account.
    Goji Extreme recently posted…Goji ExtremeMy Profile

  12. Vernita says:

    Tout le monde aime ce que vous les gars sont généralement ont tendance à être trop.
    Ce type de couverture! Gardez les superbes merveilleuse travaille
    les gars, je suis Incorporated vous les gars à
    propre blogroll.
    Vernita recently posted…VernitaMy Profile

  13. Leslie says:

    I think more writers ought to write with enthusiasm like you.
    Even informative articles like this can have personality.

    That’s what you have put in this educational article.
    Your opinions are very one of a kind.
    Leslie recently posted…LeslieMy Profile

  14. Also the procedure of installing movie games is
    really simple, simply Google for no price PC movie games obtain. Most CPU
    come with their user guide on how to configure the BIOS settings.

    With the proper level of respect for the original content, the desire to innovate, and
    a true love for the franchise, any of these games could still
    potentially make an explosive–or at least successful–comeback as a smaller indie game or (perhaps) even a
    full-scale AAA title.
    http://hotel-restaurant-laloire.fr/index.php/component/k2/itemlist/user/2778 recently posted…http://hotel-restaurant-laloire.fr/index.php/component/k2/itemlist/user/2778My Profile

Comments are closed.