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!

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

  1. Lisa Bucher

    I have 2013 version and have tried to save my excel sheet to .xlsx like recommended from someone in order for the box to not be greyed out to check off “Add this data to the Data Model”. But that did not work for me. Any other ideas? Really could use the Distinct count to get positions in each union. some positions could be occupied by multiple people throughout a year, but only want to show it as 1 position.

    Thanks!
    Lisa

  2. Tom

    Hi,
    I want to add in a Calculated Field, but if I choose to “add this data to data model” when I set the Pivot table up the option is no longer there, as it would be within a standard pivot table.

    I only have Office Business and Home 2013 version (not Professional) – does anyone know how I might add a calculation in without having to upgrade or start using Power Pivots? I cannot seem to add the Power Pivot tab to my ribbon either.

    Thanks!

  3. Mark

    When I double click the distinct count number it shows results for a normal count. How can I get this to show just the distinct count which shows in the pivot?

  4. Dipesh Patel

    I would check the counts manually before using them in analysis. I’m an accountant and find that sometimes this function gives the wrong number, for whatever reason.

Leave a Reply

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