Distinct Count in a Pivot Table: Unique Values

Part of the free Module 10: Pivot Tables · Lesson 16 of 20 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016. Distinct Count needs the Data Model, which is built into Excel 2013 and later.

A distinct count in a pivot table tells you how many unique items appear in a field, for example how many different customers placed an order or how many different employees worked under each supervisor. The normal Count function counts rows, so a customer with ten orders is counted ten times. To count unique values in a pivot table you add the source data to the Data Model and choose Distinct Count in Value Field Settings.

Why Count gives the wrong answer

Every row in a sales table is one transaction, not one customer. When you drop the Customer field into the Values area, Excel summarises it as Count, which simply counts how many rows contain a value. Ten orders from the same customer produce a count of 10, even though only one customer is involved.

The same problem appears whenever a name repeats: employees on a daily attendance log, products on an invoice list, SKUs in a stock movement file, or tickets raised by the same caller. In every case the question “how many different ones?” needs a distinct count, not a row count.

A standard pivot table, one built from a normal range or table without the Data Model, cannot answer that question. Open Value Field Settings and the Summarize Values By list stops at Var and Varp. The Distinct Count option only exists when the pivot table runs on the Data Model, because the calculation is done by the in-memory engine (the same engine that powers Power Pivot) rather than the classic pivot cache.

Count, Count Numbers and Distinct Count compared

The three counting options look similar in the Value Field Settings list but behave very differently. This table uses the eight-row sample from the worked example below.

Summarise by	What it counts	Employee column (text)	Revenue column (numbers)	Needs Data Model?
Count	Every non-blank cell (text, numbers, dates)	8	8	No
Count Numbers	Only cells that contain numbers	0	8	No
Distinct Count	Each different value once, no matter how many times it repeats	5	7	Yes

Notice that Distinct Count works on numbers as well as text. Two rows with the same revenue of 900 count as one distinct value, which is rarely what you want for an amount column but is exactly right for IDs, names and codes.

How to get Distinct Count in a pivot table

You must tick the Data Model option while creating the pivot table. An existing pivot table cannot be switched to the Data Model afterwards, so if your report already exists you build a new one and copy the layout across.

Click any cell inside your data. Convert it to a table first with Ctrl + T so new rows are picked up on refresh.
Go to Insert > Tables > PivotTable (in Excel 365 choose From Table/Range).
In the Create PivotTable dialog, tick Add this data to the Data Model at the bottom, then click OK.
Build the report as usual. Drag Supervisor to Rows and Employee to Values. Excel shows Count of Employee.
Click the drop-down arrow on Count of Employee in the Values area and choose Value Field Settings.
On the Summarize Values By tab, scroll to the bottom of the list and select Distinct Count. It is always the last item.
Click OK. The heading changes to Distinct Count of Employee and each row now shows the number of different employees.

In a Data Model pivot table the field list groups fields under the table name (for example Table1) and shows a small cylinder icon. Right-clicking a value cell still offers Summarize Values By, and the Distinct Count option appears under More Options in that menu.

Version and platform support

The Data Model is built into Excel 2013 and every later Windows version, so Distinct Count works in Excel 365, 2021, 2019 and 2016. Excel 2010 needs the free Power Pivot add-in and has no tick box. Excel for Microsoft 365 on Mac gained the Data Model in 2021 builds; Excel 2016 and 2019 for Mac do not have it. Excel for the web can open and refresh a Data Model pivot table built on the desktop but does not offer the tick box when inserting one.

Worked example: distinct employees per supervisor

The course practice file lists one row per sale with the date, supervisor, employee and revenue. Here is a cut-down version with eight rows so you can follow the numbers by hand.

Date	Supervisor	Employee	Revenue
01-Jul-2026	Rahul	Amit	1,200
01-Jul-2026	Rahul	Neha	900
02-Jul-2026	Rahul	Amit	1,500
02-Jul-2026	Priya	Sunil	800
03-Jul-2026	Priya	Sunil	1,100
03-Jul-2026	Priya	Kavita	700
04-Jul-2026	Rahul	Neha	1,300
04-Jul-2026	Priya	Ravi	900

