Excel Pivot Table Filter by Unique Values

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel, a PivotTable field already lists each distinct label once, even when that value appears in many source rows; use the field’s filter to choose which labels to display. If by “unique” you mean values that occur exactly once in the source, apply a Value Filter based on a count, or use a different method for a distinct list.

What “unique values” means in a PivotTable

The phrase can describe two different tasks, and the distinction matters. A PivotTable groups identical items together. If the source has the department name Sales in 40 rows, Sales normally appears just once in the Department field’s row or column labels. The PivotTable can then summarize those 40 rows, for example by totaling sales or counting records. This is the usual meaning of unique values in a PivotTable: one displayed label for each distinct field item.

Sometimes, however, “unique” means a value that occurs exactly once in the source data. That is a frequency test, not simply a distinct-label list. A PivotTable does not have a universal Show only unique source values switch. You can use its count and value filters to find items that occur once, provided the layout and the count field are set up appropriately.

A third, related request is to count distinct entries in a group. For example, someone may want the number of different customers per region, even if one customer has multiple transactions. That requires a distinct count, which is different again from counting all rows.

Filter a PivotTable to selected distinct labels

For the common case—showing only selected labels—put the field you want to filter in the Rows or Columns area of the PivotTable Fields pane. Excel combines repeated source labels into one item in that field. Then use the field’s dropdown to include or exclude items:

  1. Click a cell in the PivotTable so its PivotTable tabs and field list are available.
  2. Find the field in the PivotTable itself, such as Row Labels, or use the dropdown beside the specific row or column field name.
  3. Open the dropdown. Clear Select All, select the labels to keep, and choose OK. Use the search box in the menu when there are many items.

In a compact layout, the dropdown may be labeled Row Labels or Column Labels rather than the source field name. The field list can also be used to move a field between Rows, Columns, Filters, and Values. Putting a field in Filters creates a report filter above the PivotTable; it still filters items, but is more convenient when readers need to change the selection without opening a row-label menu.

These menus filter existing PivotTable items; they do not remove records from the source table. A filtered total reflects the visible items, while the underlying source remains unchanged.

Use search, label filters, and value filters

The dropdown’s checklist works well for a short set of categories. For larger fields, search for an item by typing in the filter menu, or use a label filter for text-based conditions. For example, a Product field can be limited to labels that begin with a particular prefix. A date field may offer date-specific filtering choices. The exact options depend on the field’s data type and the PivotTable layout.

Value Filters are useful when the decision is based on a summarized number rather than the label itself. For instance, you can keep products whose summed revenue is above a threshold. Open the row or column field dropdown, point to Value Filters, choose a condition such as Greater Than, and select the value field and comparison value in the dialog. The filter is evaluated using the current PivotTable’s grouping and summary, so changing other filters can change which items qualify.

Label Filters and Value Filters answer different questions. A label filter tests the text or date label—for example, a name begins with A. A value filter tests an aggregate—for example, the count of records for an item equals 1. If the intended test is about occurrence frequency, make sure the Values area contains a count of a field that is populated on every source record.

Show items that occur exactly once

To find labels that appear in only one source record, count records for each label and filter that count to 1. For example, imagine a source table with one row per order line and fields named Product and Order ID. Put Product in Rows and Order ID in Values. If Order ID is a text field, Excel will generally summarize it as Count; if it summarizes as Sum or another unsuitable operation, open Value Field Settings and choose Count. A nonblank record identifier is a good counting field because each source row contributes to the count.

Next, open the dropdown for the Product row field, choose Value Filters, then Equals. In the dialog, choose the count of Order ID and set the comparison to 1. The PivotTable will display products with one counted record in the current filtered data. If you count a field that has blanks, blank records will not contribute to Count, so the result may not represent the number of source rows. Count a reliably populated identifier or another field present in every record.

This result is only as granular as the row fields in the PivotTable. If Product is grouped by Product and Region, the count is per product-region combination; the same product can appear once in several regions. For a count across all regions, remove Region from Rows or Columns, or otherwise arrange the fields so each product is evaluated at the intended level. Also remember that report filters and slicers affect the count.

Count distinct entries instead of all records

