Excel Distinct Count with a PivotTable

Posted on:  03/31/2022
Excel Distinct Count with a PivotTable

PivotTables in Excel can create a Distinct Count. An example of a distinct count is we had five orders in one day, but three orders were placed by the same customer. I want to know we have five orders from three customers.

PivotTable with a Distinct Count

PivotTable with a Distinct Count

To create a distinct count in a PivotTable, there is a box you must check to have this feature enabled in your PivotTable. In this short video, I'll demonstrate a PivotTable without a distinct count, create another PivotTable with a distinct count, and finally show the awesome UNIQUE function. The tip is to select Add this data to the data model when creating your PivotTable.

YouTube video

Chapters/Bookmarks

  • 0:00 Intro
  • 0:15 PivotTable without distinct count
  • 2:10 PivotTable with distinct count
  • 4:15 UNIQUE Function in Excel
Other Excel articles

Create a PivotTable with a Distinct Count

 

Add this data to the Data Model

Add this data to the Data Model

  1. Click inside your data
  2. Click the Insert Tab and click PivotTable
  3. Make sure to check Add this data to the Data Model (see screenshot above)
  4. Click OK
  5. Drag your fields to the PivotTable field area
  6. To do a distinct count, right-click and click Summarize Values By and click More Options
  7. Scroll to the bottom and select Distinct Count

 

Summarize Values By - More Options

Summarize Values By - More Options

Distinct Count - Value Field Settings

Distinct Count - Value Field Settings

Recent Excel articles

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Recommend PivotTables - Improved Experience

Excel Recommend PivotTables - Improved Experience

Excel PivotTable - Calculate the number of days of each week for every month of the year

Excel PivotTable - Calculate the number of days of each week for every month of the year

Categories