Distinct Count in Excel PivotTables: How to Enable It

How-to basics · Updated November 20, 2026

To enable distinct count in an Excel PivotTable, create the PivotTable with Add this data to the Data Model selected, then put the field you want to count in Values and choose Distinct Count in Value Field Settings. This counts each different value once within the relevant PivotTable group, rather than counting every row. The Data Model option is the key requirement; it is available when creating a PivotTable in Excel for Microsoft 365 on Windows.

What You’ll Need

  • Excel for Microsoft 365 on Windows. The instructions below use the Windows desktop app.
  • A clean source range with a single header row and no merged cells. Convert it to an Excel table first if you expect to add rows later.
  • Permission to create a new PivotTable. A PivotTable made without the Data Model generally cannot be changed into a distinct-count PivotTable just by changing its value summary.
  • A saved copy of the workbook if it contains important data. Adding a Data Model can increase workbook size, especially for large or complex sources.

Step-by-Step

  1. Check the source data. Each column should have one clear, unique header, such as Order ID, Customer, and Region. Remove completely blank rows and columns inside the data, and make sure each value you intend to count is in one column. For example, if you want unique customers, every transaction row should contain a customer identifier in the Customer column. A consistent ID is usually safer than a name: two people can share a name, and one person’s name can be entered with different spelling or spacing.
  2. Make the source an Excel table if it will grow. Click inside the data and choose Insert > Table. Confirm the range and select My table has headers if it is not already selected, then click OK. A table expands when you add records below it, which makes it easier to refresh the PivotTable with new rows. If the source is already a table, keep using it rather than making a second one.
  3. Start a new PivotTable. Click in the source table or range, then choose Insert > PivotTable. In the Create PivotTable dialog, confirm that the correct table or range is shown. Choose where to put the PivotTable, commonly New Worksheet. If you select a range directly rather than a table, verify that it includes every source row and column.
  4. Add the source to the Data Model. In the Create PivotTable dialog, select Add this data to the Data Model, then click OK. Do not skip this checkbox: it is what makes the Distinct Count summary available. If you cannot see the checkbox, confirm that you are using the Windows desktop version of Excel and that you are creating a new PivotTable from a supported source, rather than editing an existing ordinary PivotTable.
  5. Build the layout. In the PivotTable Fields pane, drag the category you want to break results down by into Rows. For example, drag Region there to see a separate result for each region. Drag the field whose unique values you want to count into Values. For a customer count, that might be Customer ID. Excel may initially summarize it as Count or Sum; that is fine for now because you will change the summary in the next step.
  6. Choose Distinct Count. In the Values area, select the field’s drop-down arrow and choose Value Field Settings. In the dialog, open Summarize Values By, select Distinct Count, and click OK. If the field name is hard to read in the PivotTable, use the Custom Name box in the same dialog to give the result a clear label, such as Unique Customers.
  7. Check the result and refresh when needed. Compare a small group in the PivotTable against its source rows by listing the different IDs in that group. If you add or change source records, right-click the PivotTable and choose Refresh, or use Data > Refresh All. A distinct count is recalculated from the refreshed source; it does not update immediately just because a cell in the source changed.

For example, suppose the North region has transactions for customer IDs 104, 104, and 209. A normal Count returns 3 because there are three records. Distinct Count returns 2 because there are two different customer IDs. If you also place Month in Columns, Excel calculates the unique customers within each region-and-month intersection. That is often more useful than counting an entire dataset when you need a breakdown.

Common Mistakes

  • Creating the PivotTable without the Data Model. This is the most common reason Distinct Count is missing from Value Field Settings. The ordinary PivotTable summary choices do not include it. You will generally need to create a new PivotTable and select the Data Model checkbox during setup; changing the source or refreshing an ordinary PivotTable will not add that capability.
  • Using Count when you mean Distinct Count. Count measures nonempty records in the field, so repeated values are counted repeatedly. If an order has several line items for the same customer, Count of Customer ID measures line items, not unique customers. Choose Distinct Count when the question is how many different IDs or labels appear.
  • Counting names that are not reliable identifiers. A distinct count treats different text entries as different values. For instance, a spelling variation or an accidental trailing space can make one person appear to be two customers. Clean or standardize the source, or use a stable customer ID where possible.
  • Assuming a grand total is the sum of the visible group totals. Distinct values can appear in more than one group. If customer 104 appears in both North and South, each region can show one unique customer, while the grand total counts that customer once across the full dataset. Therefore, distinct-count subtotals may not add up to the grand total.
  • Forgetting that blanks can affect the result. A blank in the counted field may be treated as a distinct value by the Data Model. If blanks should not be included in your business definition, filter out blank entries in the source or exclude them using the PivotTable’s field filters, then check the result against the records you intend to count.

