Excel - Distinct Count in an Excel Pivot Table - Episode 1924 Video
Microsoft Excel Tutorial: How to do a Distinct Count or a Unique Count in an Excel Pivot Table.
Welcome to the MrExcel podcast. In this episode, we will be discussing a highly requested topic - Distinct Count in Excel Pivot Tables. This has always been a difficult task in previous versions of Excel, but with the release of Excel 2013, it has become much easier. So let's dive in and learn how to do a Distinct Count in Excel 2013 Pivot Tables.
First, I want to mention my Excel class, "Excel formulas - The Dirty Dozen" available at tinyurl.com/edupow5. It's a great resource for learning all the essential Excel formulas. Now, let's get back to our topic. In Excel 2013, we can easily do a Distinct Count by using the Data Model feature. To do this, simply go to INSERT, PivotTable, and check the box that says "Add this data to the DataModel". This will allow us to join two tables and make our Distinct Count problem much simpler.
Next, we will take the Sector field and drag it to ROWS, and then take the Customer field and drag it to VALUES. This will give us a count, but not the count of unique or distinct customers. To get the distinct count, we need to go to Field Settings, choose the Customer field, and under "Summarize Values By", select "Distinct Count". And just like that, we have our desired result - a distinct count of customers. This feature is only available in Excel 2013 and it has made our lives so much easier.
Now, for those of you who are still using older versions of Excel, don't worry, I've got you covered. In Excel 2010 or earlier, you can achieve a Distinct Count by using a formula. Simply use the formula =1/COUNTIF($D$2:$D$546,D2) and press F4 to lock the cell references. This will give you the distinct count for each customer. However, this method is not as flexible as the one in Excel 2013 and it can be time-consuming. So, I highly recommend upgrading to Excel 2013 or Office 365 Home Premium for just $9/month.
I hope this tutorial has been helpful in understanding how to do a Distinct Count in Excel Pivot Tables. Don't forget to like and subscribe to our channel for more Excel tips and tricks. And if you have any questions or suggestions for future episodes, please leave them in the comments below. Thank you for watching and we'll see you next time for another MrExcel netcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents:
(00:00) Distinct Count in Excel Pivot Table
(00:22) Difficulty of Distinct Count in Pivot Tables
(00:40) Solution in Excel using the Data Model
(00:56) Sector and Customer Placement
(01:08) Difference Between Total Records and Distinct Customers
(01:19) Field Settings and Summarize Values By
(01:47) Encouragement to Upgrade to Excel 2013
(02:00) Conclusion and Thank You
(02:10) Alternative Solution for Excel 2010 and Earlier
(02:25) Explanation of Alternative Solution
(02:38) Pivot Table Solution for Alternative Solution
(02:52) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
#pivottable #pivot_table #excelpivot #excelpivottablestutorial #powerpivot #DAX
This video answers these common search terms:
how to count distinct values in excel pivot
how to count unique in excel pivot
how to count unique values in excel pivot
how to do count distinct in excel pivot table
how to do distinct count in excel pivot table
how to calculate unique values in excel pivot table
how to distinct count in excel pivot
what is distinct count in excel pivot tables
how to get unique count in excel pivot table
If you still have Excel 97-2010, watch the outtake for the ugly method prior to Excel 2013.
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153750/
About the Site 🌐
This site provides links to random videos hosted at YouTube, with the emphasis on random. 🎥
Origins of the Idea 🌱
The original idea for this site stemmed from the need to benchmark the popularity of a video against the general population of YouTube videos. 🧠
Challenges Faced 🤔
Obtaining a large sample of videos was crucial for accurate ranking, but YouTube lacks a direct method to gather random video IDs.
Even searching for random strings on YouTube doesn't yield truly random results, complicating the process further. 🔍
Creating Truly Random Links 🛠️
The YouTube API offers additional functions enabling the discovery of more random videos. Through inventive techniques and a touch of space-time manipulation, we've achieved a process yielding nearly 100% random links to YouTube videos.
About YouTube 📺
YouTube, an American video-sharing website based in San Bruno, California, offers a diverse range of user-generated and corporate media content. 🌟
Content and Users 🎵
Users can upload, view, rate, share, and comment on videos, with content spanning video clips, music videos, live streams, and more.
While most content is uploaded by individuals, media corporations like CBS and the BBC also contribute. Unregistered users can watch videos, while registered users enjoy additional privileges such as uploading unlimited videos and adding comments.
Monetization and Impact 🤑
YouTube and creators earn revenue through Google AdSense, with most videos free to view. Premium channels and subscription services like YouTube Music and YouTube Premium offer ad-free streaming.
As of February 2017, over 400 hours of content were uploaded to YouTube every minute, with the site ranking as the second-most popular globally. By May 2019, this figure exceeded 500 hours per minute. 📈
List of ours generators⚡
Random YouTube Videos Generator
Random Film and Animation Video Generator
Random Autos and Vehicles Video Generator
Random Pets and Animals Video Generator
Random Travel and Events Video Generator
Random People and Blogs Video Generator
Random Entertainment Video Generator
Random News and Politics Video Generator
Random Howto and Style Video Generator
Random Education Video Generator