How to Count Unique Values (Distinct Count) in a Pivot Table in Excel Online and Excel for Mac

A unique count (also called a distinct count) represents the number of unique values in a column. In the following list, a "standard" count returns 4 and a distinct/unique count returns 2.

123
456
123
123

In Excel, Pivot Tables can display both a standard count and a distinct count.

If you store the Pivot Table source data in the Data Model instead of the worksheet grid, creating a distinct count is simple. However, neither the Mac version of Excel nor the online version supports creating Pivot Tables from Data Model-based data.

In this video I show you how to create a distinct count in a Pivot Table created in Excel Online or Excel for Mac

For full details see the timestamps below:

⏱ Timestamps
00:00 Introduction
00:00:22 What is a Distinct / Unique Count?
00:01:26 The Formula to Solve the Problem
00:02:43 Create the Pivot Table

🗒 Notes and Resources

✅ Want more FREE training?

🙋‍♂️ Let's connect on social

#excel #distinctcount #uniquecount

[MikeThomas]

Thanks to Heather who mailed me with this question. Heather isn't a Mac user but makes heavy use of Excel Online. What video would YOU like me to create? Let me know in the comments

[viet-bachcao]

Thank you so much! Such a great solution

[bzeqfqs]

Thanks Mike. It is very helpful video.

[parahitafaqih]

Thanks a lot, Mike! a great veideo and so helping my work.

[rodrigoconstantino]

Thanks for the hint, Mike! I was struggling during days to have this! 👍

[Adaddy]

Hello Mike,

Thank you for the amazing video.

If we used logic and looked at data over time for example months or quarters- this logic would not work for each distinct month/quarter.

Would love to know if there is a work around for distinct counts within a time period

[KevinA]

Thanks Mike a great video and very helpful. Stay cool as the heat is going to be brutal for y'all I hear.

[Schlesinger]

Hi. Thank you for the video. However, I wanted to point out this only works if for example the customer, only appears at the same city. But, if for example the same customer bought at a different store location, the numbers added will end up with decimals. In your example, if the Saphire Saloon would be a customer one time in Atlanta then the sum function would add to 2, 1666. So it really only works if the two columns are directly corelated, meaning for example that the saphire saloon can only be in new york, But if you have data that for the repeated item it can relate to different items on the other columns, then its not going to work.

[oleksiimartyshchenko]

Thank you for help.
I wouldn't come to it myself

[joshwand]

This also doesn't work when filtering the data in the pivottable, e.g. with a slicer.

[arindambhattacharya]

This is manually done, nothing done in pivot