In Excel: create the PivotTable with "Add this data to the Data Model" ticked, drag the field into Values, then Value Field Settings ▸ Summarize value field by ▸ Distinct Count.
On this page7
Click inside the data and choose Insert ▸ PivotTable.
Tick Add this data to the Data Model and click OK.
Drag the field whose unique values you want to count into Values.
Click the field in Values ▸ Value Field Settings ▸ Distinct Count ▸ OK.
Without the Data Model: add a helper column =IF(COUNTIF($B$2:B2,B2)=1,1,0) and use Sum of that column.
What this does
A normal PivotTable can Count rows but not count each different value once. Distinct Count becomes available when the PivotTable is built on the Data Model, which you choose with a checkbox in the Create PivotTable dialog. The setting is fixed when the PivotTable is created, so an existing PivotTable without it has to be rebuilt. The Data Model option is not available in every Excel edition; where it is missing, a helper column that marks the first occurrence of each value gives the same result with an ordinary Sum. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “excel pivot table count distinct”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected pivot table change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
An orders table has 10 rows for 4 customers. Insert ▸ PivotTable, tick Add this data to the Data Model, put Region in Rows and Customer in Values. Count of Customer shows 10; open Value Field Settings, choose Distinct Count, and it shows 4. Without the Data Model, add a column with =IF(COUNTIF($B$2:B2,B2)=1,1,0) and sum that column in the PivotTable. How many different customers, products or users per region is a different question from how many rows — and it is the one most reports actually need. If this is something you do often, add the command to the Quick Access Toolbar (right-click it on the ribbon and choose Add to Quick Access Toolbar) or learn its shortcut, so next time it is one click instead of a hunt through the menus.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Treat “excel pivot table count distinct” as a routine rather than a one-off. Once you know which setting or command controls it, the same few steps handle every workbook where it comes up in Excel, and you can explain them to a colleague in a sentence.
Common mistakes
- Looking for Distinct Count on a PivotTable that was not created with the Data Model — rebuild it with the box ticked.
- Using Count, which counts rows, not different values.
- Helper-column counts that break when the PivotTable is split by a second field, because the first occurrence is counted only once overall.
- Trailing spaces or different spellings making the same customer count twice.
Frequently asked questions
Why is Distinct Count missing from Value Field Settings?
The PivotTable was not added to the Data Model. Create a new PivotTable and tick Add this data to the Data Model.
Can I count unique values with a formula instead?
Yes — in Excel with dynamic arrays, =COUNTA(UNIQUE(B2:B11)) counts the different values in a range.
Can I change an existing PivotTable to Distinct Count?
Only if it was created on the Data Model. Otherwise insert a new PivotTable from the same data with the Data Model box ticked.
Other ways people ask this
On the way here you may have searched this as “distinct count in excel pivot table”, “excel distinct count in pivot table”, “excel pivot table distinct count” and “count distinct excel pivot table” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “distinct count in excel pivot table”, “excel distinct count in pivot table”, “excel pivot table distinct count” all point at the one operation explained on this page, which is why they all lead here.