Tips

  • Use a table as the source. Tables make it less likely that newly appended rows will be left outside the PivotTable source. After adding rows, refresh the PivotTable so the model and report use the latest data.
  • Choose the field that defines uniqueness. Count a customer ID for unique customers, not an invoice number unless invoices are what you mean to count. Before building the report, write down what one distinct item represents and select the column that identifies it.
  • Use row and column fields to define the scope. Distinct Count is calculated separately for each displayed group. Adding Year to Rows, for instance, produces a unique count within each year. If an individual can appear in multiple years, the overall total can be smaller than the sum of the yearly figures.
  • Keep labels self-explanatory. Rename the value field to something specific, such as Unique Orders or Distinct Products. A label like Count of Product does not tell readers whether duplicates are included.
  • Know the platform limitation. The steps here are for Excel for Microsoft 365 on Windows. Excel for Mac does not provide the same Power Pivot/Data Model authoring experience, and Excel for the web does not offer the same full workflow for creating a Data Model PivotTable. If you need to create this report, use Excel for Windows; a workbook with a Data Model may have limited editing options in other versions.

Troubleshooting

Distinct Count is not listed. First check whether the PivotTable was created with Add this data to the Data Model selected. If not, create a new one: select the source, choose Insert > PivotTable, select the checkbox in the dialog, and rebuild the layout. Also confirm that you are using Excel for Windows, not the web app or a Mac version with limited Data Model support.

The checkbox is missing or unavailable. Make sure you are in the Create PivotTable dialog for a new PivotTable, rather than a dialog for an existing report. Confirm that the selected source is a normal worksheet table or range and that the workbook is not opened in a restricted mode or an unsupported app. If the workbook is stored in an older file format, save a working copy as an Excel workbook (.xlsx) and try again in the Windows desktop app.

The number is higher than expected. Check for variations in the values, such as inconsistent IDs, spaces, or blanks. Then verify that the field in Values is the identifier you intended to count and that the report filters are set correctly. To diagnose the issue, temporarily put the identifier field in Rows and examine the listed entries for duplicates that look different or unexpected blank entries.

The number is lower than expected or has not changed. Confirm that the new records are inside the source table or PivotTable source range, then refresh the report. Check whether a report filter, slicer, or field filter is excluding records. If your source is a fixed range rather than a table, use PivotTable Analyze > Change Data Source to check or adjust the range, then refresh.

The total does not equal the sum of the groups. This can be correct. A distinct count removes duplicates within the calculation being shown, and the same item may occur in multiple groups. Check the grand total against the unique identifiers across the entire filtered source rather than adding the group figures together.

Frequently Asked Questions

Can I turn on Distinct Count in an existing PivotTable?
Not usually if it was created without the Data Model. Create a new PivotTable from the same source with Add this data to the Data Model selected, then recreate the fields and choose Distinct Count. An existing PivotTable already built from the Data Model can use the setting through Value Field Settings.

What is the difference between Count and Distinct Count?
Count counts records with a value in the selected field, including repeated values. Distinct Count counts different values once within the current PivotTable group. If a customer ID appears five times in one group, Count returns five and Distinct Count returns one.

Does Distinct Count ignore blank cells?
Do not assume that it does. The Data Model can treat blank as a distinct value, so a blank entry may affect the result. If blanks are not meaningful for your report, filter them out and verify the total against the source.

Can I use Distinct Count for each department or month?
Yes. Put Department or Month in Rows or Columns, and put the identifier to count in Values with Distinct Count selected. Excel calculates a separate unique count for each displayed intersection of those groups and filters.

Why does the grand total differ from the sum of each department?
The same identifier may appear in multiple departments. Each department counts it within its own group, but the grand total counts it once across the full set of records included in the report. The difference is expected when groups overlap.

Can I create this PivotTable in Excel for Mac or Excel for the web?
The complete creation workflow described here is for Excel for Microsoft 365 on Windows. Mac and web versions do not offer the same Data Model authoring controls, so use Excel for Windows to create the distinct-count PivotTable. You may be able to view or interact with a workbook created there, depending on the features available in your version.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.