Excel PivotChart: How to Count Unique Values

Yes. In Excel for Microsoft 365 on Windows, you can count unique values in a PivotChart by building its PivotTable from the Data Model and choosing Distinct Count for the value field. The key step is checking Add this data to the Data Model when you create the PivotTable; a standard PivotTable’s ordinary Count option does not count unique values.

What a unique count means in a PivotChart

A unique count, also called a distinct count, counts each different value once within the group being summarized. If a sales list contains customer IDs C104, C104, C208, and C315 for one region, the distinct customer count for that region is three, not four. The duplicates still appear in the underlying data; the summary simply counts each ID once.

This is useful when a chart needs to show measures such as unique customers by region, distinct products sold by month, or different employees assigned to each project. It answers a different question from a transaction count. For example, counting sales rows tells you how many transactions occurred, while counting distinct customer IDs tells you how many different customers made them.

Distinct Count applies to the field placed in the PivotTable’s Values area. It does not automatically identify duplicate records across every column, nor does it count unique combinations of fields. If your definition of “unique” is a combination such as customer plus product, you need a combined key or another method that represents that combination.

Create a PivotTable that supports Distinct Count

Start with a rectangular dataset that has one header row and consistent columns. For a customer chart, you might have fields such as Date, Region, CustomerID, and Sales. An Excel Table is a convenient source because it expands as you add rows. On Windows, select the data and press Ctrl+T, then confirm that the table has headers. On Mac, the equivalent shortcut is usually Command+T.

Select a cell in the source table or data range, then choose Insert > PivotTable.
In the PivotTable dialog, choose where to place the PivotTable. Select Add this data to the Data Model before confirming.
In the PivotTable Fields pane, drag the category you want to compare, such as Region, into Rows.
Drag the identifier to count, such as CustomerID, into Values.
Open the value field’s menu and choose Value Field Settings. Under Summarize Values By, select Distinct Count, then choose OK.

If the checkbox for adding the source to the Data Model was missed, the Distinct Count choice may not be available. In that case, create a new PivotTable and select the option during setup. The precise appearance of the dialog can vary slightly with Excel updates, but the essential requirement is a PivotTable based on the Data Model.

Turn the distinct-count summary into a PivotChart

Once the PivotTable displays the distinct count, click inside it and choose PivotTable Analyze > PivotChart. Select a chart type that suits the comparison: a clustered column chart works well for comparing categories, while a line chart can show change over time when dates are grouped appropriately. The chart uses the PivotTable’s fields and filters, so changing a PivotTable filter also changes the chart.

You can also create the chart as part of the initial workflow by selecting the PivotTable and using the PivotChart command on the ribbon. If you want to compare unique customers by region and month, for example, put Region in Rows, Month in Columns, and CustomerID in Values with Distinct Count selected. The chart can then display a series for each month or a category for each region, depending on its layout and chart type.

Think about what the visual should communicate before adding extra fields. Too many categories or series can make a PivotChart difficult to read. A useful starting point is one category field, one distinct-count value, and—if needed—one date or series field. You can add slicers or use PivotTable filters to let readers focus on a subset of the data.

Understand totals and overlapping groups

Distinct counts are not generally additive. Suppose the North region has customers A, B, and C, while the South region has B, C, and D. Each region has three distinct customers, but the overall distinct customer count is four, not six, because B and C appear in both regions. The grand total is recalculated across the complete set; it is not necessarily the sum of the visible category values.

This distinction matters when interpreting chart labels and PivotTable totals. A chart showing three customers in each of two regions does not mean there are six different customers overall. If groups can share the same IDs, explain that the bars represent unique values within each group. If the goal is to show non-overlapping membership, the data must define mutually exclusive groups or use a different measure.

Also check what the identifier represents. A customer name may not be a reliable key if different people share a name or if one person’s name is entered in multiple ways. A stable CustomerID is usually a better field to count. Similarly, counting distinct product descriptions may produce a different result from counting distinct product codes.

Blanks, inconsistent IDs, and refreshes