Create the pivot table with Add this data to the Data Model ticked, put Supervisor in Rows and Employee in Values, then change the field to Distinct Count. The result is:

Supervisor	Count of Employee	Distinct Count of Employee
Priya	4	3
Rahul	4	2
Grand Total	8	5

Rahul has four sales rows but only two people (Amit and Neha). Priya has four rows and three people (Sunil, Kavita and Ravi). The grand total of 5 is the number of different employees in the whole table, not the sum of the rows above it. If Amit had also sold under Priya, each supervisor row would count him, but the grand total would still count him once. That is correct behaviour and one reason Distinct Count totals sometimes look “wrong” at first glance.

Count unique values without the Data Model

If you cannot use the Data Model (older Excel, a shared workbook that must stay small, or a pivot table that already exists), three alternatives give the same numbers.

1. Helper column with COUNTIFS, then Sum

Add a column to the source data that gives each supervisor-employee combination a fractional share so that every combination adds up to exactly 1. With Supervisor in column B and Employee in column C, enter this in D2 and fill down:

=1/COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,C2)

Amit appears twice under Rahul, so each of his rows gets 0.5 and the pair sums to 1. Now put the helper column in the Values area summarised as Sum. Each supervisor row shows the distinct number of employees. The grand total counts distinct supervisor-employee pairs, so an employee who works under two supervisors is counted twice there. This method works in every Excel version and updates on refresh. See the COUNTIFS lesson for the criteria syntax.

2. UNIQUE with COUNTA or ROWS (Excel 365 and 2021 only)

Dynamic array functions can count unique values directly in a cell, outside the pivot table:

=COUNTA(UNIQUE(C2:C9))
=ROWS(UNIQUE(FILTER(C2:C9,B2:B9="Rahul")))

The first formula returns 5, the second returns 2. Wrap the range in a table reference such as Table1[Employee] so it grows with the data. These functions are not available in Excel 2019 or earlier, where the formula returns #NAME?.

3. Power Query Group By

Select the data and choose Data > Get & Transform Data > From Table/Range. In the Power Query Editor, keep only the Supervisor and Employee columns, use Home > Remove Rows > Remove Duplicates, then Home > Group By Supervisor with the operation Count Rows. Load the result to a sheet. The Group By dialog also offers Count Distinct Rows, which counts unique whole rows within each group.

Limitations of a Data Model pivot table

The Data Model unlocks Distinct Count and relationships between tables, but the pivot table behaves like an OLAP report and loses a few classic features.

No calculated fields or calculated items. PivotTable Analyze > Fields, Items & Sets > Calculated Field is greyed out. Use a DAX measure instead: right-click the table name in the field list and choose Add Measure (or Power Pivot > Measures > New Measure). A measure such as =DISTINCTCOUNT(Table1[Employee]) gives the same result and can be reused in every pivot table on the model.
Date grouping by right-click works in Excel 2016 and later. Excel 2013 cannot group dates in a Data Model pivot table; add Year and Month columns to the source instead.
Show Details (double-clicking a value) returns at most 1,000 source rows by default. Raise the limit under Data > Queries & Connections > Connections tab > ThisWorkbookDataModel > Properties > Usage > Maximum number of records to retrieve.
File size. The model stores a compressed copy of the data inside the workbook. For very large sources the file grows, and saving takes longer.

Tips and common mistakes

Tick the box before you click OK. The Data Model choice is made when the pivot table is created. If you forget, delete the pivot table and insert it again.
Clean the key column first. “Amit “, “amit” and “Amit” are treated as the same value (the Data Model ignores case) but a trailing space creates a second item. Run TRIM or Data > Data Tools > Remove Duplicates on a copy to check.
Blanks count as one item. A blank cell in the Employee column becomes a (blank) member and adds 1 to the distinct count. Filter it out with the row label filter or fill the gaps in the source.
Do not expect totals to add up. The grand total counts unique items across the whole table, so it is often lower than the sum of the rows. That is correct.
Distinct Count on an amount column is usually meaningless. Use it on IDs, names, codes and dates, not on revenue.
Keep the source as an Excel table. Ranges added to the Data Model are frozen at their original size; a table extends automatically when you refresh.

