To count unique values in an Excel PivotTable, create the PivotTable with Add this data to the Data Model selected, place the field you want to count in Values, then choose Value Field Settings > Summarize Values By > Distinct Count. This option is available in Excel for Microsoft 365 on Windows for PivotTables based on the Data Model; an ordinary PivotTable does not offer it.
What “unique count” means
A regular count tallies records. A distinct count tallies different values in a field, counting repeated values once. For example, if a customer column contains Ava, Ben, Ava, and Chen, the regular count is four records, while the distinct count is three customers. This is useful when a source table has several transactions per customer and you want to know how many customers appear in each category.
In PivotTable terminology, Count and Distinct Count are different summaries. Count is appropriate for questions such as “How many rows or entries are there?” Distinct Count is appropriate for “How many different customers, products, or order IDs are represented?” Make sure the field you put in Values is the field whose different entries you want counted.
Create a PivotTable that supports Distinct Count
The Data Model requirement is easy to miss: Excel needs to add the source data to the model when it creates the PivotTable. On Excel for Microsoft 365 for Windows, prepare a clean table or range, then follow these steps:
- Select a cell in the source data. If it is not already an Excel table, consider choosing Insert > Table and confirming that the table has headers.
- Choose Insert > PivotTable.
- In the PivotTable creation dialog, confirm the table or range and where to place the report.
- Select Add this data to the Data Model, then create the PivotTable.
- In the PivotTable Fields pane, drag the field to count into Values. Add any grouping fields, such as Region or Month, to Rows or Columns.
- In the Values area, open the field’s menu and choose Value Field Settings. Under Summarize Values By, choose Distinct Count, then select OK.
The Values area may initially say “Sum of Customer ID” or “Count of Customer ID,” depending on the field’s contents. Changing its summary to Distinct Count changes the calculation, not the underlying source data.
Example: distinct customers by region
Imagine a sales table with columns for Region, Customer, and Order Amount. A customer may have several orders in the same region. To report the number of different customers in each region, add Region to Rows and Customer to Values, then set Customer’s summary to Distinct Count. The result is one customer total per region, rather than one total per transaction.
For instance, suppose the North region’s customer entries are C101, C102, C101, and C103. The distinct total is 3, even though there are 4 rows. If C101 also appears in the South region, it can be counted once in North and once in South: each region’s result is calculated within that group. The Grand Total is a distinct count across the entire source, not necessarily the sum of the regional totals. When the same customer occurs in multiple regions, adding the regional counts would count that customer more than once.
Prepare the data for a reliable result
A PivotTable can only count the values it receives. Before building it, check that the source column consistently identifies the thing you mean to count. If a customer is sometimes recorded as “C101” and elsewhere as “Customer 101,” Excel sees two different entries. Similarly, trailing spaces or inconsistent identifiers can create apparent duplicates. Standardize codes and labels in the source when those differences do not represent genuinely different entities.
Also check for missing values. A blank is not a useful customer or product identifier, and blank handling can make a distinct total confusing. Review the source and decide whether empty identifiers should be filled, excluded, or reported separately. Data Model distinct-count behavior can treat a blank as a distinct value, so do not assume an empty entry will simply disappear. Filter out blanks in the PivotTable or clean them from the source if they should not contribute.
- Use a single header row with a distinct, nonblank name for each column.
- Keep one record per row and avoid merged cells or subtotal rows inside the source range.
- Use stable IDs when names can vary or different people can share the same name.
- Confirm that the PivotTable source includes every intended row before interpreting the total.
Refresh the PivotTable after source changes
PivotTables use a stored view of their source data, so edits or newly added records may not appear immediately. Select a cell in the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. To update all PivotTables and connections in the workbook, use Data > Refresh All. If the source is an Excel table, new rows added directly beneath it are generally included when the PivotTable refreshes; a fixed range may need to be expanded or replaced with a table.
If a refresh does not change the result, inspect the PivotTable source and confirm that the new rows are included. Also check active filters, slicers, and report filters: they can change which records contribute to the visible number. Refreshing updates the calculation from available source data; it does not fix inconsistent IDs or remove unwanted records automatically.
Why Distinct Count may be missing
The usual reason is that the PivotTable was created without being added to the Data Model. In that case, Value Field Settings offers standard summaries such as Sum, Count, and Average, but not Distinct Count. The checkbox is chosen during creation; it generally cannot be switched on for an already-created conventional PivotTable. Create a new PivotTable from the same source and select Add this data to the Data Model.
Other possible causes include using an Excel edition or platform that does not provide the same Data Model authoring features, or working with a PivotTable connected to an external source with different capabilities. Excel for Microsoft 365 on Windows is the clearest route for creating this type of PivotTable. Excel for Mac and Excel for the web have different Data Model and Power Pivot support, and may not offer the same creation or editing steps. If you need to work on a Data Model PivotTable, use a supported Windows desktop version rather than assuming the Mac or web interface has an equivalent option.
If you cannot use the Data Model, a worksheet formula can calculate distinct values in a range. With names in A2:A8, and with blank cells excluded, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A8,A2:A8<>""))),0). For the values Ava, Ben, Ava, blank, Chen, Ben, and blank, the result is 3. UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. This formula gives one result for the specified range; it does not automatically create grouped PivotTable totals.
Distinct Count compared with a formula
Use the Data Model PivotTable when you want interactive summaries by categories, such as distinct customers by region, and need to filter or rearrange those summaries. Use a formula when you need a simple distinct total in a worksheet, cannot create a Data Model PivotTable, or want the result alongside other calculations. A formula based on UNIQUE can also be adapted to a filtered subset, but that requires the criteria to be represented in the formula.
Both approaches depend on data quality and on how blanks are treated. They can also produce different-looking results if one includes empty entries and the other excludes them. Decide whether blanks should count before comparing totals. For case-sensitive distinctions, do not assume either method treats capitalization as separate identity; Excel’s usual text comparisons are not case-sensitive. If “abc” and “ABC” must be different IDs, normalize the data or use a deliberately case-sensitive method.
Frequently Asked Questions
Can I count unique values in a normal PivotTable without the Data Model?
No. The built-in Distinct Count summary is available for PivotTables created with the source added to the Data Model. Recreate the PivotTable with that option selected, or use a worksheet formula such as UNIQUE with FILTER for a standalone count.
Why does my Grand Total not equal the sum of the rows?
Each row group gets its own distinct count, while the Grand Total counts distinct values across all included records. If the same customer appears in two regions, that customer contributes to both regional counts but only once to the overall total.
Does Distinct Count include blank cells?
A blank can be treated as a distinct value by a Data Model distinct-count calculation. If blanks should not count, filter them out or clean the source, and verify the result against the intended business rule.
Can I change an existing PivotTable to use Distinct Count?
Not if it was created as a conventional PivotTable without the Data Model. Create a new PivotTable from the source and select Add this data to the Data Model in the creation dialog, then choose Distinct Count in Value Field Settings.
Will a distinct count update when I add more rows?
It updates after the PivotTable refreshes, provided the added rows are inside its source. An Excel table usually expands when rows are added directly below it; a fixed source range may need to be updated first. Choose PivotTable Analyze > Refresh after confirming the source includes the new records.
Related
- Conditional Formatting in Excel for a Specific Date
- Excel Conditional Formatting Icons: How to Use Them
- Excel Conditional Formatting Icon Sets: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find a Percentage in Excel: Formulas, Examples, and TipsTo find what percentage one value is of another in Excel, divide the part by the total, then…
- Filter Excel for Empty Cells: Blanks and FormulasTo filter for empty cells in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Filter Multiple Criteria in One Column: How It WorksTo filter one Excel column for several allowed values, select the column’s filter arrow and check each value…
- Clear a Filter in an Excel Table: Steps and OptionsTo clear a filter in an Excel table, open the drop-down arrow in the filtered column’s header and…
- Concatenate Text and Formula in Excel: ExamplesTo concatenate text and a formula in Excel, join them with the ampersand (&) operator. With the words…
- Excel UPPER Function: How to Make Text All CapsExcel’s uppercase text function is UPPER. In Microsoft Excel for Microsoft 365, enter =UPPER(A2) to convert the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.