Create a PivotTable Distinct Count in Excel for the Web
Question details
The user wants to count distinct values in a PivotTable using Excel for the Web.

- Product
- Microsoft Excel for the Web
- Device & OS
- not provided
- Scenario
- Creating or editing a PivotTable and choosing how to summarize its values.
- Observed behavior
- The Distinct Count option does not appear in Excel for the Web.
Check that your source data has column headers and that you have access to a desktop version of Excel that supports Data Model PivotTables. If your existing PivotTable was not created with the Data Model, you may need to create a new one.
Create a Data Model PivotTable in desktop Excel
Use desktop Excel to add the source data to the Data Model and select Distinct Count.
Distinct Count requires a PivotTable based on the Data Model. Excel for the Web can work with many PivotTables, but it does not provide the same Data Model creation options as desktop Excel.
In Excel for the Web, use the option to open the workbook in the desktop app. Sign in to desktop Excel if prompted.
Select a cell in the source data, then choose Insert > PivotTable. In the creation dialog, select Add this data to the Data Model and choose where to place the PivotTable.
In the PivotTable Fields pane, drag the category field to Rows and the field you want to count to Values.
Open the value field's menu in the Values area, choose Value Field Settings, select Distinct Count, and click OK.
Choose File > Save so the updated PivotTable is available in the saved workbook.
Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier move between applications. It does not fix Excel for the Web's Data Model limitations.

Frequently Asked Questions
Why is Distinct Count missing from my PivotTable?
Distinct Count requires a PivotTable based on the Data Model. If the PivotTable was created without it, create a new PivotTable in a supported desktop version of Excel and select Add this data to the Data Model.
Where is Distinct Count in desktop Excel?
For a Data Model PivotTable, open the value field's menu in the Values area, choose Value Field Settings, and select Distinct Count.
Can I add the Data Model to an existing ordinary PivotTable?
An ordinary PivotTable generally cannot be converted directly. Select the source data and use Insert > PivotTable to create a new one with Add this data to the Data Model selected.
What if I don't see the Data Model checkbox?
Data Model support varies by desktop Excel version and platform. Open the workbook in a desktop installation that supports Data Model PivotTables and check the Insert > PivotTable dialog again.




