Excel - How to Do a Distinct Count in an Excel Pivot Table - Episode 2015
Microsoft Excel Tutorial: How to Use Distinct Count in Pivot Table in Microsoft Excel.
Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing how to use the Distinct Count function in a Pivot Table. This is a great tool for creating reports that show the number of unique customers in each sector.
If you're new to the podcast, be sure to check out our playlist for all of our previous episodes. And if you're looking for more Excel tips and tricks, be sure to grab a copy of our book, available in both print and e-book formats.
So let's get started. When we try to use a regular Pivot Table to count the number of customers in each sector, we run into a problem. The count it gives us is actually the number of records, not the number of customers. But fear not, there is a simple solution.
In Excel 2013 and newer versions, we can use the Data Model to join two tables together. Simply select the option to "Add this data to the Data Model" and build your Pivot Table as usual. Then, in the Field Settings, you will see a new option called Distinct Count. This will give you the accurate count of unique customers in each sector.
For those of you using older versions of Excel, don't worry, we have a solution for you too. You can use the COUNTIF function to count the number of times a customer appears in the original data, and then divide that by the total number of records to get the distinct count. But let's be honest, the Data Model makes it so much easier.
Not only does the Data Model make it easier to do a distinct count, but it also has the added benefit of being able to join tables. So if you haven't upgraded to Excel 2013 or newer, I highly recommend it. And for those of you who have, take advantage of this powerful tool to make your Pivot Tables even more accurate and efficient.
Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to hit the "i" on the top-right hand corner to purchase our book for even more Excel tips and tricks. And be sure to subscribe to our channel for more helpful tutorials. See you next time!
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-...
Table of Contents:
(00:00) Pivot Table Distinct Count!
(00:13) Creating a report with customer count per sector.
(00:24) Insert PivotTable and use Data Model to join tables.
(01:14) New option for Distinct Count in Field Settings.
(01:30) Old method for Distinct Count in Excel
(02:00) Benefits of using Data Model for Distinct Count
(02:39) Data Model also useful for joining tables.
(02:49) Regular Pivot table cannot count customers per sector.
(02:59) 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 #excelformula #exceltooltip
This video answers these common search terms:
how to do distinct count in pivot table excel
how to distinct count in excel pivot
how to do distinct count in excel pivot table
how to add distinct count to excel pivot
can excel do a count distinct in a pivot table
how to count distinct values in excel pivot
how to distinct count in excel
how to count distinct cells in excel
how to count distinct values in excel column
how to do a count distinct in excel
what is the formula for counting distinct values in excel?
Creating a distinct count or unique count in a pivot table used to be hard. This episode shows the new easy way and the old way. Recap:
Introduced the Data Model in Podcast 2014 for Joining Tables
Another Benefit is the ability to do Distinct Count
Regular pivot table can not count customers per sector
Add the data to the Data Model and you have Distinct Count available
Before Excel 2013, you would have to add 1 / COUNTIF to the original data
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...