To count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data Model selected, then place your category field in Rows and the field you want to count in Values. Open that value field’s settings and choose Distinct Count. This counts each different value once within each category, even when the source has duplicate rows.
What You’ll Need
- Excel for Microsoft 365 on Windows. The Data Model method also works in many recent Windows desktop editions of Excel, but the exact options can vary by version.
- A clean source range with a header in every column and one record per row. For example, a sales list might have columns for Region, Product, and Order Date.
- A field whose different values you want to count, such as Product, Customer ID, or Order Number. Decide whether blank cells should be included or excluded before building the report.
- A category field to group the results by, if you need a separate distinct count for each group. For example, count products by region rather than count products across the whole worksheet.
The steps below are for Excel on Windows. Excel for Mac and Excel for the web may not offer the same Data Model and Distinct Count options. A helper-column method later in this guide is a practical alternative when that feature is unavailable.
Step-by-Step
- Check and prepare the source data. Make sure the first row contains clear, nonblank column headings, such as Region and Product. Keep each record on one row, avoid merged cells, and remove completely empty rows within the data. For a product count, each row could represent one order line; repeated product names are expected because the PivotTable will count those duplicates only once. If the source is a range, click inside it and press Ctrl+T to convert it to an Excel table. Confirm My table has headers and select OK. A table expands more reliably when you add records later. On Mac, the equivalent shortcut for creating a table is usually Command+T.
- Select the source and start a PivotTable. Click any cell in the table or range. On the ribbon, choose Insert > PivotTable. Excel should detect the whole table or contiguous data range. Check the range shown in the dialog before continuing; a range that stops short of the last row will produce incomplete counts.
- Add the source to the Data Model. In the Create PivotTable dialog, choose where to place the PivotTable, usually New Worksheet. Select Add this data to the Data Model, then select OK. This checkbox is essential: the ordinary PivotTable calculation options do not include Distinct Count unless the PivotTable uses the Data Model. If you do not see the checkbox, check your Excel edition or use the helper-column workaround below.
- Choose the field to group by. In the PivotTable Fields pane, drag the category field to the Rows area. For example, drag Region to Rows to get one result for each region. If you want only one distinct count for the entire dataset, you can leave Rows empty; the PivotTable will show a grand total.
- Add the field to count. Drag the field whose different values you want counted to the Values area. For a customer count, use Customer ID rather than a transaction amount. Excel may initially summarize the field as Count or Sum. That first summary is not yet the distinct count, so continue to the next step.
- Change the calculation to Distinct Count. In the Values area, open the drop-down beside the field name and select Value Field Settings. On the Summarize Values By tab, scroll through the calculation list and select Distinct Count, then select OK. Depending on window size, you may need to widen the dialog or scroll to see the option. The PivotTable should now show one count for each row group, with duplicates within that group counted once.
- Check the result with a small sample. Pick one category and compare its result with the source records. If a region has product values Red, Blue, Red, and Green, its distinct product count should be 3, not 4. If the PivotTable shows a different number, check that the correct field is in Values, the correct field is in Rows, and the source range includes the records you expect.
- Refresh after changing the source. When you add or edit records, right-click the PivotTable and choose Refresh, or select PivotTable Analyze > Refresh. If the source is an Excel table, new rows added to that table are normally included when you refresh. If you used a fixed cell range, you may need to change the source range first using PivotTable Analyze > Change Data Source.
Example: Suppose the source has Region and Product columns. The East region has five order rows, but the products listed are Pen, Notebook, Pen, Folder, and Notebook. The PivotTable should report 3 distinct products for East. If West has Folder, Pen, and Folder, it should report 2. The overall grand total is the number of distinct products across the whole source, not necessarily the sum of the regional counts: a product sold in both regions appears once in each regional count but once in the overall distinct count.
Common Mistakes
- Creating a standard PivotTable without the Data Model. The Distinct Count option is tied to the Data Model. If you created the PivotTable without selecting Add this data to the Data Model, you generally need to create a new PivotTable with that option selected; changing Value Field Settings alone will not add the missing calculation.
- Confusing distinct values with values that occur only once. Distinct Count counts each different value once, regardless of how many times it appears. In a list containing Red, Red, and Blue, the distinct count is 2. A count of values that occur exactly once would be 1, because only Blue appears once. Those are different questions.
- Counting the wrong field. Putting Region in Values counts or summarizes regions, not products or customers. Put the grouping field in Rows and the field to be counted in Values. For customer counts, a stable customer ID is usually safer than a name, because two people may share a name or one person’s name may be spelled inconsistently.
- Assuming the grand total is the sum of group results. Distinct values can appear in multiple groups. If the same product is sold in East and West, it contributes to both regional counts but only once to the grand total across all regions.
- Overlooking blanks and inconsistent labels. Blank entries and variations such as North, north, and North with a trailing space can lead to results that seem unexpected. Check the source values and use PivotTable filters or clean the data when you need a consistent count. The Data Model can treat a blank as a distinct value, so do not assume blanks are ignored.
Tips
- Choose the right level of detail. Add one field to Rows for a count by that category. Add a second field beneath it to break the first group down further, such as Region followed by Salesperson. The distinct count is then calculated within each displayed group.
- Use IDs when labels can vary. A customer ID or product code reduces the risk that spelling, punctuation, or capitalization differences make the same real-world item appear to be several values. Clean duplicates in the source if they represent the same item but have different codes.
- Filter records before interpreting the count. You can drag a field to Filters or use the filter controls on the PivotTable headings. The displayed distinct count then reflects the records remaining in the PivotTable’s filter context. Check active filters before comparing totals.
- Format the report after checking it. Rename a value heading to something clear, such as Distinct Products, by selecting the heading cell and typing a new label. Avoid using a heading that exactly matches a source field name if Excel rejects it; use a slightly different label.
- Keep a copy of the raw data. A PivotTable summarizes the source; it does not repair inconsistent labels or delete duplicates from the underlying records. Keep the source sheet intact so you can trace a surprising count back to its original rows.
Troubleshooting
Distinct Count is missing from Value Field Settings. The most common cause is that the PivotTable was not added to the Data Model. Create a new one through Insert > PivotTable and select Add this data to the Data Model in the creation dialog. If the option is unavailable, your Excel version or platform may not support this workflow. Excel for Mac and Excel for the web do not consistently expose the same Data Model PivotTable features as Excel for Windows, so use the workaround below or open the file in a supported Windows desktop edition.
The number is too high or too low. Confirm the field in Values is the one you intend to count and inspect a single category’s source rows. Look for values that differ only by spaces, spelling, or formatting, and check whether blank entries should be part of the result. Also check PivotTable filters and slicers; they may be excluding rows. If the source contains a mix of numbers stored as text and actual numbers, standardize the values before relying on the count.
New records do not appear. Refresh the PivotTable. If they are still missing, check the source range using PivotTable Analyze > Change Data Source. A fixed range may not include appended rows. Converting the source to an Excel table before creating the PivotTable helps prevent that issue, but you still need to refresh after edits.
You need a workaround without Data Model support. Add a helper column that marks the first occurrence of each Category-and-Item pair, then sum that marker in a regular PivotTable. Assume categories are in A2:A8, item names are in B2:B8, and the example data has West/Red, West/Blue, West/Red, West/Green, East/Red, East/Red, and East/Gold. In C2, use =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0) and fill it down through C8. The results are 1, 1, 0, 1, 1, 0, and 1; summing the marker gives West 3 and East 2, for 5 category-item pairs overall. Build a regular PivotTable with Category in Rows and the helper column in Values, summarized by Sum. This uses ordinary Excel functions and works as a practical alternative on platforms where Distinct Count is unavailable. Exclude blank categories or items first if you do not want a blank pair to be counted.
The helper column gives unexpected results. Check that you copied the formula down without changing its anchored starting ranges, and that the category and item references point to the correct columns. The formula marks the first appearance of each exact pair as it scans downward. If values that look identical are actually different because of extra spaces or inconsistent text, clean those source cells before counting.
Frequently Asked Questions
Is Distinct Count the same as counting unique values?
In PivotTable terminology, Distinct Count counts each different value once, even if it repeats. Some people use unique to mean values that appear exactly once. If you mean that stricter definition, Distinct Count is not the right calculation.
Can I count unique values in a PivotTable without the Data Model?
Not with the built-in Distinct Count summary. Create the PivotTable with Add this data to the Data Model selected, or use a helper column that flags the first occurrence of each group-and-item pair and summarize that helper by Sum.
Does the grand total equal the sum of the category counts?
Not always. The grand total counts different values across the entire filtered source. A value appearing in more than one category is counted once in each category row but only once in the overall grand total.
Will a Distinct Count include blank cells?
A blank can be treated as a distinct value by the Data Model, so the result may include one blank. Filter blank records out or clean the source if blanks should not count, and verify the result against a small sample.
Can Excel for Mac or Excel for the web create this PivotTable?
Feature availability differs from Windows desktop Excel, and the Data Model Distinct Count option may not be available in the same way. If you cannot find the option, use the helper-column workaround or create the Data Model PivotTable in a supported Windows desktop version.
What happens when I add more rows to the source?
Refresh the PivotTable after updating the data. If the source was a fixed range, expand it through PivotTable Analyze > Change Data Source. An Excel table is often easier to maintain because it can expand to include new rows.
Related
- Protect a Sheet in Excel From Viewing: What Actually Works
- How to Lock Cells in Excel to Prevent Editing on a Sheet
- Lock Cells in Excel for the Web: A Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
- AVERAGEIF Across Sheets in Excel: Formulas & ExamplesExcel’s AVERAGEIF function cannot directly average matching values across multiple worksheets in one formula. Assuming Jan and Feb…
- Excel Lookup Function Examples: XLOOKUP, VLOOKUP and MoreExcel lookup functions find a value in one range and return a related value from another. For example,…
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell…
- Excel Data Table Cheat Sheet: What-If Analysis GuideAn Excel Data Table is a What-If Analysis tool that recalculates a formula for different values in one…
- Excel How to Filter Columns: Steps and TipsTo filter columns in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.