Yes. To show each distinct item once in an Excel PivotTable, drag its field into the Rows area; Excel groups matching labels together automatically. If you mean counting distinct items, or displaying only items that occur exactly once, those are different tasks and need different settings.
What a PivotTable means by unique values
A PivotTable is designed to summarize records, so it separates fields into categories and calculations. When you place a text or number field in Rows, Excel displays each label once and groups the source records with that label beneath the summary. For example, if a source list contains Alex, Priya, Alex, and Sam, putting Name in Rows displays Alex, Priya, and Sam, not four separate entries.
This is a distinct list of labels, not a cleaned or edited source column. The original data remains unchanged, and each occurrence still contributes to other calculations in the PivotTable. If you also put Sales in Values, for instance, the sales for all records grouped under Alex are normally added together.
Be clear about which meaning of unique you need:
- Distinct values: show each different label once, even if it occurs many times. A field in Rows does this.
- Distinct count: show the number of different labels in a group. This requires a Data Model PivotTable for Excel’s built-in Distinct Count option.
- Values occurring exactly once: show only labels that appear in one source record. A normal Rows field does not do this by itself.
Show each value once in a PivotTable
In Excel for Microsoft 365 on Windows, first make sure your source data has a header row and no completely blank rows or columns splitting the list. Click in the data, then choose Insert > PivotTable. Check that the proposed table or range is right, choose where to place the PivotTable, and select OK. Excel creates an empty report and opens the PivotTable Fields pane.
- Find the field whose distinct labels you want to display, such as Product, City, or Customer.
- Drag that field to Rows in the PivotTable Fields pane. You can also select its checkbox; Excel usually places a text field in Rows automatically.
- Look at the resulting row labels. Each matching item appears once, with the underlying records grouped into that category.
- For a clean list, remove any other fields from Rows and Columns that would split the categories into combinations.
For example, with Product values Tea, Coffee, Tea, and Cocoa, a PivotTable with Product in Rows shows Cocoa, Coffee, and Tea. If you add Region beneath Product in Rows, the report instead displays product-and-region combinations. Tea may appear under more than one region because those are separate groups. That is not a failure to remove duplicates; it is the effect of adding another grouping field.
To create a distinct list only, you do not need to put the field in Values. Doing so changes the report into a calculation, such as Count of Product, rather than a simple list of labels.
When to use Values and Distinct Count
The Values area summarizes data. By default, Excel commonly sums numeric fields and counts text fields, although the available summary may depend on the field’s contents. This is why dropping a column into Values does not produce a list of unique entries: it produces a number for each category.
If the goal is to report how many different customers, products, or codes appear in each group, use Distinct Count. In a new PivotTable on Windows, select the option to add the source data to the Data Model in the Create PivotTable dialog, then build the report. Add the category field to Rows and the field you want to count to Values. Open the Values field menu, choose Value Field Settings, and select Distinct Count if it is available.
For example, suppose a sales table has Region and CustomerID columns, and the same customer can place several orders. Put Region in Rows and CustomerID in Values, summarized by Distinct Count. The result counts each customer once within each region, even if that customer has multiple order rows. A regular Count would count the order records instead, so it could be larger.
Distinct Count is tied to the Data Model; it is not the same as the ordinary Count summary. Excel for Mac does not provide the same Data Model PivotTable workflow as Excel for Windows, and Excel for the web may not expose all Data Model features. If you do not see Distinct Count, check that the PivotTable was created with Add this data to the Data Model selected, or use a formula-based alternative.
Distinct values versus values that occur once
A frequent source of confusion is the word unique. In everyday use, it can mean a value that is different from all the others, or a value that appears only once. PivotTable rows use the first meaning: repeated labels are consolidated, but the label remains in the report.
To find values that occur in exactly one source row, add the category field to Rows and add a consistently populated record identifier to Values. Set the Values summary to Count, then use the row-label dropdown and choose Value Filters > Equals. Set the condition to the count field equal to 1. Use an ID or other nonblank field for the count; counting a field that contains blanks can undercount records. This filter keeps categories represented by one counted record, rather than simply showing each category once.
For a compact list without a PivotTable, Microsoft 365 and Excel 2021 also have the UNIQUE function. With product names in A2:A100, the formula =UNIQUE(A2:A100) spills the distinct names into the cells below the formula. If the range contains Tea, Coffee, Tea, and Cocoa, the result contains Tea, Coffee, and Cocoa once each. To return only items that occur exactly once, use =UNIQUE(A2:A100,,TRUE); with that same example, it returns Coffee and Cocoa. The formula’s output updates when the source values change, while a PivotTable usually needs a refresh.
Adjust labels, blanks, and dates
A PivotTable shows values as it interprets them in the source, so apparent duplicates may be caused by inconsistent data. For example, North and North followed by a space can look identical on screen but be treated as different text. Differences in punctuation or spelling, such as St. Louis and St Louis, also create separate labels. Clean the source column if those should represent the same category.
Blank source cells may appear as a (blank) row label. If blank is not useful in the report, open the field’s row-label filter and clear the blank item. This hides the blank category from the PivotTable; it does not fill or remove blank cells in the source data.
Date fields can be grouped automatically into larger periods, such as months or quarters, rather than displayed as individual dates. If you need individual date labels, right-click a date in the PivotTable and choose Ungroup. If you want months, keeping the grouped field may be more useful. Grouping changes the display categories; it does not make the source dates distinct or alter them.
Refresh the report and check its source
A PivotTable is a summary based on its source data, not a live list that recalculates after every edit. After changing the source, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh all PivotTables in the workbook, use Data > Refresh All.
Refreshing updates records already included in the PivotTable’s source range. If you added new rows outside a fixed source range, those rows may not be included. Converting the source to an Excel table before creating the PivotTable can make the source easier to extend: select the data and choose Insert > Table, confirm that the table has headers, and create the PivotTable from that table. Then refresh after adding records.
If an item deleted from the source still appears in the PivotTable, refresh first. PivotTable options can also retain old field items in the cache. On Windows, open PivotTable Analyze > Options, go to the Data tab, and set Number of items to retain per field to None; then refresh. The available controls can differ by Excel edition.
PivotTable or another way to get a distinct list?
Choose the method based on what you need to do with the result. A PivotTable is useful when the unique labels should organize a report, support filtering, or summarize other fields. Its groupings and totals can be rearranged without changing the source column.
- Use a PivotTable Rows field for a reusable report with one row per distinct category.
- Use Distinct Count when the result should be a number of different items, such as customers per region, and the Data Model is available.
- Use UNIQUE when you want a formula-driven list that spills into worksheet cells. It is convenient for downstream formulas, but requires Excel 2021 or Microsoft 365 and does not work in older perpetual versions.
- Use Remove Duplicates when you intend to change a selected range and permanently delete repeated rows from that range. Find it under Data > Remove Duplicates. Make a copy first if the source records need to be preserved; this command is not a PivotTable display option.
In Excel for the web, the UNIQUE function is available for supported Microsoft 365 accounts, and PivotTables can be used, though some advanced PivotTable features may differ from the Windows desktop app. On Mac, a Rows field or UNIQUE formula is a practical route for distinct labels. If a feature or field setting is missing, verify the Excel version and whether the report uses the Data Model before changing the source.
Frequently Asked Questions
Why does my PivotTable still show repeated values?
Check whether another field in Rows or Columns is splitting the labels into combinations, and check for source values that differ by spaces, spelling, or punctuation. Refresh after correcting the source. The same item can also appear under different parent categories when the report includes multiple row fields.
Does putting a field in Rows delete duplicates from my data?
No. It groups matching items in the PivotTable display but leaves every source record intact. If you need to remove repeated records from the data itself, use Data > Remove Duplicates on a copy or use an appropriate data-cleaning workflow.
How do I count unique customers in a PivotTable?
Create the PivotTable with Add this data to the Data Model selected, put the desired grouping field in Rows, and set the customer field in Values to Distinct Count. If Distinct Count is unavailable, use a supported version and confirm the PivotTable uses the Data Model.
How do I show only values that appear once?
Count a nonblank record identifier for each row-label item, then apply a Value Filter equal to 1. Alternatively, with the values in A2:A100 in Excel 2021 or Microsoft 365, =UNIQUE(A2:A100,,TRUE) returns values appearing exactly once.
Why does a new source item not appear in my PivotTable?
Refresh the PivotTable and confirm that its source range includes the new record. If the source was a fixed range, expand it or use an Excel table as the source so appended rows can be included on refresh.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart Line Graphs in Excel: How They WorkYes. In Excel, a PivotChart line graph displays values from a PivotTable as a line chart, making it…
- Excel Pivot Chart Axis Labels: How to Rotate Them EasilyTo rotate category-axis labels in an Excel PivotChart, select the labels, right-click and choose Format Axis, then open…
- Excel Pivot Chart: Count Unique ValuesYes. An Excel PivotChart can display a count of unique values, but its PivotTable must use the Data…
- Change the X-Axis on an Excel HistogramTo change a histogram’s X-axis in Excel for Microsoft 365 on Windows, right-click the horizontal axis, choose Format…
- Pivot Columns in Power Query: How to Reshape DataTo pivot columns in Power Query, select the column whose distinct values should become new headers, then choose…
- How to Make a Graph in Google Sheets on a ComputerTo make a graph in Google Sheets on a computer, arrange your information in labeled rows or columns,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.