Errors and how to fix them

Problem	Cause	Fix
Distinct Count is not in the Summarize Values By list	The pivot table is not on the Data Model	Insert a new pivot table and tick Add this data to the Data Model
Add this data to the Data Model is greyed out	Workbook is in .xls compatibility mode, or Excel 2010 or older	Save as .xlsx and reopen; in Excel 2010 install Power Pivot
Distinct count is one higher than expected	Blank cells counted as an item	Filter out (blank) or fill the blanks in the source
The same name is counted twice	Trailing spaces or non-printing characters	Clean the column with TRIM and CLEAN, then refresh
Grand total is lower than the sum of the rows	An item appears under more than one row label	No fix needed; this is the correct distinct total
Calculated Field is greyed out	Data Model pivot tables do not support calculated fields	Add a DAX measure from the field list instead
Amount field defaults to Count instead of Sum	Column contains text, blanks or numbers stored as text	Convert the column to numbers, refresh, then set Sum
Show Details stops at 1,000 rows	Default drill-through limit for the Data Model connection	Raise Maximum number of records to retrieve in Connection Properties

Practice exercise

Open the course practice file Pivot-Table.xlsx and try the following:

Insert a pivot table with Add this data to the Data Model ticked. Put Supervisor in Rows and Employee in Values, then change the field to Distinct Count. Compare the number with a plain Count of the same field.
Add Product to Values a second time and set it to Distinct Count. How many different products did each supervisor’s team sell?
Add Date to the Columns area and group it by month. Check whether the distinct employee count per month adds up to the grand total, and explain why it does not.
Add a helper column to the source with =1/COUNTIFS(...) on Supervisor and Employee, build a normal pivot table and confirm that Sum of the helper matches the Distinct Count from task 1.
If you have Excel 365, write a COUNTA(UNIQUE(...)) formula next to the pivot table that returns the total number of employees, and check it against the grand total.

Key takeaways

Count counts rows; Distinct Count counts each different value once.
Distinct Count only appears when the pivot table is built with Add this data to the Data Model ticked (Excel 2013 and later).
Find it at the bottom of the Summarize Values By list in Value Field Settings.
Grand totals for a distinct count are not the sum of the rows, and blanks count as one item.
Without the Data Model, use a 1/COUNTIFS helper column, COUNTA(UNIQUE()) in Excel 365, or Power Query Group By.
Data Model pivot tables lose calculated fields and items; use DAX measures instead.

Related lessons

Frequently asked questions

Why is Distinct Count missing from my pivot table?

Distinct Count only exists in pivot tables that use the Data Model. If you inserted the pivot table without ticking Add this data to the Data Model, the Summarize Values By list ends at Varp. Delete the pivot table, insert it again with the box ticked, and the option appears as the last entry in the list.

Can I add an existing pivot table to the Data Model?

No. The Data Model choice is made in the Create PivotTable dialog and cannot be changed later. Note the layout of the existing report, insert a new pivot table from the same source with the tick box selected, and rebuild the rows, columns, filters and slicers. Slicers can be reconnected through Report Connections.

How do I count unique values in a pivot table in Excel 2010?

Excel 2010 has no built-in Data Model, so add a helper column to the source such as =1/COUNTIFS(range,criteria) and summarise it as Sum in a normal pivot table. Alternatively install the free Power Pivot add-in, load the table there, and create a DISTINCTCOUNT measure.

Does Distinct Count work on the Mac?

Yes in Excel for Microsoft 365 on Mac, which gained Data Model support in 2021. Insert the pivot table, tick Add this data to the Data Model and choose Distinct Count in Value Field Settings exactly as on Windows. Excel 2016 and 2019 for Mac do not include the Data Model.

Why does the grand total not equal the sum of the distinct counts?

Because an item that appears under several row labels is counted once in each row but only once in the total. Three employees who each sold in two regions give 6 in the region rows and 3 in the grand total. This is the correct answer to “how many different employees” for the whole table.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.