Data quality affects the count. A blank identifier is not a useful customer or product, so inspect how blanks are handled in your source and result. Data Model distinct-count behavior can treat a blank as a distinct value. If blanks should not count, filter them out or clean the source before relying on the chart. Check the displayed result against a small sample, especially when blank IDs are present.

Leading zeros: Codes such as 0017 and 17 can become indistinguishable if imported or converted as numbers. Store identifiers in a consistent form, often as text when the zeros are meaningful.
Extra spaces: Entries that look the same may contain leading or trailing spaces. Clean the source values so the count reflects the intended identity.
Mixed formats: A number stored as text and the same digits stored as a number can behave unexpectedly during data preparation. Standardize the identifier column.
New rows: If the source is an Excel Table, added rows are easier to include in later refreshes. After changing the source, use Data > Refresh All to update the PivotTable and chart.

Refreshing does not fix inconsistent data; it recalculates the summary from the source values. When a result seems wrong, check the source range, refresh status, blank entries, and whether the field being counted is truly a stable identifier.

Use a formula when you do not need a PivotChart

If you need a unique count in an ordinary summary table rather than a PivotTable, Excel for Microsoft 365 and Excel 2021 can use UNIQUE and FILTER. For example, assume customer IDs are in A2:A100, regions are in B2:B100, and the region to summarize is in D2. This formula counts distinct, nonblank customer IDs for the region in D2:

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$100,($B$2:$B$100=D2)*($A$2:$A$100<>"")))),0)

If North has the IDs C104, C104, C208, and a blank, with all four rows assigned to North, the formula returns 2: C104 and C208. Copy it down beside a list of region names to build a summary, then create a regular chart from that summary. This formula-driven chart is not a PivotChart, but it can be a practical alternative when you do not need PivotTable field controls.

UNIQUE, FILTER, and the dynamic-array behavior used here are available in Microsoft 365 and Excel 2021 or later. In Excel for the web, these worksheet functions may be available, but creating or managing a Data Model PivotTable is not equivalent to the Windows desktop workflow. Mac feature availability also differs by Excel version; if the Data Model option is unavailable in your installation, use a formula summary or prepare the data another way.

Choose between Distinct Count and other approaches

Use the Data Model’s Distinct Count when you want a PivotTable and PivotChart that summarize unique values by categories, respond to PivotTable filters, and can be refreshed from a source table. It is usually the most direct choice for a repeatable report in Windows Excel.

Use a formula summary when you need a small, transparent calculation or when your Excel environment does not offer the Data Model workflow. Use a regular Count when the question is how many nonblank records are in each group, including repeated IDs. These methods answer different questions, so choose based on the meaning of the measure rather than the chart style.

For unique combinations—such as distinct customer-and-product pairs—counting CustomerID alone is not enough. Create a key that represents the combination or shape the source data so each combination is identifiable, then count that key. Take care when building text keys: a separator can appear in the original values and create collisions. If this is a recurring analysis, a properly structured source or Data Model design is safer than an improvised concatenation.

Frequently Asked Questions

Why can’t I see Distinct Count in Value Field Settings?
Most often, the PivotTable was created without selecting Add this data to the Data Model. Create a new PivotTable from the source and select that option, then add the field to Values and reopen Value Field Settings.

Does Count in a PivotTable count unique values?
No. Count counts records with a value in the field, so repeated IDs are counted repeatedly. Choose Distinct Count for unique IDs; use Count when each row or transaction should count separately.

Can a PivotChart show unique customers by month?
Yes. Put the date or month field in the PivotTable’s Rows or Columns area and CustomerID in Values, summarized by Distinct Count. If dates are grouped, confirm that the groups represent the time periods you intend to compare.

Why doesn’t the grand total equal the sum of the bars?
Customers can appear in more than one category. Excel recalculates the distinct count for the total population, so shared IDs are counted once in the grand total even though they appear in multiple category counts.

Can I make a unique-count PivotChart in Excel for Mac or the web?
Data Model authoring and PivotTable features differ across platforms and versions. The Windows Microsoft 365 steps above are the most direct route. If your Mac or web version does not offer the required Data Model option, make a formula-based summary and chart it instead.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.