Count Distinct Values in an Excel 2013 PivotTable

Distinct Count of Item Summary

This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable.  Prior to Excel 2013, this capability was not built-in to the PivotTable feature.  For Excel versions earlier than 2013, there are a variety of different workarounds available, some use VBA code, some use helper formulas, and some of them use functions such as COUNTIF, COUNTIFS, and SUMPRODUCT.  The good news is that beginning with Excel 2013, this capability is built-in to the standard PivotTable feature. This post demonstrates how to perform a distinct count with an Excel 2013 PivotTable.

Objective

First, let’s review our objective.  We have some sales transactions with region, item, and amount columns, as shown below.

Data Range

Our objective is to create a PivotTable that counts the number of unique region/item combinations. For example, we want the PivotTable to show that for region N, the count of unique items is 2.

Traditional PivotTable Limitation

With Excel 2010 and earlier, the best we could do is to count the number of transactions (rows) with any given region/item combination. This is illustrated in the screenshot below.

PivotTable Count

The count illustrated above is not what we want.  We do not want to count the number of transactions/rows, we want to count the unique, or distinct, number of region/item combinations.  We want a PivotTable that reports 1 for E since there is one unique region/item combination for E (X101).  We expect 2 for N, since there are two items for N (X101, X102).  We expect 4 for S, since there are four items (X101, X102, X103, X104). We expect 3 for W (Y200, Y201, Y202).

Excel 2013 Distinct Count PivotTable

To obtain a distinct count in an Excel 2013 PivotTable, here is what we do.  We select any cell in the data source, as normal, and then click Insert > PivotTable, as normal.  However, we need to add this data source to the Data Model.  This is easily performed by checking the relevant check box in the Create PivotTable dialog box, as seen below.  Please note, this “Add this data to the Data Model” checkbox is a new option with Excel 2013.

Create PivotTable

 

Next, we build the PivotTable as we normally would by inserting the region and item fields into the Rows area.  Next, we insert the item field into the Values area.  Now, this is the good part.  We open the Values Field Settings dialog box for the item field we placed into the Values area, and look…a brand new option!  The Distinct Count choice in the Summarize value field by list box, as shown below, is new in Excel 2013.

PivotTable Distinct Count

 

Clicking OK results in the glorious PivotTable pictured below.

Distinct Count of Item PivotTable

 

As you can see, our desired counts are all here, E is 1, N is 2, S is 4 and W is 3.

If it is not important to see the item labels, we can remove the item field from the Rows area, or, hide the detail; either way is fine.  This will result in the PivotTable below.

Distinct Count of Item Summary

 

So, that is a great new PivotTable enhancement rolled out in Excel 2013.  Thanks Microsoft!

Excel file used in the above screenshots: PTDistinctCount

 

 

 

 

This article was written by Jeff Lenning

11 comments:

  1. John Popp
    Reply

    Maybe you could point me in the right direction. Your last pic shows your collapsed pivot table with a Grand Total of 7. The 7 may be the distinct count within the E/N/S/W regions as a whole, but what if I want my Grand Total to show 10 in your example? The distinct count is pretty slick, but I’d like to find a way to get my Grand Total to actually sum all of the subtotals. I haven’t found anything online addressing this particular concern.

  2. jefflenning Post author
    Reply

    John,

    Happy to help out. To clarify this for future readers, the Grand Total 7 in the final screenshot above represents the number of unique items in the population. And, this is misleading because if we visually add up the report, we expect that to reflect a total of 10, because 1+2+4+3=10. This total represents the number of unique Region/Item combinations (rows).

    So, the question is, how to update the PivotTable report to reflect the desired total of 10, rather than the default value of 7.

    Since this is Excel we are talking about, there are many ways to accomplish this. Here are just a couple of ideas.

    Running Total Column
    One way is to modify the PivotTable report to include an additional running total column. To add the running total column, simply insert the Item field into the Values area again, and set the field to display the Distinct count. Next, change the settings to Show Values As…and select Running Total In.
    The result is shown below:
    Running Total

    Remove Duplicates
    Another method is to use the Remove Duplicates feature. This takes only a few moments. Basically, copy the Region and Item columns from the source data, and paste them into a blank sheet. Then, select the range and select Data > Remove Duplicates. Confirm the dialog box, and click OK. This feature removes duplicate values, resulting in a list of unique Region/Item combinations. The resulting number of unique rows (10) is displayed in the dialog box. If you need to show the total in the report, a standard COUNTA function, or other counting function, can be used to compute the number of remaining rows.

    In addition to these options above, I’d love to have some comments below if you guys would like to suggest any other approaches.

  3. Michael
    Reply

    Thank you so much…. I have been looking for this for so long….

  4. Jason Lo
    Reply

    Dear Jeff,

    May I know how to do it through vba?

    Best regards,
    Jason

    1. jefflenning Post author
      Reply

      Jason,
      Thanks for your question about accomplishing this task with VBA. I’ll consider it for an upcoming blog post…thanks!
      Thanks
      Jeff

  5. thomas dejager
    Reply

    Jeff, I see your command to check the box for Add This Data To The Data Model. What does it mean when this box is grayed out, preventing me from even having the ability to check it? Thanks for any assistance!

    1. jefflenning Post author
      Reply

      Thomas,
      Generally speaking, when the PivotTable source data is stored in an Excel worksheet, the Data Model checkbox is enabled. There are some restrictions however, such as the version of Windows and if there are multiple tables. Here are a couple of links that may be able to help determine why the Add to Data Model checkbox is disabled in your workbook, I hope they can help out:

      http://blogs.office.com/2012/08/23/introduction-to-the-data-model-and-relationships-in-excel-2013/
      http://office.microsoft.com/en-us/excel-help/create-a-data-model-in-excel-HA102923361.aspx

      If you figure it out, I’d love a quick update comment in case others encounter a similar restriction…thanks!

      Thanks
      Jeff

    2. Scott H.
      Reply

      Thomas, I ran into this problem as well and resolved it promptly by making sure I was in a saved .xlsx file… initially I had been working in .csv and Excel 2013 doesn’t like letting you add the Data Model unless you’re actually working in the .xlsx file. Good luck, I hope this helps.

      1. jefflenning Post author
        Reply

        Scott – thank you for the comment!

        Thanks
        Jeff

  6. Jeff Ulicny
    Reply

    Hi Jeff,

    I found that the box was grayed out when I initially opened my file in CSV format using Excel 2013. After I saved it as an Excel Workbook (.xlsx) the “Add This Data…” checkbox was enabled. I also confirmed that the box is grayed out when the file extension is XLS (Excel 97-2003 Workbook) instead of XLSX.

    Thanks for posting this procedure; it came in quite handy for me recently!
    Jeff

    1. jefflenning Post author
      Reply

      Jeff,

      I appreciate you taking the time to post the information about the impact of the file format…thanks!

      Thanks
      Jeff

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

By submitting this form, you accept the Mollom privacy policy.