Distinct Count in Excel Pivot Tables: A Practical Guide

To get a distinct count in a PivotTable in Excel for Microsoft 365 on Windows, create the PivotTable with Add this data to the Data Model selected, then set the value field’s summary calculation to Distinct Count. This counts each different value once within the relevant group, rather than counting every source row.

What Distinct Count Means

A regular Count in a PivotTable counts records that contain a value in the selected field. Distinct Count instead counts the number of different values in that field. If the source contains customer ID C101 on five transaction rows, a regular Count includes five records, while a distinct count includes one customer.

This is useful whenever the question is about unique entities rather than activity: unique customers, products sold, tickets handled, employees assigned, or locations visited. For example, a sales report may need to show how many individual customers placed orders in each region, even when some customers placed several orders.

Distinct Count is an aggregation choice for a PivotTable value field. It does not remove duplicate rows from the source or change the underlying data. The repeated records remain available for other calculations, such as total sales or transaction count.

How to Add Distinct Count in Excel

The important requirement is that the PivotTable use the Excel Data Model. A standard PivotTable created without the Data Model generally does not offer Distinct Count in its value summaries.

Select a cell in the source range, or select a cell in an Excel table. A table is helpful because it expands when you add new rows.
Choose Insert > PivotTable. In the dialog, confirm the source and choose where to place the PivotTable.
Select Add this data to the Data Model, then create the PivotTable. The wording and position of this checkbox can vary slightly with the dialog layout.
In the PivotTable Fields pane, drag the field you want to count into Values. Drag category fields, such as Region or Month, into Rows or Columns as needed.
Open the menu for the value field and choose Value Field Settings. Under Summarize Values By, choose Distinct Count, then select OK.

If the option is missing, the first thing to check is whether the PivotTable was added to the Data Model. Changing a regular PivotTable’s value setting alone does not add the model requirement after the fact. You may need to create a new PivotTable with the checkbox selected.

A Small Example

Suppose a sales table has five rows with these Region and Customer ID pairs: North–C101, North–C101, North–C205, South–C101, and South–C330. Put Region in Rows, Customer ID in Values, and set the value calculation to Distinct Count. North returns 2 because it has two different IDs; South also returns 2.

The grand total is 3, not 4. C101 appears in both regions, but it is still one distinct customer across the whole source. This difference is a useful check: distinct counts in separate groups can overlap, so adding the group results may not produce the overall unique count.

The same logic applies to dates, products, and other fields. If a field contains order dates with several orders on the same date, Distinct Count reports the number of different date values in each group. It does not count calendar days that have no source record, nor does it automatically interpret dates as months or business days.

What Happens to Totals and Filters

A PivotTable grand total for Distinct Count is normally calculated over all records included in the PivotTable, rather than by adding the displayed group counts. That is why the grand total in the example is 3 even though the two region rows each show 2. The groups share a customer.

Filters change which source records are included in the calculation. Filtering the PivotTable to one region, for example, changes the distinct count to the number of different IDs in that region. Report filters, slicers, and row or column selections can therefore change the result. When checking a number, confirm which filters are active and whether the intended question is “unique within each group” or “unique across the selected data.”

Subtotals follow the same principle: a subtotal reflects the distinct values in the records covered by that subtotal. It need not equal the sum of the distinct counts shown on the lines beneath it. This is expected behavior, not necessarily a calculation error.

Data Quality and Common Problems

A distinct count is only as meaningful as the field being counted. Customer IDs should be stable identifiers, not names that may be spelled differently or shared by multiple people. If one customer appears as C101 in one row and C-101 in another, Excel treats those as different text values. Extra spaces or inconsistent formatting can likewise split what a business considers one entity into multiple values.

Before relying on a result, consider these checks:

Confirm that the field contains the identifier you actually intend to count, and that IDs are stored consistently.
Look for leading or trailing spaces, inconsistent punctuation, and values that mix text and numbers.
Check whether blanks are present. Blank entries can affect distinct-value results, so decide whether a missing ID should be excluded or treated as a category and verify the outcome against a small sample.
Refresh the PivotTable after changing source data. For a PivotTable using the Data Model, use Data > Refresh All when you want to refresh the workbook’s connected data and model content.
Check filters and slicers before comparing the count with another report.

Also distinguish a genuinely repeated entity from a data-entry error. Two rows with the same customer ID may represent two valid orders, which should remain separate for a transaction count but count once for unique customers. Removing duplicates from the source simply to make a PivotTable number smaller can discard legitimate transaction detail.

Choosing the Right Counting Method

Use Count when the question is how many records have a nonblank value. Use Distinct Count when the question is how many different values occur. Use Sum for additive numeric amounts such as sales, and use Average when the intended result is an average rather than a record or entity count.

For instance, a table with 100 order lines might contain 100 order records, 42 distinct customers, and 18 distinct products. Those are all valid answers to different questions. A chart titled “Orders” should not use the distinct-customer measure unless that is what the chart is meant to show.

Think about the level at which uniqueness applies. “Unique customers by month” counts customers separately within each month; a customer ordering in three months appears in each month’s count. The grand total can still count that person once across the selected period. If the business definition instead treats each customer-month as a separate unit, create or define a suitable combined key rather than assuming a customer ID alone expresses that definition.

Alternatives and Platform Differences

If you need a quick unique count outside a PivotTable, Microsoft 365 for Windows includes dynamic-array functions such as UNIQUE and FILTER. With customer IDs in B2:B6 and the example values C101, C101, C205, C101, and C330, use =COUNTA(UNIQUE(FILTER(B2:B6,B2:B6<>""))) in a cell outside the table. It returns 3. The FILTER condition excludes blank cells; if blanks should count as a distinct value, do not apply that exclusion and consider how the source represents missing values.

A formula can be convenient for a single summary, but a PivotTable is usually easier when readers need to slice the result by several categories. For recurring data-cleaning and reporting workflows, Power Query can help standardize identifiers before loading the data. Removing duplicates is another option when the goal is to create a separate list of unique records, but it is not a substitute for counting unique values while preserving all transactions.

These instructions target Excel for Microsoft 365 on Windows. Excel for the web and Excel for Mac do not provide the same Windows Data Model workflow for creating this kind of PivotTable; the Data Model checkbox and the Distinct Count setup described above may not be available there. If you must work on Mac or in a browser, use an available formula-based approach, prepare a unique summary in a compatible tool, or create the Data Model PivotTable in Excel for Windows and then check that the receiving app supports the workbook features you need.

Frequently Asked Questions

Why can’t I see Distinct Count in Value Field Settings?
Most often, the PivotTable was created without being added to the Data Model. Create a new PivotTable and select Add this data to the Data Model in the creation dialog, then check the value field settings again.

Does Distinct Count remove duplicate rows?
No. It changes how the PivotTable summarizes a field; it does not delete or alter source records. Other value fields can still count transactions or sum sales from those same rows.

Why is the grand total smaller than the sum of the rows?
Groups can contain some of the same values. The grand total counts each distinct value once across its full scope, while each row counts unique values within its own group.

Does Distinct Count include blank cells?
Blank values can affect a distinct count, and the outcome depends on how missing values are represented in the source and model. Test a small filtered sample and decide whether blanks should be included in the business definition.

Can I use Distinct Count in a normal PivotTable?
The built-in Distinct Count summary requires a PivotTable based on the Data Model. A regular PivotTable without that model does not provide the same value-field option.

Will a distinct count update when I add new rows?
It updates after the PivotTable’s source or model is refreshed. Using an Excel table as the source helps include appended rows, but refresh the PivotTable or choose Data > Refresh All when needed.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.