Simple Pivot Table to Count Unique Values

by

UPDATE: You can do this now automatically with Excel 2013. I’ve created this as a new answer because my previous answer actually solves a slightly different problem.

If you have that version, 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 you’re looking for.

More Related Contents:

  1. Count Unique values with a condition
  2. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  3. Excel Number Separation [closed]
  4. Calculation is wrong, Rounding wrong
  5. Excel formula checklist in employees
  6. Sequence of numbers based on start and end value of 2 cells [closed]
  7. Excel VBA – How to add dynamic array formula
  8. Return empty cell from formula in Excel
  9. TEXTJOIN for xl2010/xl2013 with criteria
  10. Vlookup using 2 columns to reference another
  11. Difference between date and time w/out work week Excel
  12. How can I perform a reverse string search in Excel without using VBA?
  13. Ignore Duplicates and Create New List of Unique Values in Excel
  14. Excel Formula for Inventory
  15. Using UNIQUE with non-adjecent columns on different sheets
  16. Count unique values in Excel
  17. Concatenate column headers if value in rows below is non-blank
  18. If two cells match, return value from third
  19. What does — do in Excel formulas?
  20. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  21. Using the value in a cell as a cell reference in a formula?
  22. How to list all possible combinations of the values in three columns in excel?
  23. Excel – extracting top 5 values
  24. CountIf With Filtered Data
  25. How to split texts from dynamic range?
  26. Excel: Search for a list of strings within a particular string using array formulas?
  27. How to count cells in a range with a value less than another cell in excel?
  28. Extract the last substring from a cell
  29. How to extract text within a string of text
  30. Excel: How to gather unique values in one column that are associated with duplicates in another column?

Leave a Comment