Layout and values Updated November 2026

Distinct count

When creating the pivot tick Add this data to the Data Model, then drag the field to Values, open Value Field Settings and choose Distinct Count at the end of the list. In Google Sheets pick COUNTUNIQUE in Summarize by. A normal Count counts every row, including repeats.

Syntax

Insert > PivotTable > tick Add this data to the Data Model > Value Field Settings > Distinct Count

Part	What it is
`Add this data to the Data Model`	Checkbox in the Create PivotTable dialog; required for Distinct Count in Excel
`Distinct Count`	Counts each different value once per row of the pivot
`COUNTUNIQUE`	Google Sheets equivalent in the Summarize by dropdown

Examples

Rows: Region. Values: Distinct Count of Customer

Result: The number of different customers per region. A customer with five orders counts once.

Rows: Region. Values: Count of Customer, Distinct Count of Customer

Result: Orders per region next to unique customers per region, so you can see repeat business at a glance.

Google Sheets: Rows: Channel. Values: Customer, Summarize by COUNTUNIQUE

Result: Unique customers per channel.

Common mistakes

Distinct Count is not in the list: the pivot is not on the Data Model, so recreate it with the checkbox ticked.
The Grand Total is smaller than the sum of the rows: that is correct, a customer who bought in two regions is counted once overall.
Grouping dates is slower or missing on a Data Model pivot: date grouping works, but calculated fields and items do not; use measures instead.

Practice Distinct count

These drills check your answer on live data, free, no account needed.

Returning customer valueCompare New and Returning customers: number of orders, total Sales and average Sales per order. Customer in rows.
How many orders per productShow how many orders there are for each Product.

Questions

How do I count unique values in a pivot table without the Data Model?

Add a helper column in the source such as =1/COUNTIFS(Customer:Customer, A2, Region:Region, B2) and sum it in the pivot. The sum equals the distinct count per region.

Does distinct count work in Excel for Mac?

Yes in Microsoft 365 for Mac with the Data Model; older Mac versions lack it, so use the helper column method.

Data Model pivot tableWhat Add this data to the Data Model does: relate several tables without lookups, get Distinct Count and write DAX measures. Also what you lose by using it.
Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.
Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.