To list unique values in Excel, put the field in a PivotTable’s Rows area; repeated labels appear once. To count distinct values, create the PivotTable with Add this data to the Data Model selected, then use Value Field Settings > Summarize Values By > Distinct Count.
What unique values mean in a PivotTable
A PivotTable can show unique labels or calculate a distinct count, but those are different tasks. For example, imagine a source column containing Hana, Luis, Hana, and Luis. Putting that column in Rows displays Hana and Luis once each. Putting it in Values and choosing Count returns 4, because there are four records. Choosing Distinct Count returns 2, because there are two different names.
In spreadsheet discussions, “unique” sometimes means a value that occurs exactly once. Excel’s PivotTable Distinct Count does not mean that: it counts each different value once, even if that value appears repeatedly. In the example, a value occurring exactly once would be neither name. If you specifically need values that occur only once, use a different method, such as counting occurrences in a helper column and filtering for 1.
A PivotTable is useful because it summarizes a source range without changing or deleting its records. It can group labels, apply filters, and recalculate summaries after a refresh. The source remains intact, so a PivotTable that displays each category once is not the same as permanently removing duplicate rows from the source.
Count distinct values with the Data Model
In Excel for Microsoft 365 on Windows, use these steps to count different items in a field:
- Select a cell in the source data. A structured Excel Table is a good source because it can expand as you add rows.
- Choose Insert > PivotTable. In the Create PivotTable dialog, confirm the table or range and choose where to place the report.
- Select Add this data to the Data Model before creating the PivotTable. This is the key requirement for the built-in Distinct Count summary.
- In the PivotTable Fields pane, drag the field to count into Values. If you also want a breakdown, drag a grouping field, such as Region or Department, into Rows.
- Open the field’s drop-down in the Values area and choose Value Field Settings. Under Summarize Values By, select Distinct Count, then select OK.
For example, with one row per order line, put Customer in Values and Region in Rows. Distinct Count then reports the number of different customers represented in each region. The grand total is the number of different customers across the whole filtered source, not necessarily the sum of the regional figures.
Show a unique list without counting it
If your goal is simply to produce a compact list of categories, a Data Model is not required. Create a regular PivotTable, drag the desired field into Rows, and leave Values empty. Excel displays each row label once, even if the source contains many records with that label. You can add other fields to Columns or Filters if you want to organize or narrow the report.
For instance, if the source has hundreds of sales records but only six product names, placing Product in Rows gives a list of those six names. A PivotTable may also show a “(blank)” item when the source contains empty cells. You can hide that item with the filter on the row labels, but doing so changes the displayed report, not the underlying source.
A row-label list is a useful quick inventory, but it has PivotTable formatting and refresh behavior. If you need a simple formula-driven list that spills into nearby cells, and you have Excel 2021 or Microsoft 365, use UNIQUE. With product names in A2:A100, enter this in an empty cell:
=SORT(UNIQUE(A2:A100))
The formula returns a sorted list of distinct entries from that range and updates when the referenced cells change. It can return a blank entry if the source range includes blanks. Unlike a PivotTable, it does not provide built-in subtotals, slicers, or a Values area. Excel for the web also supports many dynamic-array formulas, but menu and PivotTable capabilities can differ from desktop Excel.
Count unique items within categories
When you put a category in Rows and an item in Values, the summary is calculated separately for each category. For example, Region in Rows and Customer in Values with Distinct Count selected answers “How many different customers bought in each region?” This is different from counting all sales records in each region.
Distinct counts can overlap between groups. If the same customer bought in both North and South, that customer appears in both regional counts. Therefore, adding the regional distinct counts can produce a number larger than the grand total. The grand total calculates distinct customers across the entire set of records; it does not add the group results together.
Filters and slicers affect which source records are included in the report, so a filtered distinct count can change. This is useful for questions such as “How many customers bought this product in 2025?” Make sure the filter field and date grouping represent the intended period, especially when a customer has records in more than one period.
Why the Data Model option matters
Excel’s ordinary PivotTable summaries include choices such as Sum, Count, and Average. Distinct Count is a separate option that relies on the Data Model, Excel’s in-workbook data storage and relationship feature. A PivotTable created without adding its source to the Data Model generally will not offer Distinct Count in Value Field Settings. Changing a regular PivotTable’s layout later does not switch on that requirement; create a new one with the Data Model option selected.
The Data Model is also useful when a report combines related tables, but it is not necessary just to display unique row labels. For a straightforward list, use Rows. For a distinct total, use the model-based summary. The extra setup is worthwhile when the report needs an accurate distinct count by category or needs to respond to report filters.
These instructions are for Excel for Microsoft 365 on Windows. Data Model authoring and Power Pivot features are more limited or unavailable in Excel for Mac and Excel for the web, and the exact capabilities can depend on the edition. If you do not see Add this data to the Data Model or Distinct Count, try Excel desktop for Windows, or use a formula or another supported tool to calculate the result.
Refresh the PivotTable when the source changes
PivotTables do not always update immediately when you edit or append source records. To recalculate a report, select it and choose PivotTable Analyze > Refresh. To refresh all PivotTables and connections in the workbook, use Data > Refresh All. On a Mac, ribbon labels or available Data Model commands may differ, so use the corresponding Refresh command in the Excel interface.
If you added rows outside the original source range, refreshing may not include them. Converting the source range to an Excel Table before building the PivotTable helps: select the data and choose Insert > Table, confirm whether the table has headers, then build the report from that table. New rows added directly beneath the table are incorporated into its expanding range; refresh the PivotTable afterward.
For a report that must always reflect a changing dataset, check both the source and the refresh state. A correct distinct-count setting cannot include records that are outside the PivotTable’s source. Similarly, a formula such as UNIQUE only examines the range written into the formula; extend that range or refer to a suitable table column when the data grows.
Common problems and checks
- Distinct Count is missing. The PivotTable may not have been created with Add this data to the Data Model selected. Create a replacement PivotTable with that option enabled, then set its value field to Distinct Count.
- The result is larger than expected. Check for extra spaces, inconsistent spellings, IDs stored in different formats, or blank entries. “North”, “North ”, and “NORTH” can create confusing results depending on how the source values are represented and processed. Clean and standardize source data when labels should mean the same thing.
- The result is smaller than expected. Review report filters, slicers, hidden items, and the PivotTable’s source range. A filtered-out record cannot contribute to the count.
- The grand total does not equal the group totals added together. That is normal when the same item belongs to more than one group. Distinct counts overlap; the total counts different items across the whole included dataset.
- There is a “(blank)” row or an unexpected blank count. Inspect empty cells in the source and decide whether blanks should be included. Filter the blank label from the report if appropriate, and document that choice if the number will be shared.
For reliable results, decide what constitutes one item before building the report. A customer name may not be a safe identifier if two customers share a name; a customer ID is usually a clearer field to count. Likewise, decide whether a blank is a meaningful category or missing data. A PivotTable follows the values it receives; it cannot infer the business meaning behind them.
PivotTable or formula: which should you choose?
Choose a PivotTable when you want an interactive summary, counts by category, filters, or a report that other users can rearrange. Choose a row-label list when you need a quick view of the distinct labels. Choose UNIQUE when you want a live list in worksheet cells that can feed another formula or be displayed without a PivotTable layout. For a distinct count by group, the Data Model PivotTable is often the most direct built-in option.
These methods answer different questions. A list shows which values exist; Count shows how many source records exist; Distinct Count shows how many different values exist. If you need values that occur exactly once, neither a standard row-label list nor Distinct Count identifies them by itself. State the question precisely before choosing a method, then check the result against a small sample of source records.
Frequently Asked Questions
Can a PivotTable show each value only once?
Yes. Drag the field into Rows. Repeated source values are represented by one row label. This displays a unique list but does not remove duplicate records from the source.
How do I count unique values in an Excel PivotTable?
Create the PivotTable with Add this data to the Data Model selected. Put the field in Values, open Value Field Settings, and choose Distinct Count under Summarize Values By.
Why is Distinct Count not available?
The PivotTable was likely created without the Data Model, or your Excel edition does not expose the required Data Model feature. In Windows desktop Excel, create a new PivotTable and select Add this data to the Data Model.
Does Distinct Count count duplicates?
It counts each different value once, regardless of how many times that value occurs. It is not a count of values that appear exactly one time.
Can I count unique values without a PivotTable?
Yes. In Excel 2021 or Microsoft 365, UNIQUE can return a distinct list, and a formula can be used to derive counts. For a simple list, with source values in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart: Group Dates by Month in ExcelYes. In Excel for Microsoft 365 on Windows, group dates for a PivotChart through its underlying PivotTable: right-click…
- Excel Line Chart With X and Y Values: Setup and TipsTo make an Excel line chart, put your x values in one column and the matching y values…
- Pivot Chart Colors by Value in Excel: Setup GuideExcel PivotCharts do not automatically change each data point’s color according to its value, and worksheet conditional formatting…
- Add a Bar Chart in Excel: Steps and TipsTo add a bar chart in Excel for Microsoft 365 on Windows, select the category labels and numeric…
- Excel Trendline: How to Ignore Zero ValuesExcel has no built-in setting to make a trendline ignore zero values, but you can exclude them from…
- Build an Org Chart From Excel Data: Steps and OptionsYes. You can build an org chart in Excel by choosing Insert > SmartArt > Hierarchy > Organization…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.