To count different customers per region, rather than transactions per region, use the PivotTable’s Distinct Count summary when available. During PivotTable creation in Microsoft 365 for Windows, select the source data, choose Insert > PivotTable, and select Add this data to the Data Model in the Create PivotTable dialog. Place Region in Rows and Customer ID in Values. Open Value Field Settings for Customer ID and choose Distinct Count under Summarize Values By.

Distinct Count treats repeated customer IDs within a region as one customer for that region. For example, if a region has 12 transaction rows but only 8 different customer IDs, its ordinary Count can show 12 while Distinct Count shows 8. A customer who appears in two regions is counted once in each region, because the calculation is made within each group.

Distinct Count is not the same as filtering the source to records that occur once. It counts different values in each PivotTable group; it does not identify which individual source rows are duplicates. If the Add this data to the Data Model option or Distinct Count summary is unavailable in your Excel edition, a conventional PivotTable count or a separate unique-list approach may be more suitable. Data Model features and PivotTable options can differ in Excel for Mac and Excel for the web, so check the commands available in that edition.

Why a filtered item may be missing or return unexpectedly

PivotTables use a cache of source data, so edits to the source may not immediately change the filter menu or results. After adding, correcting, or deleting source records, click the PivotTable and choose PivotTable Analyze > Refresh. If the source table has grown, confirm that the PivotTable source includes the new rows. Using an Excel Table as the source helps because its range expands as rows are added, although you still need to refresh the PivotTable.

Also check for values that look identical but are not identical in the source. Trailing spaces, inconsistent spelling, different date values, and numbers stored as text can create separate PivotTable items. For example, “North” and “North ” may display similarly but be treated as different labels. Correct or standardize the source values, then refresh.

If a field filter behaves as though it is counting the wrong things, inspect the Values area and its summary. Sum is appropriate for numeric amounts; Count is appropriate for counting nonblank entries; Distinct Count counts different entries when supported. A count of a partially blank field can understate the number of source records. Finally, check other filters and slicers: an item excluded by another selection may not appear in the current view.

When a PivotTable is not the best tool

A PivotTable is useful when you want an interactive summary, grouped totals, counts, and filters that can be changed without rewriting the source. It is especially handy when distinct labels need to be grouped with totals or other measures. But if the goal is just a clean, live list of distinct source values in worksheet cells, a formula may be simpler. In Microsoft 365 or Excel 2021, the UNIQUE function can return a distinct list from a range; it is a dynamic-array formula rather than a PivotTable filter.

Another option is Remove Duplicates on a copy of the source when a one-time cleaned list is needed. It changes the selected data, so do not use it on the only copy of records you need to preserve. Power Query can also group or deduplicate rows as part of a repeatable import-and-clean process. Choose based on the job: use a PivotTable for an interactive summary, a formula for a live distinct list, or a transformation workflow when cleaning and reshaping data is part of the task.

Frequently Asked Questions

Does a PivotTable automatically remove duplicates?
It groups identical field items into one displayed label, but it does not delete duplicate source rows. Those rows still contribute to totals and counts unless a filter excludes them.

How do I show only values that appear once?
Put the field in Rows, put a reliably populated record identifier in Values as Count, then use the row field’s Value Filters > Equals option to keep a count of 1. The result applies to the current grouping and filter selections.

Can I filter a PivotTable by a distinct count?
You can summarize a field using Distinct Count in a Data Model PivotTable where that option is supported. Distinct Count is a summary measure; it is not a command to display only individual source records that are unique.

Why do duplicate-looking labels appear twice?
The source entries may differ by a trailing space, spelling, data type, or other invisible difference. Standardize the source values and refresh the PivotTable to update its item list.

Why does my new value not appear in the filter list?
Refresh the PivotTable and confirm its source range includes the new row. If the PivotTable is based on a fixed range, expand or change that source; an Excel Table can make the range easier to maintain.

Can I filter unique values without a PivotTable?
Yes. Excel 2021 and Microsoft 365 include the UNIQUE worksheet function for returning a distinct list. It is a good choice when you need a list of labels rather than a grouped summary with PivotTable filters.

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.