Pivot Table Issues

How to Fix Excel PivotTable Showing Counts or Zeros Instead of Values

John WilsonJohn Wilson Nov 3, 2026 870 views

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.

How to Fix Excel PivotTable Showing Counts or Zeros Instead of Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Launch the Power Query Editor

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.

2
Locate the affected value columns

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.

3
Change the column data type

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.

4
Load the corrected data

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.

5
Refresh and aggregate the PivotTable

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'.

Check for blank cells: In addition to text formatting, if your column contains any blank cells or hidden spaces, the PivotTable will also default to 'Count'. You can use Power Query's 'Replace Values' feature to replace nulls or blanks with zeros before loading.
Free Microsoft Office alternative

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. 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
  2. 2. Open Your Excel Workbook: Launch WPS Spreadsheets and open your existing .xlsx file without losing any formatting or PivotTable configurations.
  3. 3. Analyze Data Smoothly: Select your dataset, click 'Insert', and choose 'PivotTable' to effortlessly drag, drop, and accurately sum your numerical fields.
100% compatibility with Microsoft Excel (.xlsx) files and existing PivotTable structures.Intelligent data recognition automatically correctly formats numbers for instant summing in PivotTables.Lightweight application that runs smoothly even when processing large datasets.Familiar interface ensures a zero-learning-curve transition from Microsoft Office.
microsoft office alternative - wps office

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.