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!
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.
YES! Finally … that's so awesome. Definitely another reason to upgrade to Excel 2013. I'm really digging the new timeline slicer too.
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.
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.
@ Jared and Will: Chris Webb has a good post on this at http://cwebbbi.wordpress.com/2013/02/01/office-2013-office-365-editions-and-bi-features/
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?
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?
@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.
Excel (Power Pivot) 2010 has this function as well.
Hello there I am so delighted I found your website, I really found you by accident,
while I was researching on Aol for something else, Anyways I
am here now and would just like to say kudos for a fantastic post and
a all round enjoyable blog (I also love
the theme/design), I don’t have time to read through it all at the minute but I have saved it and also added your RSS feeds, so when I have
time I will be back to read much more, Please do keep up the
great b.