How to Fix Excel PivotTable Showing Counts or Zeros Instead of Values
Question details
The user needs to fix a PivotTable that is displaying 'Count' or zeros instead of calculating the sum of numeric values when using data loaded from Power Query.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Aggregating data in a PivotTable using a dataset imported and processed through Power Query.
- Observed behavior
- The PivotTable automatically aggregates the value fields using 'Count' or displays a sum of zero, typically because the source numbers are formatted or stored as text.
Ensure you have access to the original workbook containing the Power Query connections and that you are permitted to refresh and modify the background queries.
Format Data Types as Numeric in Power Query Editor
Changing the data type of your values from text to a numeric format inside Power Query ensures that the PivotTable recognizes them as numbers and sums them correctly.
When data is imported into Excel via Power Query, numerical columns are sometimes mistakenly categorized as text. Because PivotTables cannot mathematically sum text values, they default to counting the number of entries instead. If forced to sum text, the result will output as zero. Correcting this at the query level is the most robust solution.
Navigate to the 'Data' tab on your Excel ribbon. Click on 'Queries & Connections' to open the side pane, then double-click the specific query that feeds data into your PivotTable.
In the Power Query Editor window, scroll horizontally to find the columns that contain the numerical data currently showing up as counts or zeros in your PivotTable.
Click the small data type icon located on the left side of the column header (it may display 'ABC' if formatted as text). From the dropdown menu, select 'Whole Number', 'Decimal Number', or 'Currency' depending on your specific data requirements.
Click the 'Close & Load' button located on the Home tab of the Power Query Editor. This will apply the new data types and update the dataset in your workbook.
Right-click anywhere inside your PivotTable and select 'Refresh'. In the PivotTable Fields pane, remove the problematic field from the 'Values' area and drag it back in. It should now automatically default to 'Sum'.
Experience Seamless PivotTables with WPS Office
If you frequently struggle with complex Excel features like Power Query data types and PivotTable aggregation errors, consider trying WPS Office. It provides a highly compatible, intuitive Spreadsheet application that makes data analysis straightforward and hassle-free.
- 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Excel Workbook: Launch WPS Spreadsheets and open your existing .xlsx file without losing any formatting or PivotTable configurations.
- 3. Analyze Data Smoothly: Select your dataset, click 'Insert', and choose 'PivotTable' to effortlessly drag, drop, and accurately sum your numerical fields.

Frequently Asked Questions
Why does my PivotTable automatically default to Count instead of Sum?
A PivotTable defaults to 'Count' if it detects any non-numeric data within the selected column of your source data. This includes numbers stored as text, blank cells, error values, or spaces. If the entire column contains strictly numbers, it will default to 'Sum'.
How do I manually change Count to Sum in my PivotTable?
In the PivotTable Fields pane, click the small arrow next to the field in the 'Values' box and select 'Value Field Settings'. In the dialog box that appears, select 'Sum' from the 'Summarize value field by' list, and click 'OK'. Note that if the underlying data is still text, the sum will result in zero.
Can I format the cells as numbers directly in the worksheet to fix this?
Simply changing the cell format to 'Number' in the Home ribbon often doesn't convert text-stored numbers into actual numerical values recognized by the PivotTable. You typically need to use the 'Text to Columns' tool, the 'Convert to Number' error-checking option, or fix the data type at the source via Power Query.




