Excel like a finance pro.
Pivot tables · Calculations
Distinct count
Counts unique values in a field, such as how many customers ordered.
When to use it
Distinct Count counts unique values in a field, such as how many customers ordered, and is available only when the pivot uses the Data Model.
The shape of it
- How
Check "Add this data to the Data Model" when creating the pivot, then Value Field Settings, Distinct Count.
Worked examples
Unique count
Insert, PivotTable, check Add this data to the Data Model, then Value Field Settings, Distinct Count → Unique customers per region
The only built-in way.
Products per region
Distinct Count of Product → How many products each region sells
Breadth.
Why it is missing
Without the Data Model → Distinct Count is missing from the list
Check the box when creating the pivot.
Worth knowing
- Data Model pivots cannot use grouping or calculated fields; use measures instead.
- Distinct count of a text field ignores case differences.
- For older files, a helper column with COUNTIF(...)=1 approximates it.
Where it goes wrong
- Blank values count as one distinct value.
- The Data Model changes some behaviors (no calculated fields, different refresh).
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.