Excel Pivot Table Unique Count: How to Get It

Charts & pivot tables · Updated November 18, 2026

Yes—Excel can count unique values 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 to Distinct Count in Value Field Settings.

What a PivotTable unique count means

A unique count, also called a distinct count, counts each different value in a field once within the current PivotTable grouping. If a sales table contains the customer IDs 104, 104, 208 and 319, the distinct count is 3, while an ordinary count of the entries is 4. The distinction matters whenever your source contains repeated transactions, visits, orders or other records for the same person or item.

PivotTables normally offer familiar summaries such as Sum, Count and Average. Distinct Count is available when the data is placed in Excel’s Data Model. It is not the same as removing duplicate rows from your source: the PivotTable summarizes the data without deleting or changing the original records.

How to create a PivotTable with Distinct Count

These steps apply to Excel for Microsoft 365 on Windows. A structured Excel Table is a useful source because it can expand as you add records.

  1. Select a cell in the source data. Check that each column has a heading and that the data has no completely blank rows or columns within the range.
  2. Choose Insert > PivotTable. In the Create PivotTable dialog, confirm the table or range and choose where to put the report.
  3. Select Add this data to the Data Model, then choose OK. This selection is essential: without the Data Model, Distinct Count may not appear among the summary options.
  4. In the PivotTable Fields pane, drag the field to be counted into Values. Drag any grouping fields, such as Region or Month, into Rows or Columns.
  5. In the Values area, open the field’s menu and choose Value Field Settings. Under Summarize Values By, select Distinct Count, then choose OK.

If Distinct Count is missing, first check whether the PivotTable was created with the Data Model option enabled. Changing the summary setting on an ordinary PivotTable does not add that capability; in many cases you will need to create a new PivotTable and select the option during setup.

How grouping changes the result

The distinct count is calculated separately for each row or column group. For example, put Region in Rows and Customer ID in Values, summarized by Distinct Count. The result for each region is the number of different customers who appear in that region’s records.

A customer who buys in both East and West is counted once in East and once in West. Those regional results therefore cannot necessarily be added to get the overall number of unique customers: the same customer may occur in more than one group. If you need a grand total of unique customers across all regions, use the overall total or an ungrouped distinct count, rather than summing the regional figures.

Also distinguish unique values from unique rows. If the question is “How many different customer IDs are present?”, count the Customer ID field. If it is “How many distinct customer-and-product combinations are present?”, counting either field alone answers a different question. You need to identify or create a key for the combination, or prepare the data by deduplicating on both fields.

Worked example: customers by region

Suppose a transaction table has three columns: Region, Customer ID and Order ID. It contains six orders: East/104, East/104, East/208, West/104, West/319 and West/319. Place Region in Rows and Customer ID in Values, then choose Distinct Count. East returns 2 customers (104 and 208), and West returns 2 (104 and 319). The grand total is 3, because customer 104 appears in both regions but is counted once overall.

If you instead choose the ordinary Count summary for Customer ID, East returns 3 and West returns 3, counting order records rather than different customers. That is a common source of unexpectedly high results. When validating a report, compare the result with the question’s unit: people, orders, products, or some other entity.

Blanks, duplicates and data quality

Repeated values are the reason to use Distinct Count; they are not an error to fix in the source. Blank values require more care. A blank can be treated as a distinct value in the Data Model’s distinct-count calculation, so a field containing several blanks may contribute one blank value to a result. If blanks should not count, filter them out of the PivotTable or clean them from the source, then check that the total matches the intended definition.

Other data inconsistencies can make values appear different even when they look similar. For example, customer IDs stored as numbers in some rows and text in others, or IDs with leading or trailing spaces, can undermine comparisons. Check the source field for consistent data types and standardized values. Avoid casually converting identifiers such as 00125 to numbers if the leading zeros are meaningful.

Distinct Count counts different field values, not records that are “almost the same.” It will not infer that two spellings refer to one person, merge alternate customer IDs, or decide whether capitalization differences represent the same entity. Standardize such business rules before relying on the result.

Refresh the count when data changes

A PivotTable is a summary, not a live view that recalculates every time a source cell changes. After editing or adding source records, right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh all PivotTables and connections in the workbook, use Data > Refresh All.

If new rows are not included, check the source range. A source formatted as an Excel Table generally expands when you type directly below it, making it easier for a later refresh to include appended rows. With a plain cell range, you may need to change the PivotTable’s data source to cover the additional rows. A refresh updates the report from its defined source; it does not automatically correct an incomplete source range.

Formula alternative and other options

For a simple list where you only need a distinct count—not the interactive grouping and filtering of a PivotTable—a dynamic-array formula can be more direct. In Excel for Microsoft 365 or Excel 2021, with the values in A2:A10, including any blanks, this formula counts distinct nonblank entries:

=COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>"")))

For the sample values Ana, Ben, Ana, blank, Cara, Ben, Dev, Cara and blank in A2:A10, the formula returns 4: Ana, Ben, Cara and Dev. The FILTER condition excludes empty cells before UNIQUE returns one copy of each remaining value; COUNTA counts those results. If the range has no nonblank entries, FILTER can return an error unless you add an appropriate empty-result option.

Use the Data Model PivotTable when you want distinct counts by several dimensions, such as customer by region and month, with PivotTable filters and rearrangeable fields. Use a formula for a compact result that should update in the worksheet. Power Query is another useful option for repeatable data preparation: you can remove duplicates using one or more selected columns, then group and count rows. That approach changes the prepared query output, not the original source table.

Excel for the web and Mac may not offer the same Data Model creation workflow as Excel for Windows, and availability can vary by version and workbook. If you cannot find the Data Model checkbox or Distinct Count setting, use the formula method if your version supports UNIQUE and FILTER, or prepare a distinct list with Power Query where available. The formula above requires Excel 2021 or Microsoft 365; older Excel versions do not include these dynamic-array functions.

Common problems and checks

  • Distinct Count is not listed: The PivotTable may not have been created using Add this data to the Data Model. Re-create it with that option selected.
  • The result is larger than expected: Confirm that the Values field is the identifier you intend to count, not an order number or another field that varies by record.
  • The grand total differs from the sum of group rows: This can be correct when the same item appears in multiple groups. The total counts distinct values across the whole report.
  • New records do not appear: Refresh the PivotTable and verify that its source includes the appended rows.
  • A blank seems to affect the count: Filter or clean blank entries if the reporting definition excludes them, then refresh and verify the result.

Frequently Asked Questions

Can I get a unique count in a normal Excel PivotTable?
Not through the standard summary choices in a PivotTable based only on a worksheet range. Create the PivotTable with Add this data to the Data Model selected, then choose Distinct Count in Value Field Settings.

Is Distinct Count the same as Count?
No. Count counts entries in the group, including repeats; Distinct Count counts different values. If customer 104 has five orders in one region, Count returns five records while Distinct Count returns one customer.

Can I count unique customers for each month?
Yes. Put the month field in Rows or Columns and Customer ID in Values, then set the value field to Distinct Count. Each month is evaluated separately, so a customer appearing in several months is counted once in each of those months.

Why does the grand total not equal the sum of the rows?
The same value may occur in multiple row groups. Excel’s overall total counts each value once across the whole set, whereas adding row-level distinct counts counts a cross-group value multiple times.

Can I use Distinct Count in Excel for Mac or Excel for the web?
Support for creating Data Model PivotTables differs from Windows and can depend on the Excel version. If the Data Model option or Distinct Count setting is unavailable, use a supported dynamic-array formula or prepare the data with an available query tool.

Related

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.